RE: bind peeking not happen in 9.2.0.8 and cause using default selectivity
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <exriscer@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 2 Sep 2007 11:19:34 +0200
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
Other related posts:
