enq: US - contention related to undo segments

How to correct performance issues with enq: US - contention related to undo segments [ID 1332738.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.
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 case:
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).

NOTE: The Seibel application can allow for this problem to happen.
Last Review Date

June 24, 2011
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details

The wait event “enq: US Contention” is associated with contention on the latch in the row cache (dc_rollback_seg). Enqueue US - Contention can become a bottle-neck for performance if workload dictates that a lot of offlined undo segments must be onlined over a short period of time. The latch on the row cache can be unable to keep up with the workload.

This can happen for a number of reasons and some scenarios are legitimate workload demands.

Solution:

Ensure that peaks in onlined undo segments do not happen (see workaround #2). That is not always feasible.

Workarounds:

1. Bounce the instance.

2. Setting _rollback_segment_count to a high number to keep undo segments online.

alter system set “_rollback_segment_count”=;

3. Set _undo_autotune to false

alter system set “_undo_autotune” = false;

NOTE: Simply using _smu_debug_mode=33554432 may not be enough to stop the problem, but valid fix for bug 5387030.

4. A fix to bug 7291739 is to set a new hidden parameter, _highthreshold_undoretention to set a high threshold for undo retention completely distinct from maxquerylen.

alter system set “_highthreshold_undoretention”=;

If problems persist, please file a Service Request with Oracle Support.

@ Diagnosis
@
@ Should the workarounds and/or configuration changes not help to alleviate the problems,
@ development would need the following diagnostics data:
@
@ a. Provide alert.log which shows the last instance startup parameters through the time of the
@ latest isssues.
@
@ b. AWR and/or ASH report of 30 or 60 minutes interval.
@
@ b. Following query output:
@
@ alter session set nls_date_format=’mm/dd/yy hh24:mi:ss’;
@ select begin_time, MAXQUERYID, MAXQUERYLEN from v$undostat;
@
@ c. While the error is ongoing:
@
@ On single instance:
@
@ sqlplus / as sysdba
@ oradebug setmypid
@ oradebug unlimit
@ oradebug hang易做图yze 3
@ oradebug dump systemstate 266
@
@ wait for 5 seconds
@
@ oradebug dump systemstate 266
@
@ wait for 2 minutes
@
@ sqlplus / as sysdba
@ oradebug setmypid
@ oradebug unlimit
@ oradebug hang易做图yze 3
@ oradebug dump systemstate 266
@
@ wait for 5 seconds
@
@ oradebug dump systemstate 266
@
@ On RAC get tracing on all nodes
@
@ sqlplus / as sysdba
@ oradebug setmypid
@ oradebug unlimit
@ oradebug -g all hang易做图yze 3
@ oradebug -g all dump systemstate 266
@
@ wait for 5 seconds
@
@ oradebug -g all dump systemstate 266
@
@ wait for 2 minutes
@
@ sqlplus / as sysdba
@ oradebug setmypid
@ oradebug unlimit
@ oradebug -g all hang易做图yze 3
@ oradebug -g all dump systemstate 266
@
@ wait for 5 seconds
@
@ oradebug -g all dump systemstate 266
References

BUG:12652689 - HIGH ENQ: US - CONTENTION AFTER UPGRADED TO 11.1.0.7.5
BUG:7279902 - US ENQUEUE CONTENTION OCCURS DUE TO WAITING FOR “GC CURRENT REQUEST”
BUG:7291739 - CONTENTION UNDER AUTO-TUNED UNDO RETENTION
BUG:9557199 - MASSIVE WAITS FOR ‘ENQ: US - CONTENTION’ AND ROW CACHE LOCK IN STRESS TEST
NOTE:5387030.8 - Bug 5387030 - Automatic tuning of undo_retention causes unusual extra space allocation
NOTE:7291739.8 - Bug 7291739 - Contention with auto-tuned undo retention or high TUNED_UNDORETENTION
NOTE:742035.1 - Contention Under Auto-Tuned Undo Retention

补充：综合编程 , 其他综合 ,