Transcript 604: PeopleSoft for the Oracle DBA [email protected] http://www.ubs.com [email protected] 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

604: PeopleSoft for the
Oracle DBA
[email protected]
http://www.ubs.com
[email protected]
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
R2
R3
256 disks
158 disks
84 disks
--------Total 498 disks x 18 GB = 8.7 TB
6
Database Upgrade Path
HOTL
M
PS 7.5 DEVP
I
QUAL
G
PROD
R
EXP8
UPGR8
MIGR
PS 8
DEVP8
QUAL8
ENG?
PLAY
7
DEMO
HOTL
PROD8
ENG?
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-DDHH24.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
<Operator ID>,<OS user name>,<machine
name>,<domain ID>,<program name>
 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
1 row per column
DBA_TABLES
PSRECDEFN
DBA_VIEWS
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
1 row per indexed column
DBA_IND_COLUMNS PSKEYDEFN
PSINDEXDEFN
Security
1 row per oprid
DBA_USERS
(PeopleTools <=7.x)
Grant for table access
20
DBA_TAB_PRIVS
PSOPRDEFN
Keys & Indexing
Implied from Record Definition
 Key
 Duplicate
 List (not Tools 8)
 Alternate Search
 Descending
User Specified
Constraints
Suppressing Index build
Sparse Indexing
21
Indexes Implied from Record
Definition
Key
 Duplicate
List (not indexed in Tools 8)
Alternate Search
Descending
22
Suppressing Index build
In Application Designer
 Tools -> 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
32
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
Default Parameters
PSDDLDEFPARMS
33
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
Record Parameter Overrides
PSRECDDLPARM
34
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
Index Parameter Overrides
PSIDXDDLPARM
35
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
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
(<sid>,<serial#>,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-DDHH24.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
(<sid>,<serial#>,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
[email protected]
http://www.ubs.com
[email protected]
http://www.go-faster.co.uk