Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer HELP !!!!!

Re: Optimizer HELP !!!!!

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/07/08
Message-ID: <33C2BC53.2592@iol.ie>

Bill,

   The following may be inferred from your statistics:

  1. Your "best" plan in fact returned (and accessed) the fewest rows, which is why it was the fastest.
  2. The two others which have the same plan would be just as good (i.e. bad) if they returned (and accessed) the same number of rows
  3. The other plan appears to have been for a statement with only a single value for A.PAY_LINE_STATUS. The same indexes are being usd.
  4. There is no way that such a run should take the sort of times you are talking about. With the row counts given I would expect it to take less than 20 minutes on my Mac PowerBook!
  5. You shouldn't need to analyze statistics nightly: for most tables a change of less than 20% in table composition is insignificant to the optimiser.

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:

>
> 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US