1 / 59

PeopleSoft for the Oracle DBA

PeopleSoft for the Oracle DBA. Session 627 david.kurtz@go-faster.co.uk www.go-faster.co.uk. 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).

sari
Download Presentation

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. PeopleSoft for the Oracle DBA Session 627 david.kurtz@go-faster.co.uk www.go-faster.co.uk

  2. 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) HRMS 8 Global Payroll PeopleTools 8.15 All Users via PIA Project Overview

  3. HP-UX 11 64-bit Clustered Servers HP Service Guard Oracle 8.0.5 PT 7.59 Windows and Web Clients Oracle 8.1.7.2 PT 8.15.01 PeopleSoft Internet Architecture Technical Overview

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

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

  6. DBA Team • Good Administrative Practice • Performance Tuning

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

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

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

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

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

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

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

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

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

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

  17. Tools Table -v- DB Catalogue

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

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

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

  21. User Specified Index

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

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

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

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

  26. DDL Models • System-wide default storage options

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

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

  29. DDL Model • PSDDLMODEL

  30. Default Parameters • PSDDLDEFPARMS

  31. Record Parameter Overrides • PSRECDDLPARM

  32. Index Parameter Overrides • PSIDXDDLPARM

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

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

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

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

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

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

  39. 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 • Even faster truncate

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

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

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

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

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

  45. Enabling Client Tracing

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

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

  48. Application Engine / SQR • What you see in the code is what you get • All PS programs can be traced

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

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

More Related