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

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 9.2.0.8 so it's ok however the production is 9.2.0.6 though and setting these events seems that will cause ORA-00600. I will need have a talk with production guys about upgrading this to 9.2.0.8 and setting these events.

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

Thanks Alvaro.

--
LSC






On 9/2/07, Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com> 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 9.2.0.8 . Your 9.2.0.8 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  9.2.0.6,

> 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 9.2.0.8 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 9.2.0.6, but check oacfl2 on

> the trace to be sure)

>

> regards,

>

> alvaro

>

>

> As we can see the index cardinality (IDX3T1) in 9.2.0.8 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 10.2.0.3 it gets a proper 0.000055488selectivity and bind peeking happens seen from 10053 trace file.

>

> Thanks

>

> --

> LSC

>


--
http://www.freelists.org/webpage/oracle-l