PeopleSoft query

From: Karl Arao <karlarao_at_gmail.com>
Date: Mon, 13 Jul 2009 15:56:10 +0800
Message-ID: <12ee65600907130056u65843d35q8a2d09c4c52c64ee_at_mail.gmail.com>

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

Received on Mon Jul 13 2009 - 02:56:10 CDT

This message: [ Message body ]
Next message: Stephane Faroult: "Re: PeopleSoft query"
Previous message: Vladimir Andreev: "Re: Flash-only Metalink"
Next in thread: Stephane Faroult: "Re: PeopleSoft query"
Reply: Stephane Faroult: "Re: PeopleSoft query"
Reply: David Kurtz: "RE: PeopleSoft query"
Reply: Nuno Souto: "Re: PeopleSoft query"
Maybe reply: David Kurtz: "RE: PeopleSoft query"
Maybe reply: mhthomas: "RE: PeopleSoft query"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message