 | | | -none- | -none- 2007-10-02 - By Allen, Brandon
Back
Sounds like the same problem I see almost every week with bind variable
peeking in my Baan & Oracle 9i+ systems - maybe someone ran the
query(ies) with a bind variable value that indicated a large amount of
rows would be returned (e.g. WHERE ssn > :b1; and they set b1 := 0), so
Oracle chose a full table scan as it should, but now that explain is
loaded in the shared pool and used for all subsequent executions,
regardless of what they plug in for their bind variables. If that's the
case, a quick fix is to grant permissions on the table (just grant
select to some harmless user, or grant and then revoke) so all queries
on that table will be invalidated from the shared pool and then the next
time it gets executed it will be hard parsed and bind variables will be
peeked again. In the long term, you have to modify the query so Oracle
can identify between the high and low selectivity versions, or just use
a stored outline. See Metalink #387394.1 for more info.
Regards,
Brandon
__ ____ ____ ____ ____ ____ ____
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Mercadante, Thomas F
(LABOR)
Running on 9.2.0.7
Yesterday, queries against this table used the index. Today, they do
not.
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
<!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=us-ascii">
<META content="MSHTML 6.00.6000.16525" 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 dir=ltr align=left><SPAN class=270132916-02102007><FONT face=Arial
color=#0000ff size=2>Sounds like the same problem I see almost every week with
bind variable peeking in my Baan & Oracle 9i+ systems - maybe someone
ran the query(ies) with a bind variable value that indicated a large amount of
rows would be returned (e.g. WHERE ssn > :b1; and they set b1 :=
0), so Oracle chose a full table scan as it should, but now that explain is
loaded in the shared pool and used for all subsequent executions, regardless of
what they plug in for their bind variables. If that's the case, a quick
fix is to grant permissions on the table (just grant select to some harmless
user, or grant and then revoke) so all queries on that table will be
invalidated
from the shared pool and then the next time it gets executed it will be hard
parsed and bind variables will be peeked again. In the long term, you
have
to modify the query so Oracle can identify between the high and low selectivity
versions, or just use a stored outline. See Metalink #387394.1 for more
info.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=270132916-02102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=270132916-02102007><FONT face=Arial
color=#0000ff size=2>Regards,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=270132916-02102007><FONT face=Arial
color=#0000ff size=2>Brandon</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=270132916-02102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Mercadante, Thomas F
(LABOR)<BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2></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</SPAN></FONT></P>
<P class=MsoNormal>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p></o:p></SPAN></FONT></P><FONT
face=Arial size=2><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p><FONT
color=#0000ff></FONT> </o:p></SPAN></FONT></P>
<P class=MsoNormal><SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Yesterday,
queries against this table used the index. Today, they do not. <SPAN
class=270132916-02102007><FONT
color=#0000ff> </FONT></SPAN></SPAN></P></DIV></BODY><!--[object_id=
#oneneck.com#]--><FONT face=Tahoma size=2><FONT color=#0000ff>
<P>Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.</P><
/FONT></FONT></HTML>
|
|
|