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 Instability on 10.2.0.2

Re: Optimizer Instability on 10.2.0.2

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 25 Apr 2007 14:22:36 +0100
Message-ID: <grmdnTST3I4ay7LbRVnyiQA@bt.com>

<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.html
Received on Wed Apr 25 2007 - 08:22:36 CDT

Original text of this message

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