RE: WRONG RESULTS bug in 9.2.x, 10.1.x and 10.2.x

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Thu, 1 Nov 2007 11:23:41 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C4508B6EB6C@NT15.oneneck.corp>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 01 2007 - 13:23:41 CDT