Query Intermittently Slow Without Execution Plan Change
Last updated on FEBRUARY 24, 2018
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 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