Table Prefetching Causes Intermittent Wrong Results in 9iR2,10gR1 and 10gR2
Last updated on JULY 13, 2017
Applies to:Oracle Database - Enterprise Edition - Version 184.108.40.206 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.
Confirmed in 220.127.116.11 , 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. ***
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms