Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer HELP !!!!!
Bill,
The following may be inferred from your statistics:
You don't give the definitions of the indexes being used, but my guess is that PS9PAY_EARNINGS has a leading column of PAY_LINE_STATUS, since this would result in the concatenation of access paths. If this is the case, GET RID OF IT!
The indexes you need are:
PS_PAY_EARNINGS (COMPANY, PAYGROUP, PAY_END_DT [,OFF_CYCLE, ...]) and
PS_TAX_BALANCE (EMPLID, CALENDAR_YEAR[,...])
Hope this helps.
-- Chrysalis FABRICATI DIEM, PVNC ('To Protect and to Serve') Terry Pratchett : "Guards Guards" Bill wrote:Received on Tue Jul 08 1997 - 00:00:00 CDT
>
> 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
> ************************************