 | | | Subject: RE: WRONG RESULTS bug in 9.2.x, 10.1.x and 10.2.x | Subject: RE: WRONG RESULTS bug in 9.2.x, 10.1.x and 10.2.x 2007-11-01 - By Allen, Brandon
Back
Hi list, just an update on this issue:
After more research, it seems that encountering this problem might not
be as likely as I'd originally thought. Although table prefetching is
enabled by default, it seems that it's actually used rather rarely. I
wrote the query below, which you can use to check all the explain plans
in your shared pool to see if they might be using prefetching. It
identifies the plans by using the LAG function to find any NESTED LOOPS
operations that are immediately preceeded by a TABLE ACCESS BY INDEX
ROWID operation. In my BaanIVc environment on 10.2.0.2 with a shared
pool of about 1GB, the query runs in about 1 second and only returns
about 20 plans (out of ~1500 in the pool), half of which are from SYS,
SYSTEM or SYSMAN - the remaining 10 being from my applications. My
understanding of table prefetching is still far from complete since
documentation doesn't seem to exist on it, however I did find some info
in Jonathan Lewis' Cost-Based Oracle Fundamentals (pp.308-311), and
based on this along with my testing and what I've read on Metalink and
in my SR, it seems to be that even if the plan does show this format,
the table prefetching may not actually be used at runtime, and even if
prefetching is used, the wrong results problem won't always occur. I
have checked a few of the other prefetching queries from my system and
so far have not found any others that are returning incorrect results so
that's reassuring, but the fact that this problem could still occur in
any 9i or 10g system and if it does, could be disastrous and very
difficult to track down, still leads me to recommend proactively
patching or setting the hidden parameter to disable table lookup
prefetching on all 9i and 10g systems. Of course you should open an SR
with Oracle Support to get their recommendation for your specific
situation prior to taking any such action.
Regards,
Brandon
Disclaimer: The following script is provided as-is with no warranty
expressed or implied. Use it at your own risk.
col operation format a20;
col prev_oper format a20;
col prev_opt format a20;
col prev_obj format a30;
select * from
(select
plan_hash_value,
id,
operation,
lag(operation) over (order by plan_hash_value, id) as prev_oper,
lag(options) over (order by plan_hash_value, id) as prev_opt,
lag(object_name) over (order by plan_hash_value, id) as prev_obj
from
v$sql_plan)
where
prev_oper = 'TABLE ACCESS'
and prev_opt = 'BY INDEX ROWID'
and operation = 'NESTED LOOPS';
__ ____ ____ ____ ____ ____ ____
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Allen, Brandon
There is a bug in many (if not all) versions of 9.2, 10.1 and 10.2, on
all platforms, that causes queries to intermittently return the wrong
results when table prefetching is used
. . .
Check out Metalink# 406966.1 for more info.
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><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16544" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2>Hi list, just an update on this issue:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2>After more research, it seems that encountering this
problem might not be as likely as I'd originally thought. Although table
prefetching is enabled by default, it seems that it's actually used rather
rarely. I wrote the query below, which you can use to check all the
explain plans in your shared pool to see if they <EM>might</EM> be using
prefetching. It identifies the plans by using the LAG function to find
any
NESTED LOOPS operations that are immediately preceeded by a TABLE ACCESS BY
INDEX ROWID operation. In my BaanIVc environment on 10.2.0.2 with a
shared
pool of about 1GB, the query runs in about 1 second and only
returns about 20 plans (out of ~1500 in the pool), half of which are
from SYS, SYSTEM or SYSMAN - the remaining 10 being from my applications.
My understanding of table prefetching is still far from complete since
documentation doesn't seem to exist on it, however I did find some info in
Jonathan Lewis' <EM>Cost-Based Oracle Fundamentals</EM> (pp.308-311), and
based on this along with my testing and what I've read on Metalink and in my SR
,
it seems to be that even if the plan does show this format, the table
prefetching may not actually be used at runtime, and even if prefetching is
used, the wrong results problem won't always occur. I have
checked a few of the other prefetching queries from my system and so far have
not found any others that are returning incorrect results so that's reassuring,
but the fact that this problem could still occur in any 9i or 10g system and if
it does, could be disastrous and very difficult to track down, still leads me
to
recommend proactively patching or setting the hidden parameter to disable table
lookup prefetching on all 9i and 10g systems. Of course you should open
an
SR with Oracle Support to get their recommendation for your specific situation
prior to taking any such action.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2>Regards,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2>Brandon</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2>Disclaimer: The following script is provided as-is with no
warranty expressed or implied. Use it at your own
risk.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face="Courier New"
size=2>col operation format a20;<BR>col prev_oper format a20;<BR>col prev_opt
format a20;<BR>col prev_obj format a30;</FONT></SPAN></DIV>
<DIV><FONT face="Courier New"></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=462023021-31102007><FONT face="Courier New"
size=2>select * from <BR> (select <BR> plan_hash_value, <BR> id,
<BR> operation, <BR> lag(operation) over (order by plan_hash_value,
id) as prev_oper, <BR> lag(options) over (order by plan_hash_value, id) as
prev_opt, <BR> lag(object_name) over (order by plan_hash_value, id) as
prev_obj <BR> from <BR> v$sql_plan) <BR>where <BR> prev_oper =
'TABLE ACCESS' <BR> and prev_opt = 'BY INDEX ROWID' <BR> and
operation
= 'NESTED LOOPS';</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>Allen,
Brandon</FONT></DIV>
<DIV><SPAN class=657465216-25102007><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=657465216-25102007><FONT face=Arial size=2>There is a bug in
many (if not all) versions of 9.2, 10.1 and 10.2, on all platforms, that
causes queries to intermittently return the wrong results when table
prefetching
is used</FONT></SPAN></DIV>
<DIV><SPAN class=657465216-25102007><FONT face=Arial size=2><SPAN
class=462023021-31102007></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=657465216-25102007><FONT face=Arial size=2><SPAN
class=462023021-31102007>. . .</SPAN></FONT></SPAN></DIV>
<DIV><SPAN class=657465216-25102007><FONT face=Arial size=2><SPAN
class=462023021-31102007></SPAN></FONT></SPAN> </DIV>
<DIV><SPAN class=657465216-25102007><FONT face=Arial size=2>Check out Metalink#
406966.1 for more info. </FONT></SPAN></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>
|
|
|