Table Prefetching Causes Intermittent Wrong Results in 9iR2,10gR1 and 10gR2
(Doc ID 406966.1)
Last updated on FEBRUARY 03, 2019
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.
Confirmed in 18.104.22.168 , 10.2.0.1, 10.2.0.2, 10.2.0.3 and 10.2.0.4
Applies to 9.2.X, 10.1.X, 10.2.0.1, 10.2.0.2, 10.2.0.3, 10.2.0.4
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. This includes selects used in INSERTS, CTAS and MVIEWs. 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 (enabled by default from 9i onwards). The following symptoms can be seen:
- 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.
*** There is no positive way to know if prefetch is being used or not or how much is used. ***
*** All bitmap plans are prefetch plans and the prefetch cannot be disabled on them. ***
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document