PeopleSoft query
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 13 Jul 2009 15:56:10 +0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
