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.

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