Re: Optimizer Instability on 10.2.0.2

<mccmx_at_hotmail.com> wrote in message
news:1177058349.821284.67800_at_p77g2000hsh.googlegroups.com...
> Oracle 10.2.0.2 on Windows 2003 (EM64T)
>
> One of our batch processes that runs every 30 minutes is fluctuating
> between fast and slow execution times. 50% of the time it runs in 1
> minute and the other 50% it runs for 10 minutes. The behaviour is
> erratic and there doesn't appear to be a pattern in the change in
> execution time. (There is a gap between 23:00 and 06:00 when it
> doesn't run).
>
>
> SELECT DISTINCT I01.EMPLID, I01.EMPL_RCD, I02.PER_ORG,
> TO_CHAR(I02.EFFDT,
> 'YYYY-MM-DD'), I02.EFFSEQ
> from
> PS_AUD_JOB I01, PS_JOB I02 where I01.AUDIT_STAMP > :1 and
> I01.EMPLID
> = I02.EMPLID and I01.EMPL_RCD = I02.EMPL_RCD and
> I02.JOB_INDICATOR = 'P' and (I02.PER_ORG = 'EMP' or :2 =
> 'TMMFCHRO')
> and I02.COMPANY IN (SELECT MST.COMPANY FROM PS_HRMSSI_COMPANY
> MST WHERE
> MST.HRMSSI_ID = :2) and ( I02.EFFDT = (SELECT MAX(A_ED1.EFFDT)
> FROM
> PS_JOB A_ED1 WHERE I02.EMPLID = A_ED1.EMPLID AND I02.EMPL_RCD =
> A_ED1.EMPL_RCD) AND I02.EFFSEQ = (SELECT MAX(A_ES1.EFFSEQ) FROM
> PS_JOB
> A_ES1 WHERE I02.EMPLID = A_ES1.EMPLID AND I02.EMPL_RCD =
> A_ES1.EMPL_RCD AND
> I02.EFFDT = A_ES1.EFFDT) ) ORDER BY I01.EMPLID
>
> Rows Row Source Operation
> ------- ---------------------------------------------------

> 93 NESTED LOOPS (cr=282 pr=0 pw=0 time=2324 us)
> 6 TABLE ACCESS BY INDEX ROWID PS_AUD_JOB (cr=3 pr=0 pw=0
> time=46 us)
> 6 INDEX RANGE SCAN PSBAUD_JOB (cr=2 pr=0 pw=0 time=28 us)
> (object id 69663008)
> 93 TABLE ACCESS BY INDEX ROWID PS_JOB (cr=279 pr=0 pw=0
> time=1787 us)
> 93 INDEX RANGE SCAN PSAJOB (cr=203 pr=0 pw=0 time=161 us)
> (object id 69663591)
> 93 SORT AGGREGATE (cr=188 pr=0 pw=0 time=966 us)
> 93 FIRST ROW (cr=188 pr=0 pw=0 time=689 us)
> 93 INDEX RANGE SCAN (MIN/MAX) PSAJOB (cr=188 pr=0 pw=0

> 67 HASH JOIN (cr=27702 pr=19559 pw=0 time=186768875 us)
> 4 TABLE ACCESS BY INDEX ROWID PS_AUD_JOB (cr=4 pr=1 pw=0
> time=44 us)
> 4 INDEX RANGE SCAN PSBAUD_JOB (cr=2 pr=0 pw=0 time=13
> us)(object id 69663008)
> 340719 TABLE ACCESS FULL PS_JOB (cr=27698 pr=19558 pw=0
> time=18413028 us)

It would be useful to get the two different execution plans from v$sql_plan so that we could see what Oracle thinks is going to happen with the hash join.

It is possible that different values for the :1 bind variable result in significantly different estimates of the data volume selected by

    I01.AUDIT_STAMP > :1 This could be sufficient to switch the optimizer from a nested loop join to a hash join. That being the case, you just need to have a sufficiently busy system that the plan get aged out from time to time, and you get this type of switch on the re-optimisation.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html