My Oracle Support Banner

Query Intermittently Slow Without Execution Plan Change, Scaning UNDO Endlessly (Doc ID 2347810.1)

Last updated on JANUARY 29, 2024

Applies to:

Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

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

Changes

 

Cause

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
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.