RE: PeopleSoft query
Date: Mon, 13 Jul 2009 09:59:08 +0100
Message-ID: <3E98DAC00C4141ACA1F76C09B9DB67FB_at_GOFASTER4>
Karl
This is a 'stored statement' from the Global Payroll calculation process,
GPPDPRUN.
It is looking for retroactive changes (new changes that are effective in
previous pay periods).
Was the process run with the 'update stats' option? PS_GP_PYE_STAT_WRK is a working storage table. It holds the employees to be processed (because sometimes you only do an incremental paycalc). GPPDPRUN can optionally issue an ANALYZE TABLE command (PeopleSoft COBOL programs do not use the DDL models). The option is specified on the second tab of the page where the operator requests the pay calc process.
I have seen this particular statement become a problem before, it is called GPPSERV_I_HISTWRK. But the problem is that Oracle is full scanning and hash joining S and P because it has started with W and then gone to S before it has gone to P.
My solution was a simple leading hint:
/*+LEADING(WRK P)*/
You could use a stored outline to apply this hint to this statement. There
are no literals inserted into stored statements at runtime, they always use
bind variables. However, the statement is delivered by PeopleSoft in a flat
file that is then loaded into the Stored statement table by their Data Mover
utility. So you can add the hint to the script and reload it. Example
attached, and that is what I normally do.
You could change the Query as Stefane suggested. However, you are changing a piece of core code. You would have to prove to the project/business that you haven't changed the functionality. It is easier to do that when you are only adding a hint.
Because this is vanilla code, PeopleSoft could deliver a new version in a patch or upgrade. Checking for differences in delivered and current versions of stored statements needs to be added to the usual compare procedures.
How many employees are you paying per pay period. How many pay periods have
you got?
(SELECT cal_run_id, count(*), count(distinct emplid) from ps_gp_pye_seg_stat
group by cal_run_id)
Are you running multiple concurrent payroll calculation processes? PeopleSoft call this 'streamed payroll processing'.
regards
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz_at_go-faster.co.uk
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karl Arao
>Sent: Monday, July 13, 2009 8:56 AM
>To: oracle-l_at_freelists.org
>Subject: PeopleSoft query
>Importance: High
>
>We have this Peoplesoft query that now runs for about 18hours.
>This is a payroll batch run and you can see from the SQL that
>is has 8 subqueries.
>Last year we were able to trace (10046 and used OraSrp for the
>profile) the whole batch process, and found out that this
>query is the one consuming half of the payroll runtime.
>You can see below that it is mostly on CPU which could be
>attributed by lots of LIOs on the lines 18,19,22 (and other
>lines), plus the SORT AGGREGATE and HASH JOIN operations.
>Well this profile was from last year, and now the query has
>become slower.
>
>I'm not aware of the PeopleSoft tables and how to approach the
>tuning for this SQL, so I just explained to the
>developers/DBAs the areas where this SQL goes wrong.
>
>If you have a similar environment, and have any ideas on how
>to rewrite this SQL, or just any idea :) I'm all ears.. :)
>
>
>Statement Self Statistics
>Call Cache Misses Count - Seconds - Physical Reads
>- Logical Reads - Rows
> CPU Elapsed Consistent Current
>Parse 1 1 0.0010s 0.0010s 0 0 0 0
>Exec 1 1 5,064.5331s 4,954.4516s 161212 996447290
> 14645 7782
>Fetch 0 0.0000s 0.0000s 0 0 0 0
>Total 2 2 5,064.5341s 4,954.4527s 161212
>996447290 14645 7782
>
>Per Exe 2 2 5,064.5341s 4,954.4527s 161212
>996447290 14645 7782
>Per Row 0 0 0.6508s 0.6367s 20.7 128045.1
> 1.9 1
>
>
>Statement Flat Profile
>Event Name % Time Seconds Calls - Time per Call -
> Avg Min Max
>EXEC calls [CPU] 99.80% 5,064.5331s 1 5,064.5331s
>5,064.5331s 5,064.5331s
>db file sequential read 0.20% 11.7945s 153411 0.0000s
> 0.0000s 0.3199s
>db file scattered read 0.00% 0.1724s 2362 0.0000s
>0.0000s 0.0003s
>latch: cache buffers chains 0.00% 0.0019s 10 0.0001s
>0.0000s 0.0008s
>SQL*Net message from client 0.00% 0.0014s 1 0.0014s
>0.0014s 0.0014s
>PARSE calls [CPU] 0.00% 0.0010s 1 0.0010s
>0.0010s 0.0010s
>SQL*Net more data from client 0.00% 0.0000s 1 0.0000s
> 0.0000s 0.0000s
>Total 100.00% 5,076.5044s
>
>
>Line# Rows Row Source Operation Object Id
> 1 7782 FILTER (cr=996,447,060 pr=160,234 pw=0 time=7,698.4330s)
> 2 87827917 FILTER (cr=122,611 pr=11,464 pw=0 time=357.2273s)
> 3 87827917 HASH JOIN (cr=122,611 pr=11,464 pw=0 time=182.7554s)
> 4 141462 TABLE ACCESS FULL PS_GP_PYE_STAT_WRK (cr=3,695 pr=0 pw=0
time=0.2830s) 142612
> 5 2438029 HASH JOIN (cr=118,916 pr=11,464 pw=0 time=20.3213s)
> 6 2438029 TABLE ACCESS FULL PS_GP_PYE_SEG_STAT (cr=50,660 pr=3
pw=0 time=4.8762s) 142605
> 7 2557662 TABLE ACCESS FULL PS_GP_PYE_PRC_STAT (cr=68,256
pr=11,461 pw=0 time=7.7007s) 142591
> 8 887567 SORT AGGREGATE (cr=2,662,721 pr=1,396 pw=0
time=28.5478s)
>9 887567 FIRST ROW (cr=2,662,721 pr=1,396 pw=0 time=20.3392s)
>10 887567 INDEX RANGE SCAN (MIN/MAX) PSAGP_PYE_STAT_WRK (cr=2,662,721
pr=1,396 pw=0 time=14.3338s) 959498
>11 1084735 SORT AGGREGATE (cr=3,254,205 pr=88 pw=0 time=32.0421s)
>12 1084735 FIRST ROW (cr=3,254,205 pr=88 pw=0 time=22.3453s)
>13 1084735 INDEX RANGE SCAN (MIN/MAX) PSAGP_PYE_STAT_WRK
>(cr=3,254,205 pr=88 pw=0 time=15.1136s) 959498
>14 1085219 SORT AGGREGATE (cr=3,255,657 pr=17 pw=0 time=31.2155s)
>15 1085219 FIRST ROW (cr=3,255,657 pr=17 pw=0 time=21.6912s)
>16 1085219 INDEX RANGE SCAN (MIN/MAX) PSAGP_PYE_STAT_WRK
>(cr=3,255,657 pr=17 pw=0 time=14.5179s) 959498
>17 1234828 SORT AGGREGATE (cr=985,415,180 pr=139,205 pw=0
>time=4,728.3851s)
>18 50397201 TABLE ACCESS BY INDEX ROWID PS_GP_PYE_SEG_STAT
>(cr=985,415,180 pr=139,205 pw=0 time=5,111.6081s) 142605
>19 836763897 NESTED LOOPS (cr=205,863,657 pr=120,197 pw=0
>time=8,381.8120s)
>20 49252959 TABLE ACCESS BY INDEX ROWID PS_GP_PYE_PRC_STAT
>(cr=53,424,422 pr=101,222 pw=0 time=518.0261s) 142591
>21 49253013 INDEX RANGE SCAN PSAGP_PYE_PRC_STAT (cr=4,173,271
>pr=36,966 pw=0 time=169.8647s) 449990
>22 786276110 INDEX RANGE SCAN IDX$$_45070002 (cr=152,439,235
>pr=18,975 pw=0 time=2,170.1565s) 1316150
>23 48619 SORT AGGREGATE (cr=1,108,367 pr=6,857 pw=0 time=9.3085s)
>24 958533 TABLE ACCESS BY INDEX ROWID PS_GP_PYE_PRC_STAT
>(cr=1,108,367 pr=6,857 pw=0 time=12.3917s) 142591
>25 958533 INDEX RANGE SCAN PSDGP_PYE_PRC_STAT (cr=149,841
>pr=4,686 pw=0 time=6.4900s) 449993
>26 23665 SORT AGGREGATE (cr=458,952 pr=380 pw=0 time=2.0538s)
>27 359657 TABLE ACCESS BY INDEX ROWID PS_GP_PYE_PRC_STAT
>(cr=458,952 pr=380 pw=0 time=3.0068s) 142591
>28 386645 INDEX RANGE SCAN PSDGP_PYE_PRC_STAT (cr=72,313 pr=68
>pw=0 time=1.0443s) 449993
>29 7782 SORT AGGREGATE (cr=84,228 pr=13 pw=0 time=0.4258s)
>30 7784 TABLE ACCESS BY INDEX ROWID PS_GP_PYE_PRC_STAT
>(cr=84,228 pr=13 pw=0 time=0.3551s) 142591
>31 60649 INDEX RANGE SCAN PSDGP_PYE_PRC_STAT (cr=23,582
>pr=11 pw=0 time=0.2054s) 449993
>32 7782 SORT AGGREGATE (cr=85,139 pr=814 pw=0 time=0.5396s)
>33 7908 TABLE ACCESS BY INDEX ROWID PS_GP_PYE_SEG_STAT
>(cr=85,139 pr=814 pw=0 time=0.4688s) 142605
>34 61729 INDEX RANGE SCAN IDX$$_45070002
>(cr=23,749 pr=791 pw=0 time=0.2810s) 1316150
>
>
>
>-- THIS IS THE SQL STATEMENT
>
>INSERT INTO PS_GP_PYE_HIST_WRK
> (EMPLID,
> EMPL_RCD,
> GP_PAYGROUP,
> CAL_ID,
> ORIG_CAL_RUN_ID,
> RSLT_SEG_NUM,
> CAL_RUN_ID,
> RUN_CNTL_ID,
> OPRID,
> HIST_CAL_RUN_ID,
> PRC_ORD_TS,
> CURRENCY_CD,
> CALC_TYPE,
> HIST_TYPE)
> SELECT P.EMPLID,
> P.EMPL_RCD,
> P.GP_PAYGROUP,
> P.CAL_ID,
> P.ORIG_CAL_RUN_ID,
> S.RSLT_SEG_NUM,
> WRK.CAL_RUN_ID,
> WRK.RUN_CNTL_ID,
> WRK.OPRID,
> P.CAL_RUN_ID,
> P.PRC_ORD_TS,
> P.CURRENCY_CD,
> P.CALC_TYPE,
> 'C'
> FROM PS_GP_PYE_STAT_WRK WRK, PS_GP_PYE_PRC_STAT P,
>PS_GP_PYE_SEG_STAT S
> WHERE WRK.EMPLID BETWEEN :1 AND :2
> AND WRK.CAL_RUN_ID = :3
> AND WRK.RUN_CNTL_ID = :4
> AND WRK.OPRID = :5
> AND WRK.PRC_ORD_TS =
> (SELECT MIN(SUB.PRC_ORD_TS)
> FROM PS_GP_PYE_STAT_WRK SUB
> WHERE SUB.EMPLID = WRK.EMPLID
> AND SUB.CAL_RUN_ID = WRK.CAL_RUN_ID)
> AND WRK.EMPL_RCD =
> (SELECT MIN(SUB.EMPL_RCD)
> FROM PS_GP_PYE_STAT_WRK SUB
> WHERE SUB.EMPLID = WRK.EMPLID
> AND SUB.CAL_RUN_ID = WRK.CAL_RUN_ID
> AND SUB.PRC_ORD_TS = WRK.PRC_ORD_TS)
> AND WRK.RSLT_SEG_NUM =
> (SELECT MIN(SUB.RSLT_SEG_NUM)
> FROM PS_GP_PYE_STAT_WRK SUB
> WHERE SUB.EMPLID = WRK.EMPLID
> AND SUB.CAL_RUN_ID = WRK.CAL_RUN_ID
> AND SUB.PRC_ORD_TS = WRK.PRC_ORD_TS
> AND SUB.EMPL_RCD = WRK.EMPL_RCD)
> AND WRK.EMPLID = P.EMPLID
> AND P.SEL_STAT IN ('A', 'I')
> AND P.PRC_ORD_TS =
> (SELECT MAX(SUBP.PRC_ORD_TS)
> FROM PS_GP_PYE_PRC_STAT SUBP, PS_GP_PYE_SEG_STAT SUBS
> WHERE SUBP.EMPLID = P.EMPLID
> AND SUBP.COUNTRY = WRK.COUNTRY
> AND SUBP.CALC_TYPE = P.CALC_TYPE
> AND SUBP.SEL_STAT IN ('A', 'I')
> AND SUBP.PRC_ORD_TS < WRK.PRC_ORD_TS
> AND SUBP.EMPLID = SUBS.EMPLID
> AND SUBP.CAL_RUN_ID = SUBS.CAL_RUN_ID
> AND SUBP.EMPL_RCD = SUBS.EMPL_RCD
> AND SUBP.CAL_ID = SUBS.CAL_ID
> AND SUBP.GP_PAYGROUP = SUBS.GP_PAYGROUP
> AND SUBP.ORIG_CAL_RUN_ID = SUBS.ORIG_CAL_RUN_ID
> AND SUBS.PYE_CALC_STAT IN ('70', '75'))
> AND P.RSLT_VER_NUM =
> (SELECT MAX(SUB.RSLT_VER_NUM)
> FROM PS_GP_PYE_PRC_STAT SUB
> WHERE SUB.EMPLID = P.EMPLID
> AND SUB.COUNTRY = WRK.COUNTRY
> AND SUB.CALC_TYPE = P.CALC_TYPE
> AND SUB.PRC_ORD_TS = P.PRC_ORD_TS)
> AND P.RSLT_REV_NUM =
> (SELECT MAX(SUB.RSLT_REV_NUM)
> FROM PS_GP_PYE_PRC_STAT SUB
> WHERE SUB.EMPLID = P.EMPLID
> AND SUB.COUNTRY = WRK.COUNTRY
> AND SUB.CALC_TYPE = P.CALC_TYPE
> AND SUB.PRC_ORD_TS = P.PRC_ORD_TS
> AND SUB.RSLT_VER_NUM = P.RSLT_VER_NUM)
> AND P.EMPL_RCD =
> (SELECT MAX(SUB.EMPL_RCD)
> FROM PS_GP_PYE_PRC_STAT SUB
> WHERE SUB.EMPLID = P.EMPLID
> AND SUB.COUNTRY = WRK.COUNTRY
> AND SUB.CALC_TYPE = P.CALC_TYPE
> AND SUB.PRC_ORD_TS = P.PRC_ORD_TS
> AND SUB.RSLT_VER_NUM = P.RSLT_VER_NUM
> AND SUB.RSLT_REV_NUM = P.RSLT_REV_NUM)
> AND P.EMPLID = S.EMPLID
> AND P.CAL_RUN_ID = S.CAL_RUN_ID
> AND P.EMPL_RCD = S.EMPL_RCD
> AND P.CAL_ID = S.CAL_ID
> AND P.GP_PAYGROUP = S.GP_PAYGROUP
> AND P.ORIG_CAL_RUN_ID = S.ORIG_CAL_RUN_ID
> AND S.PYE_CALC_STAT IN ('70', '75')
> AND S.RSLT_SEG_NUM =
> (SELECT MAX(SUB.RSLT_SEG_NUM)
> FROM PS_GP_PYE_SEG_STAT SUB
> WHERE SUB.EMPLID = S.EMPLID
> AND SUB.CAL_RUN_ID = S.CAL_RUN_ID
> AND SUB.EMPL_RCD = S.EMPL_RCD
> AND SUB.CAL_ID = S.CAL_ID
> AND SUB.GP_PAYGROUP = S.GP_PAYGROUP
> AND SUB.ORIG_CAL_RUN_ID = S.ORIG_CAL_RUN_ID
> AND SUB.PYE_CALC_STAT IN ('70', '75'))
>
>
>
>- Karl Arao
>http://karlarao.wordpress.com
>--
>http://www.freelists.org/webpage/oracle-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 13 2009 - 03:59:08 CDT
- application/octet-stream attachment: GPPSERVC_I_HISTWRK.dms