Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: bind peeking not happen in and cause using default selectivity

Re: bind peeking not happen in and cause using default selectivity

From: LS Cheng <>
Date: Sun, 2 Sep 2007 12:37:39 +0200
Message-ID: <>

Aha events 38044 and 38056 did the trick, now it is peeking the variables and get a proper cardinality, by the way the version of the test database is so it's ok however the production is though and setting these events seems that will cause ORA-00600. I will need have a talk with production guys about upgrading this to and setting these events.

Also the bug seems apply for implicit function calls as well.

Thanks Alvaro.


On 9/2/07, Alvaro Jose Fernandez <> wrote:

> Hi Cheng,
> Maybe you would ned to open an SR for this. There are some 9.2 cbo-related
> bugs fixed on CPUs since the release of . Your is patched
> with any of them?
> It would a case for bug "3668224 - Bind peeking does not occur for
> predicates of the form COL FUNC(:bind)" . This is fixed as of,
> but
> the fix is reportedly not enabled by default on 9.2, and it requires
> setting both events 38044 and 38056 , both at level 1 (for example, at the
> session level).
> I would try it on after again flushing the shared pool and check
> again the 10046/10053 traces not an explain (surely you have already done
> this). Be warned
> that setting 38056 would cause the cursor to be marked as non-shareable
> due to unsafe literals (I've checked this on, but check oacfl2 on
> the trace to be sure)
> regards,
> alvaro
> As we can see the index cardinality (IDX3T1) in is incorrect, the
> selectivity used is the default because of bind variables, 0.05 * 0.05. So
> it calculates a cardinality of 463220 * 0.05 * 0.05 which yields 1158 as
> plan shows.
> So my question is, why is the default selectivity is used? Shouldnt bind
> peeking suppose to happen and get a proper selectivity? I tried restarted
> the database, flushed the shared_pool, invalidated the cursor to ensure a
> hard parse but no luck however in it gets a proper 0.000055488selectivity and bind peeking happens seen from 10053 trace file.
> Thanks
> --
Received on Sun Sep 02 2007 - 05:37:39 CDT

Original text of this message