604: PeopleSoft for the Oracle DBA [email protected] http://www.ubs.com

604: PeopleSoft for the Oracle DBA ronald.dijkman@ubs.com http://www.ubs.com

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

10 logical structure of the database SQL tuning I/O and physical structure Resource contention Bugs New Features in Oracle 8.1 Object Sizing

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,'YYYYMM-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 ,,,, 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_ VI EWS DBA_ TAB_ COLUMNS PSRECFI ELD

1 row per distinct column name PSDBFI ELD 1 row per view DBA_ VI EWS PSVI EWTEXT 1 row per synonym DBA_ SYNONYMS 1 row per index

DBA_ I NDEXES 1 row per indexed column DBA_ I ND_ COLUMNS PSKEYDEFN PSI NDEXDEFN Security 1 row per oprid DBA_ USERS (PeopleTools <=7.x) Grant for table access 20

DBA_ TAB_ PRI VS 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 arent 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 Dont 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 Record Parameter Overrides PSRECDDLPARM 34 Index Parameter Overrides PSIDXDDLPARM 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

39 Local Swiss Payroll Financial Batches No High Water Mark issues Even faster truncate 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-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 (,,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

Recently Viewed Presentations

  • Forum on Concealed Carry Law Implementation October 19,

    Forum on Concealed Carry Law Implementation October 19,

    Don Fraser, Director Alumni Affairs. Jon Arriola, Prof. Learning Frameworks. ... Shawn Taylor, Professor TSI Math. Dr. Jackie Johnston, Professor TSI Math. Jack Caddell, Prof. Health & Kinesiology ... presentation of preliminary campus plans and recommendations thru chain of command...
  • Using a Medical Dictionary - Wasatch

    Using a Medical Dictionary - Wasatch

    Using a Medical Dictionary Medical Terminology Utah State Office of Education Utah State Office of Education Using A Medical Dictionary Even with the best deciphering skills, your analysis of an unfamiliar term can sometimes produce a strange-sounding definition.
  • Angle Modulation: Phase Modulation or Frequency Modulation Basic

    Angle Modulation: Phase Modulation or Frequency Modulation Basic

    Calibri Arial Times New Roman Symbol blank Equation PowerPoint Presentation Visualizing the FM Phasor . . . …down at the "Complex Plane" Instantaneous Frequency Deviation Voltage Controlled Oscillator (VCO) Simple Case For Analysis FM Spectrum PowerPoint Presentation Bessel Function Tabulation
  • Stewardship - europartners.org

    Stewardship - europartners.org

    Free to invest! S. N. W. E. Focusing on what you find most important in life - your Life Values! Free to live! Free to reach your goals! Creating margin: You can focus on developing your important relationships, learning contentment,
  • Palm Beach Lakes 1923-2016 Palm Beach Lakes Conniston

    Palm Beach Lakes 1923-2016 Palm Beach Lakes Conniston

    April 12, 1964—first elders: Jean McMasters, Hayward Milton, Don Spurlock, Alvin Witt. February; 7, 1965 first deacons: Johnny Davis, Bob Haines, Jerry Hopkins, Paul Jordan ... Chris, Josh, Troy & Robert Martin. Great Events on Leo Lane! Successes of Ladies...
  • Intro to Gravity and the NOAA Gravity Program (GRAV-D)

    Intro to Gravity and the NOAA Gravity Program (GRAV-D)

    UT Intro to Geophysics Class March 10, 2009 ... Subtract out accelerations that you know to obtain anomalies due to earth's topography and geology For free-air anomaly: - Theoretical Gravity (Latitude Correction) - Free air Correction Eotvos Correction - Off-level...
  • DERIVATIVES OF TRIGONOMETRIC FUNCTIONS When we talk about

    DERIVATIVES OF TRIGONOMETRIC FUNCTIONS When we talk about

    ??=sin?, it is understood that sin? means the sine of . the angle whose radian measure is ?. A similar convention holds for the other trigonometric functions . ... In this example, we must use the Product Rule before using...
  • 2015-2016 Adolescent Medicine Fellowship Orientation

    2015-2016 Adolescent Medicine Fellowship Orientation

    Patients and families must be given explicit information about what is known, and what is not in order to assure a genuine consent process for treatment. Slide adapted from: Olson J. Hormonal Therapy for Transgender Youth. Society for Adolescent Health...