peoplesoft for the oracle dba n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
PeopleSoft for the Oracle DBA PowerPoint Presentation
Download Presentation
PeopleSoft for the Oracle DBA

Loading in 2 Seconds...

play fullscreen
1 / 59

PeopleSoft for the Oracle DBA - PowerPoint PPT Presentation


  • 177 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


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