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. 注意：动态设置这个参数可能会导致一个繁忙的实例出现问题，并且有很多活动的事务工作，为了不改变活动事务的回滚策略，设置这个实例重启会更安全。
