SAP Basis Tech Supports

Symptom
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
This note is released as a Hot News.
You must check the note regularly for updates. Otherwise, you will not be aware of important changes regarding prerequisites, consequences and solutions in the latest release. If this note is updated, no new Hot News is issued.
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
It is possible to get intermittent wrong results from a query which has the same execution plan for each execution due to a problem with the “table prefetch” feature.
Intermittent Wrong Results are possible for queries :
- Showing a prefetch execution plan
- projection pushdown is enabled (default).
Other terms
Oracle 10.2.0.4, wrong results, prefecting, projection pushdown
Reason and Prerequisites
The “table prefetch” has a dynamic component that can kick in or out depending on the load on the buffer cache making it unpredictable so selects and DMLs can produce different results depending on the load on the server.
Sometimes returns more rows, sometimes return less even in the same session on repeated executions.Sometimes reproduces when using 2 different schema users and/or sessions accessing the same tables.Sometimes reproduces only immediately after the instance has been recycled or the buffer cache flushed and goes away after “warm up” and sometimes is completely the opposite scenario.
This is the Oracle bug 8366255 which is fixed in 10.2.0.5
Solution
The patch for bug 8363255 is included in the latest
Optimizer-Merge-Fix (#13) 8526100 as described in the SAP Note 1165319.
If it is not possible to apply the Optimizer-Merge-Fix #13 then add the following event in the init<SID>.ora / spfile as a workaround:
event=’10753 trace name context forever, level 2′
After you’ve applied the Optimizer-Merge-Fix #13 please remove the event 10753.