Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer Instability on 10.2.0.2
<mccmx_at_hotmail.com> wrote in message
news:1177428237.404718.288760_at_s33g2000prh.googlegroups.com...
>
> The example below shows that we had a HARD parse (misses in the
> library cache = 1) but we are still Full Table Scanning the large
> table even though we are only retrieving 12 rows from PS_AUD_JOB.
> That suggests to me that this query was not finding an inefficient
> plan in the library cache, but rather it was selecting an inefficient
> plan itself. I am suspecting a problem with bind variable peeking
> because I enabled a 10053 trace and I can see occurences of "Bind#0 No
> oacdef for this bind".
>
> SELECT DISTINCT I01.EMPLID, I01.EMPL_RCD, I02.PER_ORG,
> TO_CHAR(I02.EFFDT,
> 'YYYY-MM-DD'), I02.EFFSEQ
> from
> PS_AUD_JOB_JR I01, PS_JOB I02 where I01.AUDIT_STAMP > :1 and
> I01.EMPLID = I02.EMPLID and I01.EMPL_RCD = I02.EMPL_RCD
Yes - misses in library cache mean "no matching plan found, optimisation took place to generate a plan".
If the optimizer couldn't see the bind variable - there were a couple of bugs that used to cause this with the JDBC thin driver, for example - for the clause
where I01.AUDIT_STAMP > :1
then Oracle is going to use the default 5% calculation,
which could explain the tablescan.
-- 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.htmlReceived on Wed Apr 25 2007 - 08:22:36 CDT