In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 9.2.0.8 to 11.2.0.4 [Release 9.2 to 11.2]Information in this document applies to any platform. PURPOSEBelow is the most commonly seen Undo related wait events. This document will help how to diagnose the troubleshoot the same
TROUBLESHOOTING STEPSEnq: 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' 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: * Use the below queries
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.
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) 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: SQL> oradebug dump systemstate 266 SQL> exit Wait for 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
Refer: How to correct performance issues with enq: US - contention related to undo segments (Doc ID 1332738.1)
Buffer Busy Waits on UndoBuffer Busy Waits:This wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is "busy". The two main cases where this can occur are: 2)Run
SELECT p1 "File", p2 "Block", p3 "Reason"
FROM v$session_wait WHERE event='buffer busy waits';
Note: In the above query there is no reference to WAIT_TIME as you are not interested in whether a session is currently waiting or not, just what buffers are causing waits. If a particular block or range of blocks keep showing waits you can try to isolate the object using the queries in Note:
181306.1.
3)Refer: How to Identify The Segment Associated with Buffer Busy Waits (Doc ID 413931.1) Bug 5439554 : BUFFER BUSY WAITS TIMEOUTS ON INSERT INTENSIVE WORKLOAD/POSSIBLE DEADLOCK
Solutions: First, it is very important to check UNDO space healthness and mis-configurations. Check the following document and follow the described checks/steps : Troubleshooting ORA-01555 - snapshot too old: rollback segment number "string" with name "string" too small (Doc ID 1580790.1) Afterwards, apply one/both of the following solutions : 1. Setting _ROLLBACK_SEGMENT_COUNT to a high number to keep undo segments online:
ALTER SYSTEM SET "_rollback_segment_count"=<n> 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.
2. Set the event 10511 which disables SMON from offlining the undo segments which avoids the contention for US enqueue. alter system set events '10511 trace name context forever, level 1';
LATCH: UNDO GLOBAL DATA This latch serializes the access to the Undo (aka Rollback) segment information in the SGA. Known Bugs/Issues: "LATCH: UNDO GLOBAL DATA" In The Top Wait Events (Doc ID 1451536.1) Bug 5751672 - "In memory undo latch" / "undo global data" latch contention from kturimugur (Doc ID 5751672.8) Bug 7299191 - Contention on "undo global data" from concurrent Flashback queries (Doc ID 7299191.8) Solutions: To implement a solution for this problem, please execute any of the below alternative solutions:
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.
REFERENCESNOTE:5751672.8 - Bug 5751672 - "In memory undo latch" / "undo global data" latch contention from kturimugur NOTE:7299191.8 - Bug 7299191 - Contention on "undo global data" from concurrent Flashback queries NOTE:1332738.1 - How to correct performance issues with enq: US - contention related to undo segments NOTE:1451536.1 - "LATCH: UNDO GLOBAL DATA" In The Top Wait Events |
Undo Related Wait Events & Known Issues (文档 ID 1575701.1)
最新推荐文章于 2024-02-01 11:28:00 发布