 | | | -none- | -none- 2007-09-02 - By Alvaro Jose Fernandez
Back
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
<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas
-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:blue;
text-decoration:underline;}
p
{mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman";}
address
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";
font-style:italic;}
pre
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.EstiloCorreo17
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@(protected) Section1
{size:595.3pt 841.9pt;
margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=ES link=blue vlink=blue>
<div class=Section1>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;color:navy'>Hi Cheng,<o:p>
</o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;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 9.2.0.8 . Your 9.2.0.8 is patched with any of them?
<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;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 9.2.0.6, but<o:p></o:p>
</span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;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).<o:p></o:p></span><
/font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;color:navy'>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<o:p></o:p>
</span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;color:navy'>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) <o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;color:navy'>regards,<o:p><
/o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3 color=navy
face="Times New Roman"><span style='font-size:12.0pt;color:navy'>alvaro <o:p><
/o:p></span></font></p>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'><br>
As we can see the index cardinality (IDX3T1) in <a href="http://9.2.0.8">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">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'><o:p></o:p></span></font></span><
/font></p>
</div>
</body>
</html>
|
|
|