How to correct performance issues with enq: US - contention related to undo segments
(Doc ID 1332738.1)
Last updated on AUGUST 11, 2020
Applies to:Oracle Database Cloud Schema Service - Version N/A and later
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 Backup Service - Version N/A and later
Information in this document applies to any platform.
Assist in correcting performance issues related to "enq: US Contention" on undo segments.
You have many offline undo segments and the workload starts to online many undo segments over a short period of time. This can lead to high 'latch: row cache objects' contention may be seen on DC_ROLLBACK_SEGMENTS together with high 'enq: US - contention' waits when using system managed undo with an auto tuned undo retention period.
Sessions attempting to online undo segments should show ktusmous_online_undoseg() in their call stack.
Another aspect of the problem can be due to long running queries which can raise tuned_undoretention to very high values and exhausts the undo tablespace resulting in ORA-1628.
A real world example: a query is being executed and some rows are fetched from the cursor and then the user stops working on that query (e.g. does not press the "next" button on the application screen) and works on something else (e.g. in a different window). After some time the user continues working on the query ... auto-tune starts tracking the query from this point and the maxquerylen is quite large now, hence also the tuned_undoretention (that depends directly on the maxquerylen).
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