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

List,
 
I just spent the last two weeks digging through trace files before I
finally narrowed down the problem enough that I could open an SR with
Oracle and find out I've encountered a known bug so I figured I'd post a
warning to hopefully save some of you from the same trouble.
 
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, which is enabled by default.
The likelihood of encountering this is high since the feature at fault
(table prefetching) is enabled by default and it doesn't require any
exotic query to expose it - just a simple join of a few tables and the
right mix of stats, bind variables, etc. for the CBO to come up with an
explain plan that uses table prefetching.  I suggest implementing either
the patch or the workaround even if you haven't encountered the problem
yet - otherwise, you never know when it might occur since it's very
intermittent.
 
There are patches available and a workaround of setting the hidden
parameter _table_lookup_prefetch_size=0.
 
Check out Metalink# 406966.1 for more info.  
 
Regards,
Brandon 

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: