Shulou(Shulou.com)06/01 Report--

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. The following are the most common wait events related to undo:

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'

As the number of transactions increases, they need space, but if there is no space available, because most of them are still allocated to unexpired blocks, the session first searches for free space in the offline restore segment. If there are many offline undo segments, the search space may generate a large number of hits on the dc_rollback_segments, with latches and US (undo segments) queued. This may lead to high 'latch:row cache object' contention, which may occur on DC_ROLLBACK_SEGMENTS, and' 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:

When this wait event occurs, the performance of the database is affected. The row cache object latch protects the dictionary cache. First of all, it is important to understand that most contention is caused by a specific row cache object sublatch

1.1 Enquiry:

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 query 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 2057004 490074 238 43.8

Concurrency

Enq: US-contention 1548328 370460 239 33.1

Other

1.3 other means of information collection:

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: wait 5 seconds before continuing

SQL > oradebug dump systemstate 266

SQL > exit

Wait for 2 minutes, and then again: wait 2 minutes, and then again

$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).

While the buffer is busy waiting for undo, we want a new block, but the block is being read by another session (most likely undone).

1) Review the section Segments by Buffer Busy Waits, and note the segments with the highest waits

View busy waiting segments by buffer and pay attention to waiting for the highest segment

2) query:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.