Global Payroll Performance Optimisation - II David Kurtz
Global Payroll Performance Optimisation - II David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk Who Am I? • Oracle Database Specialist – Independent consultant • Performance tuning – PeopleSoft ERP – Oracle RDBMS • Book – www.psftdba.com • UKOUG Director • Server Tech & PeopleSoft • Oak Table • Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 2 Agenda • • • • • • ‘Streaming’ –Parallel processing Data Volume Read Consistency Partitioning Reporting Archiving Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 3 Warning • This is an unashamedly technical session. • I am going to talk about database internals. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 4 Size matters! Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 5 Parallel processing • All modern machines have multiple processors, – most of the processors have multiple cores. – Even the CPU in my 4 year old laptop has a 2 core CPU. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 6 Database Parallelism • All objects in the PeopleSoft schema are explicitly set NOPARALLEL – Indexes are built parallel, but later reset. – Can invoke parallel query with PARALLEL hint – Parallel insert in direct path model – Parallel DML only works on partitioned objects • 1 PQ slave per partition Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 7 PeopleSoft Batch Programs • Only run on one CPU at any one time. • Client Server processes – Program (COBOL or Application Engine) – Database (eg. Oracle) • Either busy executing COBOL or waiting for the database. – If your payroll calculation is a single process you are not getting value for money! Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 8 Payroll ‘Streaming’ • Several GP processes can be split up. – Each piece processes a distinct set of employees • Range of EMPLID – The pieces can be run concurrently. – Maximum number of streams determined by hardware. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 9 Streamable Processes • COBOL – Payroll Calculation • Application Engine – – – – Banking Preparation GL Preparation EDI Preparation Payslip Preparation • Database Intensive Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 10 Payroll ‘Streaming’ Challenges • Payroll isn’t over until the last stream completes. – Streams need to be evenly balanced. – Employee churn? • One global definition of streams – Balance for largest payroll? • Inter-stream contention – Shared working storage tables in COBOL Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 11 Payroll Calculation Process Phases • Identify – Populate working storage and some result tables • Database Intensive • Calculation – COBOL Intensive • Cancellation – Delete results • Database Intensive Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 12 How Many Streams? • In a well tuned systems, the payroll calculation phase spends about – 2/3 of its time in COBOL – 1/3 on the database. • Number of streams should not exceed – 3 * CPU on database server – 1.5 * CPU on Process Scheduler server • Payroll identification process is database intensive. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 13 How Many Streams? • First GP I ever worked on – 20 CPUs on Application/Batch server – 20 CPUs on Database server • Maximum number of streams? – 20 / 1/3 = 60 on database server – 20 / 2/3 = 30 on Application server • So we used 30 streams – Application server fully utilised during payroll calc – Database about 50% during calc, – Probably overloaded during identification. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 14 How Many Streams? • Optimise number of streams for calculation phase. • Restrict concurrency of database intensive process on process scheduler. – To limit CPU consumption, and possibly also I/O contention. • Consider use of Oracle Resource Manager – Mainly for Payroll identification – I’ve never had to do this myself. – Cancellation will be restricted by I/O Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 15 Balancing Streams • Balance employees across streams on basis of – 80% number of payroll segments per stream – 20% number of JOB history rows • Longer serving employees in earlier streams likely to have more payroll segment and job history. – Make allowance for employee churn. • You will need to periodically rebalance the streams. – Balance for the largest payroll. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 16 Employee Churn • EMPLID is allocated as an accession number. • Streams are a range of EMPLIDs – New employees are hired into the last stream – Employees are terminated across all streams • Over time the streams will go out of balance – Last stream will take longest • Periodically rebalance the streams Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 17 Bulk Churn Effects • Migration – If migrated to GP in tranches then order of migration could affect stream balance • Company merger/divestment history can affect balance of payroll. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 18 Rebalancing the streams? • Calculate new stream range values – Allow space for estimated future growth • Rebuild all range partitioned tables – Half the I/O of partition merge/split – About 42 tables in UK tables. – Need working storage space to do this Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 19 Reversing the EMPLID • Reverse the EMPLID – Instead of EMPLID 0000012345 – Use EMPLID 543210000 • Streams stay balanced because new employees hired across range • Improved search performance across HCM • BUT you must do this before you go live! Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 20 Reversing the EMPLID • • • • • • • • 0000012345 0000012346 0000012347 0000012348 0000012349 0000012350 0000012351 0000012352 Global Payroll Performance Optimisation • • • • • • • • 5432100000 6432100000 7432100000 8432100000 9432100000 0532100000 1532100000 2532100000 ©2011 www.go-faster.co.uk 21 Inter-stream Contention • Streams are just ranges of EMPLIDs. • Oracle inserts data into the first available block (roughly speaking) • Multiple streams insert data simultaneously into the same data blocks in result tables. • Payroll cancel/recalculation deletes from result tables. • Multiple transactions concurrently update different rows in the same block. – On Oracle/SQL Server >=2005: No locking, streams continue to run, but read consistency processing is expensive – Other database can experience page level locking Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 22 Working Storage Tables • COBOL – One shared instance of each working storage table • Shared SQL – Candidate for Global – Temporary Table so one instance per session Global Payroll Performance Optimisation • Application Engine – PeopleSoft Temporary Record – One instance of record per process • Different SQL • Still consider GTT to reduce redo ©2011 www.go-faster.co.uk 23 Read Consistency • The data set that you query remains the same throughout the life of your query. – If somebody else updates data that you are reading (and commits), after your query starts, then you see the original value. • Thus, readers do not block writers or vice versa. • Oracle has always done this, like this since 1990. • SQL Server 2005 has ‘read committed snapshot’ option • Other databases either block or can permit ‘dirty read’. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 24 Read Consistency • Oracle achieves this by storing ‘undo’ information for every change – Recovers ‘read-consistent’ in-memory copy of data block to point in time when query started. – A good reason for buying Oracle – Resource intensive process – Performance problem if abused. • Global Payroll is the perfect storm! Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 25 Read Consistency • Query @ 10023 • Update @ 10024 Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 26 Avoiding Inter-stream Contention • Prevent different streams accessing the same data blocks – Range Partition result tables to match stream ranges – Use Global Temporary Tables (Oracle) for working storage tables – Partition these also on other platforms. • Now different streams access different partitions. • No code change, a job for the DBA – licensed option on most platforms Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 27 Partitioning • Partitioned Table – Different physical components • Value of data determines physical location – Logically still one table – Transparent to application – Rather like a multi-part encyclopaedia. • Partition Elimination Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 28 What is Partitioning? • Typically used in DSS • But can also be effective in OLTP Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 29 Partitioning • Keep similar things together – Employees for one stream in on partition Global Payroll Performance Optimisation • Keep different things apart – Only one transaction in each block of each segment – No need for read consistency ©2011 www.go-faster.co.uk 30 Partitioning GP Recommendation • Range Partitioning – EMPLID – to match streams • List Sub-partition – CAL_RUN_ID – calendar group ID. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 31 Secondary Benefits • CAL_RUN_ID list sub-partition • Easier to archive later – Historical partitions – Different Tablespaces – Different Data Files • • • • Old data on slower disk Read Only Less frequent back-up of read-only tables Faster Backup Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 32 Global Temporary Tables • Global because the data is private • Temporary because the definition is permanent Global Payroll Performance Optimisation • Global because everyone can see the definition • Temporary because physical existance of the table is temporary so it does not need to be recovered. ©2011 www.go-faster.co.uk 33 Global Temporary Tables • A temporary object – No redo generation • But there is undo, and there is redo on the undo! – Each session gets its own physical copy. • Again no read consistency problems • No high water mark issues • Lower high water marks – less I/O Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 34 Building the DDL • Demonstrate GFCBUILD utility. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 35 Group Lists • Specify a list of individual EMPLIDs for whom to run pay calc or another process. • Some customers have experienced problems when run groups shortly before or during larger batch payroll calculations. • Why? Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 36 Cost Based Optimizer • SQL Execution Plan Caching • Bind Variable Peeking during Parse • Different Plan for Group List – Because different bind variables • But plan cached and gets used for main pay calculation which then runs longer than usual! Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 37 Plan Stability • Remember the good plan used by large payroll. • Force it to be used for all payrolls including group list. – Data Volumes small so poor plan won’t really matter. • Oracle Stored Outline – No code change, DBA can implement. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 38 Plan Stability • Collect and applied stored outline with database trigger – http://www.gofaster.co.uk/gpdoc.htm#gp.stored_outlines • Use Active Session History to demonstrate the problem and solution Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 39 Capture Stored Outline CREATE OR REPLACE TRIGGER sysadm.gfc_create_stored_outlines BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst FOR EACH ROW WHEN (new.prcsname = 'GPPDPRUN' AND (new.runstatus = 7 OR old.runstatus = 7)) DECLARE l_sql VARCHAR2(100); BEGIN l_sql := 'ALTER SESSION SET create_stored_outlines = '; IF :new.runstatus = 7 THEN EXECUTE IMMEDIATE l_sql||:new.prcsname; ELSIF :old.runstatus = 7 THEN EXECUTE IMMEDIATE l_sql||'FALSE'; END IF; --because I dont want to crash the process scheduler EXCEPTION WHEN OTHERS THEN NULL; END; / Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 40 Apply Stored Outline CREATE OR REPLACE TRIGGER sysadm.gfc_use_stored_outlines BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst FOR EACH ROW WHEN (new.prcsname = 'GPPDPRUN' AND (new.runstatus = 7 OR old.runstatus = 7)) DECLARE l_sql VARCHAR2(100); BEGIN l_sql := 'ALTER SESSION SET use_stored_outlines = '; IF :new.runstatus = 7 THEN EXECUTE IMMEDIATE l_sql||:new.prcsname; ELSIF :old.runstatus = 7 THEN EXECUTE IMMEDIATE l_sql||'FALSE'; END IF; --because I dont want to crash the process scheduler EXCEPTION WHEN OTHERS THEN NULL; END; / Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 41 Three Scenarios Compared Large / Small / Plan Stable Small SQL_ID SCENARIO 1 ASH_SECS SCENARIO 2 ASH_SECS SCENARIO 3 ASH_SECS ------------- ------------------- ---------- ------------ ---------- ------------ ---------4uzmzh74rdrnz 2514155560 280 3829487612 28750 **SAME** 5023 4n482cm7r9qyn 1595742310 680 869376931 140 **SAME** 889 2f66y2u54ru1v 1145975676 630 **SAME** 531 1n2dfvb3jrn2m 1293172177 150 **SAME** 150 652y9682bqqvp 3325291917 30 **SAME** 110 d8gxmqp2zydta 1716202706 10 678016679 10 **SAME** 32 2np47twhd5nga 3496258537 10 **SAME** 27 4ru0618dswz3y 2621940820 10 539127764 22 4ru0618dswz3y 539127764 100 **SAME** 22 4ru0618dswz3y 3325291917 10 539127764 22 4ru0618dswz3y 1403673054 110 539127764 22 gnnu2hfkjm2yd 1559321680 80 **SAME** 19 fxz4z38pybu3x 1478656524 30 4036143672 18 2xkjjwvmyf99c 1393004311 20 **SAME** 18 a05wrd51zy3kj 2641254321 10 **SAME** 15 Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 42 Data Volume • Payroll generates a lot of data. • Every pay period it generates more data. • Partitioning can offer ways of accessing the data you want quickly – Without having to trawl through data you don’t want. • Need to consider how long you need data – Do you still need data from last tax year? Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 43 Archiving • Put the data you do need to keep into a reporting table – Remove data from the live result tables – Partitioning can help you move/delete this data efficiently – May need to rebuild tables where you have to use DELETE • Reduced data volumes should improve performance of reports. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 44 Reporting • Payroll result tables delivered with single index – Not suitably indexed for all reporting requirements • Particularly single PIN queries – Adding more indexes would degrade calculation performance – Consider generating reporting table • Subset of data, and indexed as necessary. Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 45 GFC_GPRPTGEN • Reporting Table for Single Pin Queries – List Partitioned by Pin – One Partition for each Pin • Incremental Maintenance by Application Engine – Uses Parallel DML to maintain reporting table. – Sub-Paritioned GP Result Tables may still be faster for single employee, single calendar group ID queries! Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 46 Further Reading • Configuring and Operating Streamed Processing in PeopleSoft Global Payroll – www.gofaster.co.uk/gpdocs.htm#Configuring_Operating_Streamed_Payrol l • Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART Package – www.gofaster.co.uk/gpdocs.htm#Managing_Oracle_Table_Partitioning • Use of Oracle Plan Stability (Stored Outlines) in PeopleSoft Global Payroll – www.go-faster.co.uk/gpdocs.htm#gp.stored.outlines Global Payroll Performance Optimisation ©2011 www.go-faster.co.uk 47 Questions?
© Copyright 2024