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.
注意：動態設定這個引數可能會導致一個繁忙的例項出現問題，並且有很多活動的事務工作，為了不改變活動事務的回滾策略，設定這個例項重啟會更安全。

enq: US - contention

相關文章