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