Query Intermittently Slow Without Execution Plan Change

(Doc ID 2347810.1)

Last updated on FEBRUARY 24, 2018

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


1 select statement, could be simple or complex, with full table scan in execution plan.

2 full table scan a table that is relatively large, the query can take several minutes or even more to finish under normal situation.

3 sometimes the query seems to run forever without finishing, with same execution plan.

4 it's in a OLTP system, while the select is run, the application also make frequent update on the same table the select is doing full table scan.

5 turn on 10046 trace file may discover frequent physical reads from undo tablespace.

6 errorstack trace may also show function in undo management, as shown in following example (at least ktuGetCommitTimes is always seen):

7 automatic undo management (AUM) is used


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms