 | | | -none- | -none- 2007-10-02 - By -not available-
Back
All,
Thanks to Alvaro Jose Fernandez & Ric Van Dyke, this is solved. The
DENSITY and CLUSTER FACTOR values in the user_tab_columns for my
database table had bad values. These values are calculated by the
DBMS_STATS package. I manually set these to a much lower figure and my
problem went away.
I'm still trying to determine what my next steps are. One definite step
is to stop gathering stats for awhile!
Thanks
Tom
__ ____ ____ ____ ____ ____ ____
From: Koppelaars, Toon [mailto:T.Koppelaars@(protected)]
Sent: Tuesday, October 02, 2007 1:59 PM
To: Mercadante, Thomas F (LABOR); oracle-l@(protected)
Subject: RE: Very Strange Query Access Plan
Can you provide us with a (couple of) example(s) of these queries?
-- --Oorspronkelijk bericht-- --
Van: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]Namens Mercadante, Thomas F
(LABOR)
Verzonden: dinsdag 2 oktober 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 xmlns:v="urn:schemas-microsoft-com:vml" 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)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@(protected)
{font-family:Tahoma;
panose-1 (See http://ose-1.ora-code.com):2 11 6 4 3 5 4 4 2 4;}
/* 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;
font-family:Arial;
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@(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 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>All,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span 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 style='font-size:
10.0pt;font-family:Arial;color:navy'>Thanks to Alvaro Jose Fernandez & Ric
Van Dyke, this is solved. The DENSITY and CLUSTER FACTOR values in the
user_tab_columns for my database table had bad values. These values are
calculated
by the DBMS_STATS package. I manually set these to a much lower figure
and my
problem went away.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span 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 style='font-size:
10.0pt;font-family:Arial;color:navy'>I’m still trying to determine what
my next steps are. One definite step is to stop gathering stats for
awhile!<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span 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 style='font-size:
10.0pt;font-family:Arial;color:navy'>Thanks<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span 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 style='font-size:
10.0pt;font-family:Arial;color:navy'>Tom<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'> Koppelaars, Toon
[mailto:T.Koppelaars@(protected)] <br>
<b><span style='font-weight:bold'>Sent:</span></b> Tuesday, October 02, 2007
1:59 PM<br>
<b><span style='font-weight:bold'>To:</span></b> Mercadante, Thomas F (LABOR);
oracle-l@(protected)<br>
<b><span style='font-weight:bold'>Subject:</span></b> RE: Very Strange Query
Access Plan</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<div>
<p class=MsoNormal><font size=2 color=blue face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:blue'>Can you provide us with a (couple of)
example(s) of these queries?</span></font><o:p></o:p></p>
</div>
<blockquote style='margin-top:5.0pt;margin-right:0in;margin-bottom:5.0pt'>
<p class=MsoNormal style='margin-bottom:12.0pt'><font size=2 face=Tahoma><span
style='font-size:10.0pt;font-family:Tahoma'>-- --Oorspronkelijk bericht-- --<br>
<b><span style='font-weight:bold'>Van:</span></b> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]<b><span style='font-weight:bold'>Namens
</span></b>Mercadante,
Thomas F (LABOR)<br>
<b><span style='font-weight:bold'>Verzonden:</span></b> dinsdag 2 oktober 2007
17:12<br>
<b><span style='font-weight:bold'>Aan:</span></b> oracle-l@(protected)<br>
<b><span style='font-weight:bold'>Onderwerp:</span></b> Very Strange Query
Access Plan</span></font><o:p></o:p></p>
<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>
</blockquote>
</div>
</body>
</html>
|
|
|