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. 下面是最常見的與撤消相關(guān)的等待事件:
 
    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'
隨著交易數(shù)量的增加,他們需要空間,但是如果沒有空間可用,因?yàn)榇蠖鄶?shù)仍然分配給未到期的塊,那么會話首先在離線還原段中搜索空閑空間。 如果有許多脫機(jī)撤銷段,搜索空間可能在dc_rollback_segments上產(chǎn)生大量命中,鎖存器和US(撤消段)排隊(duì)。 這可能會導(dǎo)致高的'latch:row cache object'爭用,在DC_ROLLBACK_SEGMENTS上可能會出現(xiàn)爭用,而'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:
發(fā)生此等待事件時(shí),數(shù)據(jù)庫的性能會受到影響。 行緩存對象閂鎖保護(hù)字典緩存。 首先要弄清楚大部分的爭用是由一個(gè)特定的行緩存對象子鎖存器產(chǎn)生的

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秒鐘,然后繼續(xù)

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).
緩沖區(qū)忙于等待撤銷時(shí),我們想要新的塊,但該塊正在被另一個(gè)會話正在讀?。ㄗ钣锌赡艹废?nbsp;  

1)Review the section Segments by Buffer Busy Waits, and note the segments with the highest waits
查看按緩沖區(qū)繁忙等待分段,并注意等待最高的段

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的空間健康狀況和錯(cuò)誤配置是非常重要的。
檢查下面的文件,并按照所述的檢查/步驟:
ORA-01555疑難解答 - 快照太舊:名稱"字符串"的回退段號"字符串"太?。ㄎ臋nID 1580790.1)

Afterwards, apply one/both of the following solutions :之后,應(yīng)用以下一種或兩種解決方案

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.   
注意:在具有高查詢活動(dòng)(尤其是并行查詢)和_ROLLBACK_SEGMENT_COUNT的高設(shè)置的數(shù)據(jù)庫中,您可能會看到DC_ROLLBACK_SEGS在行高速緩存上的等待爭用。 強(qiáng)烈建議在這些環(huán)境中將_ROLLBACK_SEGMENT_COUNT設(shè)置為較高的值(數(shù)千和更高的值),并將Bug應(yīng)用于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.
設(shè)置禁用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.
此事件的設(shè)置不會影響正常的收縮/空間回收,因?yàn)樗粫肧MON從脫機(jī)狀態(tài),以避免過多聯(lián)機(jī)撤消段。

語法:
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
您可以通過設(shè)置以下參數(shù)禁用并行回滾

參數(shù):
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.
注意:動(dòng)態(tài)設(shè)置這個(gè)參數(shù)可能會導(dǎo)致一個(gè)繁忙的實(shí)例出現(xiàn)問題,并且有很多活動(dòng)的事務(wù)工作,為了不改變活動(dòng)事務(wù)的回滾策略,設(shè)置這個(gè)實(shí)例重啟會更安全。

網(wǎng)站欄目:enq:US-contention
轉(zhuǎn)載源于:http://bm7419.com/article48/jcephp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供移動(dòng)網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站建站公司、外貿(mào)建站搜索引擎優(yōu)化、營銷型網(wǎng)站建設(shè)

廣告

聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)

綿陽服務(wù)器托管