enq: US - contention

SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event';
Enter value for event: enq: US - contention
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'enq: US - contention'

NAME                                PARAMETER1                PARAMETER2                PARAMETER3
----------------------------------- ------------------------- ------------------------- -------------------------
enq: US - contention                name|mode                 undo segment #            0

Below is the most commonly seen Undo related wait events. 下面是最常见的与撤消相关的等待事件：
 
    Enq: US Contention
    Buffer Busy waits on Undo
    Wait for a undo record


1.Enq: US Contention
As the number of transactions increases so is their need for space but if there is little space free because most is still allocated to unexpired blocks the sessions first search for free space in offline undo segments. If there are many of Offline undo segments, the search for space can generate lots of hits on dc_rollback_segments, the latch and US(Undo Segment)enqueue.  This can lead to high 'latch: row cache objects' contention which may be seen on DC_ROLLBACK_SEGMENTS together with high 'enq: US - contention'
随着交易数量的增加，他们需要空间，但是如果没有空间可用，因为大多数仍然分配给未到期的块，那么会话首先在离线还原段中搜索空闲空间。 如果有许多脱机撤销段，搜索空间可能在dc_rollback_segments上产生大量命中，锁存器和US（撤消段）排队。 这可能会导致高的'latch：row cache object'争用，在DC_ROLLBACK_SEGMENTS上可能会出现争用，而'enq：US  -  contention'

Performance of the database is affected when this wait event occurs.  Row cache objects latch protects the dictionary cache. The first thing to figure out whether most of the contention was contributed by a particular row cache objects child latch:
发生此等待事件时，数据库的性能会受到影响。 行缓存对象闩锁保护字典缓存。 首先要弄清楚大部分的争用是由一个特定的行缓存对象子锁存器产生的

1.1查询：
1) select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs where status = 'OFFLINE';

2) select latch#, child#, sleeps from v$latch_children where name='row cache objects' and sleeps > 0 order by sleeps desc;
 
    LATCH#     CHILD#     SLEEPS
     -------- ---------- ----------
       120        1       3531645
        10        5       400

3)Query v$rowcache to find the  confirm
    SQL> select parameter, gets from v$rowcache order by gets desc;
 
    PARAMETER                              GETS
    -------------------------------- ----------
    dc_rollback_segments              310995555
    dc_tablespaces                     76251831
    dc_segments                         3912096

Here it shows  dc_rollback_segments with highest gets.     

1.2查询awr:
Check for Top 5 Wait events

High 'latch: row cache objects' contention on dc_rollback_segmentstogether with high 'enq: US - contention'
Top 5 Timed Events                                         Avg %Total         
                    
~~~~~~~~~~~~~~~~~~                                        wait   Call         
                    
Event                                 Waits    Time (s)   (ms)   Time Wait    
 
------------------------------ ------------ ----------- ------ ------
----------                  
latch: row cache objects          2,057,004     490,074    238   43.8
Concurrency                  
enq: US - contention              1,548,328     370,460    239   33.1      
Other  

1.3 其他信息收集手段：
1) When the issue occurs, collect hang analyze dumps and system state dumps.

$ sqlplus / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
SQL> oradebug dump systemstate 266

Wait for 5 seconds, and then continue with:等待5秒钟，然后继续

SQL> oradebug dump systemstate 266
SQL> exit

Wait for 2 minutes, and then again:等待2分钟，然后再次

$ sqlplus / as sysdba
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
SQL> oradebug dump systemstate 266

2) AWR and/or ASH report of 30 or 60 minutes interval.

3) Alert.log from last startup  


2. Buffer Busy Waits on Undo  
Buffer Busy Waits on Undo happens when we want to NEW the block but the block is currently being read by another session (most likely for undo).
缓冲区忙于等待撤销时，我们想要新的块，但该块正在被另一个会话正在读取（最有可能撤消）。   

1)Review the section Segments by Buffer Busy Waits, and note the segments with the highest waits
查看按缓冲区繁忙等待分段，并注意等待最高的段

2)查询：
SELECT p1 "File", p2 "Block", p3 "Reason"
    FROM v$session_wait
   WHERE event='buffer busy waits';
   
3）SELECT owner, segment_name, file_id, block_id starting_block_id, block_id + blocks ending_block_id, blocks
FROM dba_extents
WHERE file_id = &file_num AND ( block_id <= &block_id AND (&block_id < (block_id + blocks)) )
 
 OWNER      SEGMENT_NAME            FILE_ID STARTING_BLOCK_ID ENDING_BLOCK_ID     BLOCKS
---------- -------------------- ---------- ----------------- --------------- ----------
SCOTT      STOCK_PRICES                  4             78385           78393          8

如果块类型是'Undo Header'，那么解决方法是添加更多的回滚段

方案：Solutions
首先，检查UNDO的空间健康状况和错误配置是非常重要的。
检查下面的文件，并按照所述的检查/步骤：
ORA-01555疑难解答 - 快照太旧：名称"字符串"的回退段号"字符串"太小（文档ID 1580790.1）

Afterwards, apply one/both of the following solutions :之后，应用以下一种或两种解决方案

a. Setting _ROLLBACK_SEGMENT_COUNT to a high number to keep undo segments online:
ALTER SYSTEM SET "_rollback_segment_count"= scope=both;

Note: In databases with high query activity, particularly parallel query and a high setting for _ROLLBACK_SEGMENT_COUNT, you can expect to see wait contention on the row cache for DC_ROLLBACK_SEGS. It is highly recommended in these environments where setting _ROLLBACK_SEGMENT_COUNT to a high value (10s of thousands and higher) apply the patch for Bug:14226599 base Bug:1421197.  This will increase the hash buckets on the DC_ROLLBACK_SEGS row cache to help alleviate latch contention.   
注意：在具有高查询活动（尤其是并行查询）和_ROLLBACK_SEGMENT_COUNT的高设置的数据库中，您可能会看到DC_ROLLBACK_SEGS在行高速缓存上的等待争用。 强烈建议在这些环境中将_ROLLBACK_SEGMENT_COUNT设置为较高的值（数千和更高的值），并将Bug应用于Bug：14226599 base Bug：1421197。 这将增加DC_ROLLBACK_SEGS行缓存上的散列桶，以帮助缓解锁存争用。

b.Set the event 10511 which disables SMON from offlining the undo segments which avoids the contention for US enqueue.
设置禁用SMON的事件10511使撤消段离线，避免US争用。

Setting of this event does not effect the regular shrink/space reclaims as it only disables SMON from offlining to avoid excessive onlines of undo segments.
此事件的设置不会影响正常的收缩/空间回收，因为它只会禁用SMON从脱机状态，以避免过多联机撤消段。

语法：
alter system set events '10511 trace name context forever, level 1';  


3.wait for a undo record等待撤消记录
You can disable parallel rollback by setting the following parameter
您可以通过设置以下参数禁用并行回滚

参数：
fast_start_parallel_rollback = false

BEWARE: that setting this parameter dynamically can cause problems on a busy instance with a lot of active transaction work and it is safer to set this with an instance restart so as not to change the rollback strategy on active transactions.
注意：动态设置这个参数可能会导致一个繁忙的实例出现问题，并且有很多活动的事务工作，为了不改变活动事务的回滚策略，设置这个实例重启会更安全。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2149839/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2149839/