604: PeopleSoft for the
Oracle DBA
ronald.dijkman@ubs.com
http://www.ubs.com
david.kurtz@go-faster.co.uk
http://www.go-faster.co.uk
Project Overview
HRMS 7.5
Local Swiss Payroll
PeopleTools 7.59
45000 employees (33000 current)
127Gb Data, 147Gb Total DB size
3-tier clients (200-280 concurrent users)
Web clients (20-40 concurrent users)
Upgrading to HR8 + GP
2
Technical Overview
HP-UX 11 64-bit
Clustered Servers
HP Service Guard
Oracle 8.0.5 -> 8.1.6
Multi-Processor Tuxedo Domains
Windows and Web Clients
3
Hardware Configuration
QA
Development
Production
4
System Specifications
Development System
HP V-Class, Model E 9000/800
CPU: 8
RAM: 12 GB
Quality Assurance System
HP V-Class, Model E 9000/800
CPU: 10/10
RAM: 8/8 GB
Production System
HP V-Class, Model E 9000/800
CPU: 20/20
RAM: 24/10 GB
5
EMC Storage Arrays
Storage EMC
R1 256 disks
R2 158 disks
R3 84 disks
---------
Total 498 disks x 18 GB = 8.7 TB
6
Database Upgrade Path
HOTL
M
I
PS 7.5 DEVP QUAL G PROD
R
EXP8 UPGR8
MIGR HOTL
PS 8 DEVP8 QUAL8 PROD8
ENG? ENG?
PLAY DEMO
7
Challenges
Large HRMS implementation
Lots of customisations
Payroll is a ‘financial’ batch
Oracle bugs
Performance Problems
8
DBA Team
Good Administrative Practice
Performance Tuning
9
DBA Team
Good Administrative Practice
Performance Tuning
logical structure of the database
SQL tuning
I/O and physical structure
Resource contention
Bugs
New Features in Oracle 8.1
Object Sizing
10
Techniques
Who is logged in and what are they doing?
Specification of the data model
How to SQL_TRACE PeopleSoft
Where does the code come from?
Performance Metrics
11
Who is logged in and what are
they doing
Definition of ‘database’
What happens at login
PT7.5 -v- PT8
Session Registration
2-tier client
Application Server
Other Batches
12
What happens when you connect
to PeopleTools 7.x?
Connect=H75D/PS/
EXECUTE :1 := SQLCQR_LOGINCHECK(:2)
SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME =
‘H75D’
SELECT
OWNERID,TOOLSREL,TO_CHAR(LASTREFRESHDTTM,'YYYY
-MM-DD HH24:MI:SS'),
TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD
HH24:MI:SS'), SECURITY_OPTION FROM
SYSADM.PSLOCK
SELECT VERSION, OPRTYPE, OPERPSWD, ACCESSID,
ACCESSPSWD FROM SYSADM.PSOPRDEFN WHERE OPRID =
‘PS’
Connect=H75D/SYSADM/
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD
HH24.MI.SS."000000"') FROM PSCLOCK
SELECT VERSION FROM PSLOCK
13
What happens when you connect
to PeopleTools 8.1?
Connect=GP81O81/PEOPLE/
SELECT OWNERID FROM PS.PSDBOWNER WHERE DBNAME =
'GP81O81'
SELECT OWNERID, TOOLSREL,
TO_CHAR(LASTREFRESHDTTM,'YYYY-MM-DD HH24:MI:SS'),
TO_CHAR(LASTCHANGEDTTM,'YYYY-MM-DD HH24:MI:SS')
FROM SYSADM.PSSTATUS
SELECT VERSION, OPERPSWD, ENCRYPTED, SYMBOLICID,
ACCTLOCK FROM SYSADM.PSOPRDEFN WHERE OPRID = 'PS'
SELECT ACCESSID, ACCESSPSWD, ENCRYPTED FROM
SYSADM.PSACCESSPRFL WHERE SYMBOLICID = 'SYSADM1'
Connect=GP81O81/SYSADM/
SET type=2012 program=pstools.exe
SET type=2 OprId=PS
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD-
HH24.MI.SS."000000"') FROM PSCLOCK
Connect=GP81O81/SYSADM/
14
Session Registration
Problem: Everybody connects to the
database as ‘sysadm’.
Oracle provides a PL/SQL package
DBMS_APPLICATION_INFO writes string to
sys.v$session.client_info
,,,,
above is PT8.1 string
eg.
PS,david,GO-FASTER-1,PT81,PSSAMSRV.EXE,
PeopleSoft uses this package from 7.53 (Cobol
from 7.54)
15
Session Registration
Windows Client in 2-tier registers by
default (7.53)
Application Server configuration parameter
EnableDBMonitoring=1
Cobol (from 7.54)
Not used with SQR
So we wrote a trigger
16
Session Registration Trigger
When a process is started by the process
scheduler it updates its own status
from 6 (initiated)
to 7(processing)
see this from process monitor
so, place a trigger on this transition
works with Cobol and SQR
does not work with PS/Query-Crystal, nVision,
DBAgents
because their status is updated by different
application server process - PSSAMSRV
17
Session Registration Trigger
Prepends Process Instance to client_info
create or replace trigger psprcsrqst_register
before update of runstatus on psprcsrqst for each row
when (new.runstatus = 7 and old.runstatus != 7
and not new.prcstype IN('Crystal','PSJob','Database
Agent','nVision-ReportBook'))
declare
l_client_info varchar2(64);
begin
select client_info into l_client_info from v$session
where sid = (select sid from v$mystat where rownum = 1);
l_client_info:=SUBSTR(TO_CHAR(:new.prcsinstance)||','||
l_client_info,1,64);
sys.dbms_application_info.set_client_info(l_client_info);
exception when others then null;
end;
/
18
Specification of the Data Model
Two Data Dictionaries
Default Indexes
User Specified Indexes
PT8.1: Platform Specific View definition
19
Tools Table -v- DB Catalogue
Table Description Oracle Table PeopleTools Table
Data Definition
1 row per table/view DBA_TABLES PSRECDEFN
DBA_VIEWS
1 row per column DBA_TAB_COLUMNS PSRECFIELD
1 row per distinct column name PSDBFIELD
1 row per view DBA_VIEWS PSVIEWTEXT
1 row per synonym DBA_SYNONYMS
1 row per index DBA_INDEXES PSINDEXDEFN
1 row per indexed column DBA_IND_COLUMNS PSKEYDEFN
Security
1 row per oprid DBA_USERS PSOPRDEFN
(PeopleTools Data Administration -> Indexes ->
Change Record Indexes -> Edit Index
23
User Specified Index
24
Descending Key Index Bug
The following parameter must be added to
the init.ora of an Oracle 8.1.6 instance
BEFORE you build descending key indexes.
EVENT='10612 trace name context forever,
level 1’
_ignore_desc_in_index = TRUE
This takes care of several bugs found
related to DESC INDEXES (errant
ORA-3113s)
25
Constraints
Unique
Implied by Unique Key Indexes
Mandatory/Not Null
Referential Integrity?
There aren’t any!
26
Temporary Tablespaces
Create tablespace ‘ORATEMP’
Alter tablespace TEMPORARY
can only contain temporary segment
cannot contain any other object
no redo logging
alter temporary tablespace for all users
Don’t do this to PSTEMP
27
Space Management
DDL models
Default -v- Override parameters
Feeding back reality
28
DDL Models
System-wide default storage options
29
Parameters
PeopleSoft Parameters
Square Brackets
TBNAME, TBCOLLIST, IDXNAME, IDXCOLLIST,
TBSPCNAME
User Parameters
** delimited
Delivered (Oracle) INIT, NEXT, MAXEXT, PCT,
INDEXSPC, BITMAP
Other possibilites
COMPRESS, PREFIX LENGTH, PCTFREE, PCTUSED,
NOLOGGING, BUFFER POOL
30
Default -v- Override parameters
Overrides in application designer
31
DDL Model
PSDDLMODEL
Field Name Type Length Attributes Description
STATEMENT_TYPE Nbr 1 Key Statement Type
1 = Table
2 = Index
3 = Unique Index
4 = Tablespace
PLATFORMID Nbr 2 Key Platform ID
0 = SQLBase
1 = DB2
2 = Oracle
3 = Informix
4 = DB2/Unix
5 = ALLBASE
6 = Sybase
7 = Microsoft
8 = DB2/400
SIZING_SET Nbr 3 Key Sizing Set
PARMCOUNT Nbr 3 Parameter Count
MODEL_STATEMENT Long 0 Model SQL Statement
32
Default Parameters
PSDDLDEFPARMS
Field Name Type Length Attributes Description
STATEMENT_TYPE Nbr 1 Key Statement Type
PLATFORMID Nbr 2 Key Platfor m ID
SIZING_SET Nbr 3 Key Sizing Set
PARMNAME Char 8 Key DDL Parameter Name
PARMVALUE Char 128 DDL Parameter Value
33
Record Parameter Overrides
PSRECDDLPARM
Field Name Type Length Attributes Description
RECNAME Char 15 Key Record (Table) Name
PLATFORMID Nbr 2 Key Platform ID
SIZINGSET Nbr 3 Key Sizing Set
PARMNAME Char 8 Key DDL Parameter Name
PARMVALUE Char 128 DDL Parameter Value
34
Index Parameter Overrides
PSIDXDDLPARM
Field Name Type Length Attributes Description
RECNAME Char 15 Key Record (Table) Name
INDEXID Char 1 Key Index Identifier
_ = Primary key index
# = List columns index
0-9 = Alternate search
key indexes
A-Z = User specified
indexes
PLATFORMID Nbr 2 Key Platfor m ID
SIZINGSET Nbr 3 Key Sizing Set
PARMNAME Char 8 Key DDL Parameter Name
PARMVALUE Char 128 DDL Parameter Value
35
Two Data Dictionaries
Compare
Database Catalogue
USER_TABLES, USER_INDEXES
PeopleTools
PSDDLDEFPARMS, PSRECDDLPARM,
PSIDXDDLPARM
36
Retrofitting Sizing into
PeopleTools Data Dictionary
NOT SUPPORTED BY PEOPLESOFT
Possible to copy the sizing information in
USER_TABLES and USER_INDEXES back
into the Tools tables
Why is this useful?
An object is rebuilt during an upgrade
Sizing information is preserved
scripts bundled with presentation or
available from
http://www.go-faster.co.uk
37
Limitations of the DDL Model
The following object cannot be created by
the DDL Model
Index Organised Tables
Partitions
Global Temporary Tables
Clusters
Maintained manually by the DBA outside of
PeopleTools
Structure of column list still inside PeopleTools
38
Global Temporary Tables
New Feature in Oracle 8.1
Reduced Redo Logging
40%-50% I/O reduction
unrecoverable
Definition is persistent
Content is private & transient to session
not suitable for on-line processing
Useful for temporary tables
Local Swiss Payroll
Financial Batches
No High Water Mark issues
Even faster truncate
39
SQL Tracing
Client
Batches (AE, SQR)
Reports (Crystal, nVision, PS/Query)
Tracing with Triggers
Where does the code come from
40
SQL Optimisation
SQL_TRACE = TRUE;
Embed command
Trigger on processes via process scheduler
PSPRCSRQST
Set trace in session
2-tier client is multithreaded
Small Private Application server
41
SQL_TRACE = TRUE;
Initialisation Parameter
TIMED_STATISTICS = TRUE
In current session
ALTER SESSION SET SQL_TRACE=TRUE;
In another session
EXECUTE
sys.dbms_system.set_sql_trace_in_session
(,,TRUE);
42
Enabling Client Tracing
43
Typical Trace Output (PT7.x)
1-2285 0.861 Cur#1 RC=0 Dur=0.000 COM Stmt=SELECT
VERSION, FIELDVALUE, TO_CHAR(EFFDT,'YYYY-MM-DD'), EFF_STATUS,
XLATLONGNAME, XLATSHORTNAME, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-
HH24.MI.SS."000000"'), LASTUPDOPRID, FIELDNAME, LANGUAGE_CD,
EFFDT FROM XLATTABLE WHERE FIELDNAME = :1 AND LANGUAGE_CD =
:2 ORDER BY FIELDNAME, LANGUAGE_CD, FIELDVALUE, EFFDT
1-2286 0.000 Cur#1 RC=0 Dur=0.000 Bind-1 type=2 length=6
value=ACTION
1-2287 0.000 Cur#1 RC=0 Dur=0.000 Bind-2 type=2 length=3
value=ENG
1-2288 0.111 Cur#1 RC=0 Dur=0.000 COM Stmt=SELECT
VERSION FROM PSLOCK
44
SQLCLEANUP.EXE
SELECT VERSION,
FIELDVALUE,
TO_CHAR( EFFDT,
'YYYY-MM-DD' ),
EFF_STATUS,
XLATLONGNAME,
XLATSHORTNAME,
TO_CHAR( LASTUPDDTTM,
'YYYY-MM-DD-HH24.MI.SS."000000"' ),
LASTUPDOPRID,
FIELDNAME,
LANGUAGE_CD,
EFFDT
FROM XLATTABLE
WHERE FIELDNAME = :1
AND LANGUAGE_CD = :2
ORDER BY FIELDNAME, LANGUAGE_CD, FIELDVALUE, EFFDT
\
ACTION,ENG
/
45
Mass Change/AE/SQR
What you see in the code is what you get
All PS programs can be traced
46
Trigger for via process scheduler
PSPRCSRQST
create or replace trigger sysadm.set_trace
before update of runstatus on sysadm.psprcsrqst
for each row
when (new.runstatus = 7 and old.runstatus != 7
and NOT new.prcstype IN('Crystal','PSJob',
'Database Agent','nVision-ReportBook') and
...)
)
begin
sys.dbms_session.set_sql_trace(true);
end;
/
47
How developers can enable
SQL_TRACE
Check the box
Only the next execution of this process is
traced
then the box will be unchecked
Log of traced executions
48
Then what happens?
SQL trace is enabled by a trigger
When the process terminates, the trace file
is processed with TKPROF
Two additional files produced
i) statements sorted by elapsed execution time
ii) statements sorted by elapsed fetch time
Top 10 Statements only
Execution plans
49
Processed Trace Files on Web
3 files per process, .log, .exeela, .fchela
50
51
Set trace in session
2-tier client is multithreaded
Small Private Application server
EXECUTE sys.dbms_system.set_sql_trace_in_session
(,,TRUE);
52
Where does the code come from
(PeopleTools 7.x)?
Application Engine
no bind variables - literal values
PS/Query
table aliases A, B, C, A1, B1 …
Panel Processor
mostly upper case SQL
PeopleCode (scroll functions)
upper case select and from clause
lower case where clause with litteral values
SQR
mixed case with bind variable :1, :2 …
three character table aliases
53
Performance Metrics
Process Scheduler Table - PSPRCSRQST
Trigger to capture history into an archive table
CREATE OR REPLACE TRIGGER SYSADM.psprcsrqst_archive
before delete on SYSADM.psprcsrqst
for each row
begin
insert into SYSADM.ps_prcsrqstarch
(PRCSINSTANCE
, ...
) values
(:new.PRCSINSTANCE
, ...
);
EXCEPTION WHEN OTHERS THEN NULL;
end;
/
54
Summary
Identified Sessions
Synchronised sizing information in
dictionaries
Trace individual processes
Identify where the SQL comes from
Performance Tuning
Control index creation without altering
application
Performance Metrics/History
55
Questions?
56
604: PeopleSoft for the
Oracle DBA
ronald.dijkman@ubs.com
http://www.ubs.com
david.kurtz@go-faster.co.uk
http://www.go-faster.co.uk