 | | | -none- | -none- 2007-10-02 - By Koppelaars, Toon
Back
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: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.3790.2440" name=GENERATOR>
<STYLE>@(protected) Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><SPAN class=493065817-02102007><FONT face=Arial color=#0000ff size=2>Can
you provide us with a (couple of) example(s) of these
queries?</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-- --Oorspronkelijk bericht-- --<BR><B>Van:</B>
oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)]<B>Namens
</B>Mercadante, Thomas F (LABOR)<BR><B>Verzonden:</B> dinsdag 2 oktober 2007
17:12<BR><B>Aan:</B> oracle-l@(protected)<BR><B>Onderwerp:</B> Very Strange
Query Access Plan<BR><BR></FONT></DIV>
<DIV class=Section1>
<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></DIV></BLOCKQUOTE></BODY></HTML>
|
|
|