How to correct performance issues with enq: US - contention related to undo segments (Doc ID 1332738.1)

Purpose

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).

Troubleshooting Steps

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