 | | | -none- | -none- 2007-09-02 - By LS Cheng
Back
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 (See http://ORA-00600.ora-code.com). 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@(protected)> 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
>
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 <a
href="http://9.2.0.8">9.2.0.8</a> so it's ok however the production is
<a href="http://9.2.0.6">9.2.0.6</a> though and setting these events seems that
will cause ORA-00600 (See http://ORA-00600.ora-code.com). I will need have a talk with production guys about
upgrading this to <a href="http://9.2.0.8">9.2.0.8</a> and setting these events
.
<br><br>Also the bug seems apply for implicit function calls as well.<br><br
>Thanks Alvaro.<br><br>--<br>LSC<br><br><br><br><br><br><font color="navy" face=
"Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">
</span></font><br><div><span class="gmail_quote">On 9/2/07, <b class="gmail
_sendername">Alvaro Jose Fernandez</b> <<a href="mailto:alvaro.fernandez
@(protected)">alvaro.fernandez@(protected)</a>> wrote:</span><blockquote class=
"gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt
0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="blue" lang="ES">
<div>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">Hi Cheng,</span></font></p>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">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 <a href="http://9.2.0.8" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">9.2.0.8</a> . Your <a href="http:
//9.2.0.8" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)
">
9.2.0.8</a> is patched with any of them? </span></font></p>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">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 <a href="http://9.2.0.6"
target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">9.2.0.6<
/a>, but</span></font></p>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">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).</span></font></p>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">I would try it
on <a href="http://9.2.0.8" target="_blank" onclick="return top.js.OpenExtLink
(window,event,this)">9.2.0.8</a> after again flushing the shared pool and check
again the 10046/10053
traces not an explain (surely you have already done this). Be warned</span><
/font></p>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">that setting
38056 would cause the cursor to be marked as non-shareable due to unsafe
literals (I've checked this on <a href="http://9.2.0.6" target="_blank" onclick
="return top.js.OpenExtLink(window,event,this)">9.2.0.6</a>, but check oacfl2 on
the trace to
be sure) </span></font></p>
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">regards,</span></font></p><span
class="sg">
<p style="margin-bottom: 12pt;"><font color="navy" face="Times New Roman" size=
"3"><span style="font-size: 12pt; color: navy;">alvaro </span></font></p></span>
<span class="q">
<p style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span
style="font-size: 12pt;"><br>
As we can see the index cardinality (IDX3T1) in <a href="http://9.2.0.8" target
="_blank" onclick="return top.js.OpenExtLink(window,event,this)">9.2.0.8</a>
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.<br>
<br>
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 <a href="http://10.2.0.3" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">10.2.0.3</a> it gets a
proper 0.000055488 selectivity and bind peeking happens seen from 10053 trace
file.<br>
<br>
Thanks<br>
<br>
--<br>
LSC<font color="navy"><span style="color: navy;"></span></font></span></font><
/p>
</span></div>
</div>
</blockquote></div><br>
|
|
|