sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA > Oracle等待事件 >

【学习笔记】Oracle等待事件之与UNDO相关的等待事件与解决方案

时间:2016-10-23 19:37   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库等待事件之与UNDO相关的等待事件和相应的解决方案,帮助更好的理解学习等待事件完成性能优化。
Undo Related Wait Events & Known Issues (Doc ID 1575701.1)

clip_image001

Modified:29-May-2014
Type:TROUBLESHOOTING

clip_image001
In this Document

Purpose
Troubleshooting Steps
Enq: US Contention:
Buffer Busy Waits on Undo
References

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.

PURPOSE

Below is the most commonly seen Undo related wait events. This document will help how to diagnose the troubleshoot the same
Enq: US Contention
Buffer Busy waits on Undo

TROUBLESHOOTING STEPS
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’

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:

A) Use the following queries or
B) Check AWR Report
A) 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.



B) From AWR Report

a) 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




Other Information to collect:

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




Known Bugs

1) BUG:7291739 – CONTENTION UNDER AUTO-TUNED UNDO RETENTION
Fixed in
10.2.0.4.4, 10.2.0.5.0, 11.2.0.1.0

When using autotuned undo retention the high latch contention on ‘latch: row cache objects’ on dc_rollback_segments
together with high enqueue contention on ‘enq: US – contention’ can occur.

Refer: Contention Under Auto-Tuned Undo Retention (Doc ID 742035.1) for other workarounds

2) Unpublished Bug 14226599 – Increase dc_rollback_segs hash buckets to reduce ‘latch: row cache objects’ waits
This is NOT a bug, but enhancement which increases the number of hash buckets in the "dc_rollback_segments" rowcache.
Versions confirmed being affected is 10.2.0.5,11.1.0.7,11.2.0.2 and 11.2.0.3

3) Bug 13252635 : ESSC: HIGH ENQ: US – CONTENTION, LATCH: GES RESOURCE HASH LIST ON STRESS TEST
closed as duplicate of unpublished Bug 11690639


4) Unpublished Bug 6870994 : CREATE / ONLINE OF UNDO SEGMENTS SLOWER IN 11.1.0.6 THAN IN 10.2.0.3. If the number of enqueue gets is high while onlining of undo segments then we may have hit this problem.

5) Bug 5387030 : AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS
Refer: How to correct performance issues with enq: US – contention related to undo segments (Doc ID 1332738.1)

Buffer Busy Waits on Undo

Buffer 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:

a) Another session is reading the block into the buffer
b) Another session holds the buffer in an incompatible mode to our request

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)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) Oracle о

If the block type is ‘Undo Header’ then solution would be to add more rollback segments

Known bugs:
Bug 5439554 : BUFFER BUSY WAITS TIMEOUTS ON INSERT INTENSIVE WORKLOAD/POSSIBLE DEADLOCK

Happens when buffer busy wait" timeouts may be seen when running with automatic undo management and in memory undo.


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"= 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:14211971. 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.

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.
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.
Every time a session wants to know about the state of the Undo Segments, it has to get this latch.
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:
Increase the undo tablespace size to make more free space available in the undo tablespace
OR

Reduce the UNDO_RETENTION value, so less undo is being retained
OR

Enable undo auto-tuning, by unsetting the _UNDO_AUTOTUNE instance parameter

REFERENCES
NOTE:1451536.1 – "LATCH: UNDO GLOBAL DATA" In The Top Wait Events
NOTE: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
本文固定链接: http://www.htz.pw/2014/06/30/undo%e7%9b%b8%e5%85%b3%e7%9a%84%e7%ad%89%e5%be%85%e4%b8%8e%e8%a7%a3%e5%86%b3%e6%96%b9%e6%a1%88.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle等待事件之与UNDO相关的等待事件与解决方案

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1048.html

Oracle研究中心

关键词:

Doc ID 1575701.1

详解UNDO相关的等待事件与解决方案

Oracle数据库中与undo有关系的等待事件