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 laterOracle 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 |