Transcript No Slide Title

PeopleSoft for the
Oracle DBA
Session 627
[email protected]
www.go-faster.co.uk
1
Project Overview
 HRMS 7.5
 HRMS 8
 Local Swiss Payroll
 Global Payroll
 PeopleTools 7.59
 PeopleTools 8.15
 45000 employees
(33000 current)
 All Users via PIA
 127Gb Data, 147Gb
Total DB size
 3-tier clients (200-280
concurrent users)
 Web clients (20-40
concurrent users)
2
Technical Overview
 HP-UX 11 64-bit
 Clustered Servers
 HP Service Guard
 Oracle 8.0.5
 Oracle 8.1.7.2
 PT 7.59
 PT 8.15.01
 Windows and Web
Clients
 PeopleSoft Internet
Architecture
3
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
4
Challenges
 Large HRMS implementation
 Lots of customisations
 Payroll is a ‘financial’ batch
 Oracle bugs
 Performance Problems
5
DBA Team
 Good Administrative Practice
 Performance Tuning
6
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
7
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
8
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
9
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,'YYYYMM-DD HH24:MI:SS'), TO_CHAR(LASTCHANGEDTTM,'YYYY-MMDD 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
10
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,'YYYYMM-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/
11
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)
12
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
(from 8.1x) prcsapi.src
13
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
14
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
sys.dbms_appplication_info.read_client_info(l_client_info);
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;
/
15
Specification of the Data Model
 Two Data Dictionaries
 Default Indexes
 User Specified Indexes
 PT8.1: Platform Specific View definition
16
Tools Table -v- DB Catalogue
T ab le D e scrip tio n
O racle T ab le
P e o p le T o o ls T ab le
1 ro w p e r tab le /v ie w
D BA_TABLES
D B A _ V IE W S
PSRECDEFN
1 ro w p e r co lu m n
D BA_TAB_CO LU M N S
P S R E C F IE L D
D a ta D e fin itio n
1 ro w p e r d is tin c t co lu m n n am e
P S D B F IE L D
1 ro w p e r v ie w
D B A _ V IE W S
P S V IE W T E X T
1 ro w p e r sy n o n y m
DBA_SYNONYM S
1 ro w p e r in d e x
D B A _ IN D E X E S
P S IN D E X D E F N
1 ro w p e r in d e x e d co lu m n
D B A _ IN D _ C O L U M N S
PSKEYDEFN
DBA_USERS
PSO PRDEFN
S e cu rity
1 ro w p e r o p rid
(P e o p le T o o ls < = 7 .x )
G ran t fo r ta b le acce ss
D B A _ T A B _ P R IV S
17
Keys & Indexing
 Implied from Record Definition
• Key
• Duplicate
• List (not Tools 8)
• Alternate Search
• Descending
 User Specified
 Constraints
 Suppressing Index build
 Sparse Indexing
18
Indexes Implied from Record Definition
 Key
• Duplicate
 List (not indexed in Tools 8)
 Alternate Search
 Descending
19
Suppressing Index build
 In Application Designer
• Tools -> Data Administration -> Indexes ->
Change Record Indexes -> Edit Index
20
User Specified Index
21
Descending Key Index Bug
 The following parameter must be added to
the init.ora of an Oracle 8.1.x 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)
22
Constraints
 Unique
• Implied by Unique Key Indexes
 Mandatory/Not Null
 Referential Integrity?
• There aren’t any!
23
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
24
Space Management
 DDL models
 Default -v- Override parameters
 Feeding back reality
25
DDL Models
 System-wide default storage options
26
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
27
Default -v- Override parameters
 Overrides in application designer
28
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
29
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
30
Record Parameter Overrides
 PSRECDDLPARM
Field Name
Type
Length
Attributes
Description
RECNAME
Char
15
Key
Record (Table) Name
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
31
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
32
Two Data Dictionaries
 Compare
• Database Catalogue
 USER_TABLES, USER_INDEXES
• PeopleTools
 PSDDLDEFPARMS, PSRECDDLPARM,
PSIDXDDLPARM
33
Retrofitting Sizing into PeopleTools 7.5x
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 available from
• http://www.go-faster.co.uk
34
Retrofitting Sizing into PeopleTools 8.1x
Data Dictionary
 NOW SUPPORTED BY PEOPLESOFT
• settables.sqr
• setindex.sqr
 copies database data dictionary value back into
PeopleSoft Data Dictionary
• issues on Oracle
 Global Temporary tables have NULL storage
options
 resulting in 0 values in PeopleSoft
35
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
36
Tablespaces with Uniform Extent Size
 Attribute of Locally Managed Tablespaces
from Oracle 8.1
 Possible in prior versions if rely on
tablespace default storage option
 Every segment same size.
 Every free space also same size
 Space allocation algorithm will naturally
use up spaces at bottom of tablespace
 Less fragmentation
 No need to coalesce tablespace
37
Global Temporary Tables
 New Feature in Oracle 8.1
 Definition is persistent
 Content is private & transient to session
• not suitable for on-line processing due to multithreaded nature of application server
 Reduced Redo Logging
• unrecoverable
• significant I/O reduction
 min overhead 80 bytes/row
 No Permanent Tablespace space overhead
• physically exist in temporary tablespace.
38
Uses for temporary tables
 It is better to design a process not to
temporarily store large amounts of data in
the database in a permanent object in the
first place.
 Local Swiss Payroll
 Financial Batches
 Global Payroll
• Application engine & Cobol make extensive use
of temporary working tables.
 No High Water Mark issues
• table created fresh for every session/transaction
39
What is Partitioning?
 Logically,
• a partitioned table is a still a single table
 Physically,
• each partition is a separate table.
• in a range partitioned table, the partition in
which a row is placed is determined by the
value of one or more columns.
 Local Index
• is partitioned on the same logical basis as the
table.
40
How should Partitioning used in GP?
 Largest Result tables range each
partitioned on EMPLID to match GP
streaming
• 1 stream : 1 partition
• Thus each stream references one partition in each result
table.
• Only 1 interested transaction per block
• Indexes ‘locally’ partitioned
 Partitioning really designed for DSS
systems. Only efficient for large tables.
• GP_RSLT_ACUM, GP_RSLT_ERN_DED,
• GP_RSLT_PIN, GP_RSLT_PI_DATA
• GP_PYE_PRC_STAT, GP_PYE_SEG_STAT
41
SQL Tracing
 Client
 Batches (AE, SQR)
 Reports (Crystal, nVision, PS/Query)
 Tracing with Triggers
 Where does the code come from
42
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
43
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);
44
Enabling Client Tracing
45
Typical Trace Output
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
46
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
/
47
Application Engine / SQR
 What you see in the code is what you get
• All PS programs can be traced
48
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;
/
49
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
50
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
51
52
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);
53
Where does the code come from?
 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
54
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;
/
55
Summary
 Identified Sessions
 Synchronised sizing information in
dictionaries
 Global Temporary Tables
 Partitioned Tables
 Trace individual processes
 Identify where the SQL comes from
 Performance Tuning
 Control index creation without altering
application
 Performance Metrics/History
56
Questions?
57
And there’s more
 Configuring Global Payroll for Optimal
Performance
 Session 527
• Wednesday 10.30 - 11.30
 Round Table Discussion session 626
• Wednesday 15.30 - 16.30
• Discuss some areas in more detail.
58
PeopleSoft for the
Oracle DBA
Session 627
[email protected]
www.go-faster.co.uk
59