Re: PeopleSoft query
From: Karl Arao <karlarao_at_gmail.com>
Date: Mon, 13 Jul 2009 16:32:20 +0800
Message-ID: <12ee65600907130132r60dd1cafl5662b8d5259303b9_at_mail.gmail.com>
For additional info, this is the explain plan from DBMS_XPLAN.DISPLAY_CURSOR
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | INSERT STATEMENT | | |
5 - access("P"."EMPLID"="S"."EMPLID" AND "P"."CAL_RUN_ID"="S"."CAL_RUN_ID" AND "P"."EMPL_RCD"="S"."EMPL_RCD" AND
7 - filter((INTERNAL_FUNCTION("P"."SEL_STAT") AND "P"."EMPLID">=:1 AND "P"."EMPLID"<=:2))
"SUBP"."CAL_ID"="SUBS"."CAL_ID" AND "SUBP"."GP_PAYGROUP"="SUBS"."GP_PAYGROUP")) 20 - filter(("SUBP"."SEL_STAT"='A' OR "SUBP"."SEL_STAT"='I')) 21 - access("SUBP"."EMPLID"=:B1 AND "SUBP"."COUNTRY"=:B2 AND "SUBP"."CALC_TYPE"=:B3 AND
"SUBP"."SYS_NC00038$">SYS_OP_DESCEND(:B4) AND "SUBP"."SYS_NC00038$" IS NOT NULL)
filter(SYS_OP_UNDESCEND("SUBP"."SYS_NC00038$")<:B1) 22 - access("SUBS"."EMPLID"=:B1 AND "SUBP"."CAL_RUN_ID"="SUBS"."CAL_RUN_ID" AND
30 - filter(("SUB"."RSLT_REV_NUM"=:B1 AND "SUB"."CALC_TYPE"=:B2 AND "SUB"."COUNTRY"=:B3))
31 - access("SUB"."EMPLID"=:B1 AND "SUB"."PRC_ORD_TS"=:B2 AND "SUB"."RSLT_VER_NUM"=:B3)
33 - filter(("SUB"."CAL_ID"=:B1 AND "SUB"."GP_PAYGROUP"=:B2 AND "SUB"."EMPL_RCD"=:B3 AND
INTERNAL_FUNCTION("SUB"."PYE_CALC_STAT"))) 34 - access("SUB"."EMPLID"=:B1 AND "SUB"."CAL_RUN_ID"=:B2 AND "SUB"."ORIG_CAL_RUN_ID"=:B3)
Date: Mon, 13 Jul 2009 16:32:20 +0800
Message-ID: <12ee65600907130132r60dd1cafl5662b8d5259303b9_at_mail.gmail.com>
For additional info, this is the explain plan from DBMS_XPLAN.DISPLAY_CURSOR
- IF THE SQL GOES HASH JOIN THE RUNTIME FOR THIS SQL IS ESTIMATED 18HOURS
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time |
| 0 | INSERT STATEMENT | | |
| | 118M(100)| | |* 1 | FILTER | | |
| | | |
|* 2 | FILTER | | |
| | | |
|* 3 | HASH JOIN | | 86M| 16G| 10M| 112K (2)| 00:22:35 | |* 4 | TABLE ACCESS FULL | PS_GP_PYE_STAT_WRK | 173K| 8962K| | 1009 (2)| 00:00:13 | |* 5 | HASH JOIN | | 4104K| 587M| 309M| 79151 (1)| 00:15:50 | |* 6 | TABLE ACCESS FULL | PS_GP_PYE_SEG_STAT
| 4105K| 262M| | 20136 (2)| 00:04:02 |
|* 7 | TABLE ACCESS FULL | PS_GP_PYE_PRC_STAT
| 4059K| 321M| | 25317 (2)| 00:05:04 |
| 8 | SORT AGGREGATE | | 1 |
27 | | | |
| 9 | FIRST ROW | | 21 |
567 | | 3 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN (MIN/MAX) | PSAGP_PYE_STAT_WRK | 21 | 567 | | 3 (0)| 00:00:01 |
| 11 | SORT AGGREGATE | | 1 |
28 | | | |
| 12 | FIRST ROW | | 1 |
28 | | 3 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN (MIN/MAX) | PSAGP_PYE_STAT_WRK | 1 | 28 | | 3 (0)| 00:00:01 |
| 14 | SORT AGGREGATE | | 1 |
30 | | | |
| 15 | FIRST ROW | | 1 |
30 | | 3 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN (MIN/MAX) | PSAGP_PYE_STAT_WRK | 1 | 30 | | 3 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 |
141 | | | | |* 18 | TABLE ACCESS BY INDEX ROWID | PS_GP_PYE_SEG_STAT | 1 | 64 | | 3 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 |
141 | | 71 (0)| 00:00:01 | |* 20 | TABLE ACCESS BY INDEX ROWID | PS_GP_PYE_PRC_STAT | 23 | 1771 | | 4 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | PSAGP_PYE_PRC_STAT | 1 | | | 3 (0)| 00:00:01 | |* 22 | INDEX RANGE SCAN | IDX$$_4B690004
| 1 | | | 2 (0)| 00:00:01 |
| 23 | SORT AGGREGATE | | 1 |
23 | | | |
| 24 | TABLE ACCESS BY INDEX ROWID |
PS_GP_PYE_PRC_STAT | 2 | 46 | | 6 (0)| 00:00:01 | |* 25 | INDEX RANGE SCAN | IDX$$_4B690005 | 2 | | | 3 (0)| 00:00:01 |
| 26 | SORT AGGREGATE | | 1
| 26 | | | |
|* 27 | TABLE ACCESS BY INDEX ROWID | PS_GP_PYE_PRC_STAT | 1 | 26 | | 4 (0)| 00:00:01 | |* 28 | INDEX RANGE SCAN | IDX$$_4B690003 | 1 | | | 3 (0)| 00:00:01 |
| 29 | SORT AGGREGATE | | 1 |
28 | | | | |* 30 | TABLE ACCESS BY INDEX ROWID | PS_GP_PYE_PRC_STAT | 1 | 28 | | 4 (0)| 00:00:01 | |* 31 | INDEX RANGE SCAN | IDX$$_4B690003 | 1 | | | 3 (0)| 00:00:01 |
| 32 | SORT AGGREGATE | | 1 |
67 | | | | |* 33 | TABLE ACCESS BY INDEX ROWID| PS_GP_PYE_SEG_STAT | 1 | 67 | | 4 (0)| 00:00:01 | |* 34 | INDEX RANGE SCAN | IDX$$_4B690004 | 1 | | | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------------------------------
1 - filter(("WRK"."PRC_ORD_TS"= AND "WRK"."EMPL_RCD"= AND "WRK"."RSLT_SEG_NUM"= AND "P"."PRC_ORD_TS"= AND "P"."RSLT_VER_NUM"= AND "P"."RSLT_REV_NUM"= AND "P"."EMPL_RCD"= AND "S"."RSLT_SEG_NUM"=))
2 - filter(:1<=:2)
3 - access("WRK"."EMPLID"="P"."EMPLID")
4 - filter(("WRK"."EMPLID">=:1 AND "WRK"."EMPLID"<=:2 AND
"WRK"."CAL_RUN_ID"=:3 AND "WRK"."RUN_CNTL_ID"=:4 AND
"WRK"."OPRID"=:5))
5 - access("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")
6 - filter((INTERNAL_FUNCTION("S"."PYE_CALC_STAT") AND
"S"."EMPLID">=:1 AND "S"."EMPLID"<=:2))
7 - filter((INTERNAL_FUNCTION("P"."SEL_STAT") AND "P"."EMPLID">=:1 AND "P"."EMPLID"<=:2))
10 - access("SUB"."EMPLID"=:B1 AND "SUB"."CAL_RUN_ID"=:B2)
13 - access("SUB"."EMPLID"=:B1 AND "SUB"."CAL_RUN_ID"=:B2 AND
"SUB"."PRC_ORD_TS"=:B3)
16 - access("SUB"."EMPLID"=:B1 AND "SUB"."CAL_RUN_ID"=:B2 AND
"SUB"."PRC_ORD_TS"=:B3 AND "SUB"."EMPL_RCD"=:B4)
18 - filter((INTERNAL_FUNCTION("SUBS"."PYE_CALC_STAT") AND
"SUBP"."EMPL_RCD"="SUBS"."EMPL_RCD" AND
"SUBP"."CAL_ID"="SUBS"."CAL_ID" AND "SUBP"."GP_PAYGROUP"="SUBS"."GP_PAYGROUP")) 20 - filter(("SUBP"."SEL_STAT"='A' OR "SUBP"."SEL_STAT"='I')) 21 - access("SUBP"."EMPLID"=:B1 AND "SUBP"."COUNTRY"=:B2 AND "SUBP"."CALC_TYPE"=:B3 AND
"SUBP"."SYS_NC00038$">SYS_OP_DESCEND(:B4) AND "SUBP"."SYS_NC00038$" IS NOT NULL)
filter(SYS_OP_UNDESCEND("SUBP"."SYS_NC00038$")<:B1) 22 - access("SUBS"."EMPLID"=:B1 AND "SUBP"."CAL_RUN_ID"="SUBS"."CAL_RUN_ID" AND
"SUBP"."ORIG_CAL_RUN_ID"="SUBS"."ORIG_CAL_RUN_ID")
filter("SUBP"."EMPLID"="SUBS"."EMPLID")
25 - access("SUB"."EMPLID"=:B1 AND "SUB"."PRC_ORD_TS"=:B2 AND
"SUB"."CALC_TYPE"=:B3 AND "SUB"."COUNTRY"=:B4)
27 - filter(("SUB"."CALC_TYPE"=:B1 AND "SUB"."COUNTRY"=:B2))
28 - access("SUB"."EMPLID"=:B1 AND "SUB"."PRC_ORD_TS"=:B2 AND
"SUB"."RSLT_VER_NUM"=:B3)
30 - filter(("SUB"."RSLT_REV_NUM"=:B1 AND "SUB"."CALC_TYPE"=:B2 AND "SUB"."COUNTRY"=:B3))
31 - access("SUB"."EMPLID"=:B1 AND "SUB"."PRC_ORD_TS"=:B2 AND "SUB"."RSLT_VER_NUM"=:B3)
33 - filter(("SUB"."CAL_ID"=:B1 AND "SUB"."GP_PAYGROUP"=:B2 AND "SUB"."EMPL_RCD"=:B3 AND
INTERNAL_FUNCTION("SUB"."PYE_CALC_STAT"))) 34 - access("SUB"."EMPLID"=:B1 AND "SUB"."CAL_RUN_ID"=:B2 AND "SUB"."ORIG_CAL_RUN_ID"=:B3)
- Karl Arao http://karlarao.wordpress.com
On Mon, Jul 13, 2009 at 4:22 PM, Stephane Faroult<sfaroult_at_roughsea.com> wrote:
> Karl, > > Replace all your subqueries by a single one. Use an analytical > function such as rank() (although row_number() could do) to identify all > the "highest RCD for highest REV_NUM for highest VER_NUM" kind of > conditions. > I believe the last subquery should be made uncorrelated, make it a GROUP > BY query, move it up into the FROM clause and join on it. > > Basically, the fewer times each table appears in your query the better ... > > HTH > > S Faroult > > Arao wrote: >> 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. >> > > >
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 13 2009 - 03:32:20 CDT