Listed below is a query (pulled from a trace file of our weekly
payroll processing) from our ADP/CSS (PeopleSoft) Payroll System and
four different query plans that the query has taken in the past couple
of runs. When we where in testing the job was taking about 4-6 hours.
It is now taking 14-20 hours. In three of the four listed Execution
Plans the plan is the same but the distribution of records for each
nested loop is different. Can anyone explain what may be causing this
behavior and poor performance ???
Any help would be greatly appreciated !!!!!!
Some Stats: (I can supply more if you need them)
Oracle 7.1.6 (supplied by the vendor).
AIX 4.2 RS/6000 J40 SMP 384MB.
Statistics are analyzed nightly.
I am running the choose based optimizer.
- QUERY ********************************
SELECT A.PAGE# ,A.LINE# ,A.SEPCHK ,A.ADDL# ,C.COMPANY ,C.STATE
,C.LOCALITY ,
C.TAX_CLASS ,C.QTRCD ,C.MONTHCD ,C.TXGRS_YTD ,C.TAX_YTD ,C.TXGRS_QTD
,
C.TAX_QTD ,C.TXGRS_MTD ,C.TAX_MTD ,C.NLGRS_YTD ,A.COMPANY
,A.PAYGROUP ,
A.PAY_END_DT ,A.OFF_CYCLE
FROM
PS_PAY_EARNINGS A ,PS_TAX_BALANCE C WHERE A.COMPANY=:1 AND
A.PAYGROUP=:2
AND A.PAY_END_DT=:3 AND A.OFF_CYCLE=:4 AND A.SINGLE_CHECK_USE IN
('N','C')
AND A.OK_TO_PAY='Y' AND A.PAY_LINE_STATUS IN ('I', 'P', 'U') AND
A.EMPLID=C.EMPLID AND C.CALENDAR_YEAR=:5 ORDER BY A.PAGE# ASC
,A.LINE#
ASC ,A.SEPCHK ASC ,A.ADDL# ASC ,C.COMPANY ASC ,C.STATE ASC
,C.LOCALITY ASC ,
C.TAX_CLASS ASC ,C.QTRCD DESC ,C.MONTHCD DESC
- EXECUTION PLAN (1) <<< BEST >>>
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT HINT: CHOOSE
1484 SORT (ORDER BY)
0 CONCATENATION
0 NESTED LOOPS
118 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
119 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
0 NESTED LOOPS
118 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
119 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
1484 NESTED LOOPS
118 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
119 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
1484 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
1602 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
- EXECUTION PLAN < 2 > << BAD >>
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT HINT: CHOOSE
4672 SORT (ORDER BY)
0 CONCATENATION
0 NESTED LOOPS
131 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
132 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
4672 NESTED LOOPS
131 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
132 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
4672 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
4803 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
0 NESTED LOOPS
131 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
132 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
- EXECUTION PLAN < 3 > << BAD >>
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT HINT: CHOOSE
5355 SORT (ORDER BY)
5355 NESTED LOOPS
138 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
5355 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
5493 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
- EXECUTION PLAN < 4 > << WORST >>
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT HINT: CHOOSE
4654 SORT (ORDER BY)
4654 CONCATENATION
875815 NESTED LOOPS
875815 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
127 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
127 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
128 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
0 NESTED LOOPS
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
0 NESTED LOOPS
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_PAY_EARNINGS'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9PAY_EARNINGS'
(UNIQUE)
0 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF
'PS_TAX_BALANCE'
0 INDEX HINT: ANALYZED (RANGE SCAN) OF 'PS9TAX_BALANCE'
(UNIQUE)
Bill Timm
Asplundh Tree Expert Co.
voice: (215) 784-1457
fax: (215) 784-1366
e-mail bill_at_asplundh.com
************************************
Received on Tue Jul 08 1997 - 00:00:00 CDT
