1 / 57

604: PeopleSoft for the Oracle DBA

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

dwayne
Download Presentation

604: PeopleSoft for the Oracle DBA

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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

  3. 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

  4. Hardware Configuration QA Development Production

  5. System Specifications • Development System • HP V-Class, Model E 9000/800CPU: 8RAM: 12 GB • Quality Assurance System • HP V-Class, Model E 9000/800CPU: 10/10RAM: 8/8 GB • Production System • HP V-Class, Model E 9000/800CPU: 20/20RAM: 24/10 GB

  6. EMC Storage Arrays • Storage EMC R1 256 disks R2 158 disks R3 84 disks --------- Total 498 disks x 18 GB = 8.7 TB

  7. Database Upgrade Path MIGR HOTL HOTL DEVP QUAL PROD PS 7.5 UPGR8 EXP8 PLAY DEMO DEVP8 QUAL8 PROD8 PS 8 ENG? ENG? M I G R

  8. Challenges • Large HRMS implementation • Lots of customisations • Payroll is a ‘financial’ batch • Oracle bugs • Performance Problems

  9. DBA Team • Good Administrative Practice • Performance Tuning

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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/

  15. 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)

  16. 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

  17. 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

  18. 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; /

  19. Specification of the Data Model • Two Data Dictionaries • Default Indexes • User Specified Indexes • PT8.1: Platform Specific View definition

  20. Tools Table -v- DB Catalogue

  21. Keys & Indexing • Implied from Record Definition • Key • Duplicate • List (not Tools 8) • Alternate Search • Descending • User Specified • Constraints • Suppressing Index build • Sparse Indexing

  22. Indexes Implied from Record Definition • Key • Duplicate • List (not indexed in Tools 8) • Alternate Search • Descending

  23. Suppressing Index build • In Application Designer • Tools -> Data Administration -> Indexes -> Change Record Indexes -> Edit Index

  24. User Specified Index

  25. 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)

  26. Constraints • Unique • Implied by Unique Key Indexes • Mandatory/Not Null • Referential Integrity? • There aren’t any!

  27. 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

  28. Space Management • DDL models • Default -v- Override parameters • Feeding back reality

  29. DDL Models • System-wide default storage options

  30. 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

  31. Default -v- Override parameters • Overrides in application designer

  32. DDL Model • PSDDLMODEL

  33. Default Parameters • PSDDLDEFPARMS

  34. Record Parameter Overrides • PSRECDDLPARM

  35. Index Parameter Overrides • PSIDXDDLPARM

  36. Two Data Dictionaries • Compare • Database Catalogue • USER_TABLES, USER_INDEXES • PeopleTools • PSDDLDEFPARMS, PSRECDDLPARM, PSIDXDDLPARM

  37. 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

  38. 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

  39. 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

  40. SQL Tracing • Client • Batches (AE, SQR) • Reports (Crystal, nVision, PS/Query) • Tracing with Triggers • Where does the code come from

  41. 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

  42. 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);

  43. Enabling Client Tracing

  44. 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

  45. 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 /

  46. Mass Change/AE/SQR • What you see in the code is what you get • All PS programs can be traced

  47. 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; /

  48. 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

  49. 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

  50. Processed Trace Files on Web • 3 files per process, .log, .exeela, .fchela

More Related