 | | | Subject: Re: Very Strange Query Access Plan | Subject: Re: Very Strange Query Access Plan 2007-10-03 - By Terry Sutton
Back
Tom,
You might also want to check the num_distinct column of user_tab_columns to see
if that's a realistic number. I've had situations where it was really low
(like 400 when there were millions of distinct values) which resulted from too
small an estimate using analyze or dbms_stats.
--Terry
-- -- Original Message -- --
From: Mercadante, Thomas F (LABOR)
To: oracle-l@(protected)
Sent: Tuesday, October 02, 2007 11:29 AM
Subject: RE: Very Strange Query Access Plan
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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859 (See http://iso-8859.ora-code.com)-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR><!--[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 vLink=purple link=blue bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Tom,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>You might also want to check the num_distinct
column of user_tab_columns to see if that's a realistic number. I've had
situations where it was really low (like 400 when there were millions of
distinct values) which resulted from too small an estimate using analyze or
dbms_stats.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>--Terry</FONT></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT:
#000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">-- -- Original Message -- -- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=Thomas.Mercadante@(protected)
href="mailto:Thomas.Mercadante@(protected)">Mercadante, Thomas F
(LABOR)</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=oracle-l@(protected)
href="mailto:oracle-l@(protected)">oracle-l@(protected)</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, October 02, 2007 11:29
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: Very Strange Query Access
Plan</DIV>
<DIV><BR></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">All,<o:p></o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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 face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">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 face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Thanks<o:p></o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">Tom<o:p></o:p></SPAN
></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p> </o:p><
/SPAN></FONT></P>
<DIV>
<DIV class=MsoNormal style="TEXT-ALIGN: center" align=center><FONT
face="Times New Roman" size=3><SPAN style="FONT-SIZE: 12pt">
<HR tabIndex=-1 align=center width="100%" SIZE=2>
</SPAN></FONT></DIV>
<P class=MsoNormal><B><FONT face=Tahoma size=2><SPAN
style="FONT-WEIGHT: bold; FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">From:</SPAN><
/FONT></B><FONT
face=Tahoma size=2><SPAN style="FONT-SIZE: 10pt; 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 face="Times New Roman" size=3><SPAN
style="FONT-SIZE: 12pt"><o:p> </o:p></SPAN></FONT></P>
<DIV>
<P class=MsoNormal><FONT face=Arial color=blue size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: blue; FONT-FAMILY: Arial">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: 5pt; MARGIN-BOTTOM: 5pt; MARGIN-RIGHT: 0in">
<P class=MsoNormal style="MARGIN-BOTTOM: 12pt"><FONT face=Tahoma
size=2><SPAN
style="FONT-SIZE: 10pt; 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 face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">All,<o:p></o:p></SPAN></FONT><
/P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></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.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></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).<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></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.<o:p></o:p></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%.<o:p></o:p></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.<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><BR>Thanks<o:p></o:p></SPAN><
/FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><BR>Tom<o:p></o:p></SPAN></FONT
></P></BLOCKQUOTE></DIV></BLOCKQUOTE></BODY></HTML>
|
|
|