 | | | -none- | -none- 2007-10-02 - By -not available-
Back
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 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: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
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@(protected) Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>All,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Running on 9.2.0.7 on Aix.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
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).<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span 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 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 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 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><br>
Thanks<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><br>
Tom<o:p></o:p></span></font></p>
</div>
</body>
</html>
|
|
|