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

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.000055488 selectivity and bind peeking happens seen from 10053 trace file.

Thanks

--

LSC
--

http://www.freelists.org/webpage/oracle-l Received on Sun Sep 02 2007 - 04:19:34 CDT