RE: WRONG RESULTS bug in 9.2.x, 10.1.x and 10.2.x
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 1 Nov 2007 11:23:41 -0700
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
Other related posts:
