 | | | -none- | -none- 2007-10-02 - By Alvaro Jose Fernandez
Back
Hi Tom,
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.
Maybe you would not need to use histograms.
If you don't mind sending a 10053 trace excerpt for the query (maybe
using a simple test query using ssn as the only predicate), showing the
table & index statistics , and the cost assigned to each index, it would
help to see what's going on. It appears a not very appealing index for
the optimizer (625,000 out of 18 million and many nulled rows).
In the extreme case you can also workaround this situation making this
index's statistics more attractive to the optimizer, using
dbms_stats.set_index_stats . But it's worthy take a look before at an
10053 trace , though.
alvaro
<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:purple;
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;
font-family:Arial;
color:windowtext;}
span.EstiloCorreo18
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@(protected) Section1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=ES link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Hi Tom,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;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.<o:p><
/o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;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%.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span lang=EN-US style='font-size:
10.0pt;font-family:Arial'><br>
What am I missing? Maybe histograms? Haven’t used them
before, so any suggestions would be helpful.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>Maybe you would not need
to use histograms. <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>If you don’t mind
sending a 10053 trace excerpt for the query (maybe using a simple test query
using
ssn as the only predicate), showing the table & index statistics , and the
cost
assigned to each index, it would help to see what’s going on. It appears a
not very appealing index for the optimizer (625,000 out of 18 million and many
nulled rows). <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>In the extreme case you
can also workaround this situation making this index’s statistics more
attractive to the optimizer, using dbms_stats.set_index_stats . But it’s
worthy take a look before at an 10053 trace , though.<o:p></o:p></span></font><
/p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span><
/font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span lang=EN-US
style='font-size:10.0pt;font-family:Arial;color:navy'>alvaro<o:p></o:p></span><
/font></p>
</div>
</body>
</html>
|
|
|