 | | | -none- | -none- 2007-10-02 - By D'Hooge Freek
Back
Do you use a bind variable for the ssn column in the query?
If so, it could be that because of bind variable peeking you are now using a
different access plan.
regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
e-mail: freek.dhooge@(protected) <mailto:freek.dhooge@(protected)>
http://www.uptime.be <http://www.uptime.be/>
disclaimer <http://www.uptime.be/disclaimer.html>
__ ____ ____ ____ ____ ____ ____
Van: oracle-l-bounce@(protected) namens Mercadante, Thomas F (LABOR)
Verzonden: di 2/10/2007 17:12
Aan: oracle-l@(protected)
Onderwerp: Very Strange Query Access Plan
All,
Running on 9.2.0.7 on Aix.
I have a database table with 18 million rows in it. There is an SSN column in
the table. Only 625,000 rows have an SSN populated. I also have an index on
this column. Statistics on both the table and index are gathered nightly
(using dbms_stats with the estimated and default sample options).
Yesterday, queries against this table used the index. Today, they do not.
There was no large data load overnight. We add about 10,000 records per day.
This behavior has happened before where the index gets ignored for a day and
then used the next day.
I really need to use an index here. Queries via ssn are executed all day long
and take 10 minutes to complete. CPU is pegged at 99%.
What am I missing? Maybe histograms? Haven't used them before, so any
suggestions would be helpful.
Thanks
Tom
<HTML dir=ltr><HEAD>
<META http-equiv=Content-Type content="text/html; charset=unicode">
<META content="MSHTML 6.00.2900.3157" name=GENERATOR>
<STYLE>
<!--
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
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:purple;
text-decoration:underline;}
span.EmailStyle17
{
font-family:Arial;
color:windowtext;}
div.Section1
{page:Section1;}
-->
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV id=idOWAReplyText80695 dir=ltr>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2>Do you use a bind variable
for the ssn column in the query?</FONT></DIV>
<DIV dir=ltr><FONT face=Arial size=2>If so, it could be that because of
bind variable peeking you are now using a different access plan.</FONT><
/DIV>
<DIV dir=ltr><FONT face=Arial size=2></FONT> </DIV>
<DIV dir=ltr><FONT face=Arial size=2>regards,</FONT></DIV>
<DIV dir=ltr><FONT face=Arial color=#000000 size=2></FONT> </DIV></DIV>
<DIV id=idSignature13400 dir=ltr>
<DIV><FONT face=Arial size=2>Freek D'Hooge</FONT></DIV>
<DIV><FONT face=Arial size=2>Uptime</FONT></DIV>
<DIV><FONT face=Arial size=2>Oracle Database Administrator</FONT></DIV>
<DIV><FONT face=Arial size=2>e-mail: </FONT><A href="mailto:freek.dhooge@(protected)
.be"><FONT face=Arial size=2>freek.dhooge@(protected)</FONT></A></DIV>
<DIV><A href="http://www.uptime.be/"><FONT face=Arial size=2>http://www.uptime
.be</FONT></A></DIV>
<DIV><SPAN lang=EN-GB style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font
-size: 12.0pt; mso-fareast-font-family: 'Times New Roman'; mso-bidi-font-family:
'Times New Roman'; mso-ansi-language: EN-GB; mso-fareast-language: NL-BE; mso
-bidi-language: AR-SA; mso-no-proof: yes"><A href="http://www.uptime.be
/disclaimer.html"><SPAN style="mso-bidi-font-size: 10.0pt; mso-bidi-font-family:
Arial">disclaimer</SPAN></A></SPAN></DIV>
<DIV> </DIV></DIV>
<DIV dir=ltr><BR>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>Van:</B> oracle-l-bounce@(protected) namens
Mercadante, Thomas F (LABOR)<BR><B>Verzonden:</B> di 2/10/2007 17:12<BR><B>Aan:
</B> oracle-l@(protected)<BR><B>Onderwerp:</B> Very Strange Query Access Plan
<BR></FONT><BR></DIV>
<DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">All,</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">Running on 9.2.0.7 on Aix.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">I have a database table with 18 million rows in it. There
is an SSN column in the table. Only 625,000 rows have an SSN populated.
I also have an index on this column. Statistics on both the table
and index are gathered nightly (using dbms_stats with the estimated and default
sample options).</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">Yesterday, queries against this table used the index.  
;Today, they do not. There was no large data load overnight. We add
about 10,000 records per day. This behavior has happened before where the
index gets ignored for a day and then used the next day.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial">I really need to use an index here. Queries via ssn are
executed all day long and take 10 minutes to complete. CPU is pegged at
99%.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"><BR>What am I missing? Maybe histograms? Haven’
;t used them before, so any suggestions would be helpful.</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"><BR>Thanks</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT
-FAMILY: Arial"><BR>Tom</SPAN></FONT></P></DIV></DIV></BODY></HTML>
|
|
|