Oracle local write wait 和 enq：RO - fast object reuse 等待事件 说明

在AWR 看到local write waits和 enq: RO - fast object reuse 的 等待事件。

一.Local write waits 等待说明

网上对local write waits 的说明：

Note 1：

Typically DBWRhas to free up some buffers when you want to read something from the disk.During this process there are chances that you will be waiting for your localbuffer (i.e blocks dirtied/invalidated by your session) to be written to disk.During this timethewaits are shown as local write waits.

Note 2：

Basically'localwrite' wait happens (as the name indicates) when the session is waiting for itslocal (means writes pending because of its own operation)writeoperation. This could happen typically if the underlying disc has some seriousproblems (one of the member disk crash in RAID-05 - for example, or acontroller failure). That is why I might have said ' you never see this wait inthe normal databases!'.You may see thisduring (rarely) Truncating a large table while most of the buffers of thattable in cache. During TRUNCATEs the session has to a local checkpoint andduring this process, the session may wait for 'local write' wait.

基本上'local write' wait 表示会话在等待自己的写操作。在磁盘发生严重问题时会发生(例如RAID 5的一个磁盘崩溃,或者磁盘控制器错误),这在正常的系统中极少发生，在TRUNCATE 一个大表而这个表在缓存中的时候，会话必需进行一个localcheckpoint,这个时候会话会等待localsession wait.

在MOS 的文档：

Truncates Taking Too Long... [ID 334822.1]

提到了这个等待事件。

Cause：

Processes thatinvolve temporary tables being truncated and repopulated in multiple,concurrent batch streams may present this situation.

The underlyingproblem is we have to write the object's dirty buffers to disk prior toactually truncating or dropping the object. This ensures instancerecoverability and avoids a stuck recovery. It seems at first glance perfectlyreasonable to simply truncate a temporary table, then repopulate for anotherusage. And then to do the temporary poplulate/truncate operationsin concurrent batches to increase throughput.

However, inreality the concurrent truncates get bogged down as dbwr gets busy flushing thosedirty block buffers from the buffer cache. You will see huge CI enqueue waits.The multiple truncate operations in concurrent streams absolutely killthroughput.This is specially critical with large buffers.

There was also adisscussion in Bug: 4147840 (non-publish) where a peoplesoft process wascausing this behavior because of the above explanation and they seemed to fixit by changing some peoplesoft code to implement delete rather than truncate onsamll temporary tables.

Solution：

In 9.2.0.5 andhigher, it may also help to make sure a "temp" tablethat isfrequently truncated have storage defined so that it occupiesone extent.But this workaround is only available as long as the extentis no morethan 50% the size of the buffer cache. In non-RAC environmentsthe tablestill has to be smaller than 50% of the buffer cache, but itallows thetable to have up to 5 extents before falling back to the oldalgorithm.

二．enq: RO - fast object reuse 等待事件

该等待事件多与bug 相关

2.1 Bug 1：Bug 7385253

Bug 7385253 - Slow Truncate / DBWR useshigh CPU / CKPT blocks on RO enqueue [ID 7385253.8]

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versionsbelievedto be affected	Versions >= 10 but BELOW 11.2
Versionsconfirmedas being affected	11.1.0.7 10.2.0.4 10.2.0.3 10.2.0.2
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

11.2.0.1 (Base Release) 11.1.0.7.3 (Patch Set Update) 10.2.0.5 (Server Patch Set) 10.2.0.4.1 (Patch Set Update) 11.1.0.7 Patch 25 on Windows Platforms 10.2.0.4 Patch 14 on Windows Platforms 10.2.0.4 RAC Recommended Patch Bundle #3 10.2.0.4 Generic Recommended Patch Bundle #3

该Bug的3个表现：

(1)Hang(Involving Shared Resource)

(2)PerformanceAffected (General)

(3)Waits for "enq:RO - fast object reuse"

DBWR may use alot of CPU and seem to spin in or around kcbo_write_qdue to large number offree buffers on the object reuse queue or checkpoint queue.

In some casesthe CKPT holds the RO enqueue for very long blocking other operations with waitevent "enq: RO - fast objectreuse".

Operations so farreported being affected are :

- Apply Processes in StandBy databases

- Gather stats

- Truncates

- drop/shrink/alter tablespace

Note: This fix was previously incorrectlylisted as not affecting 11g.

The bug itself is present in 11g but it is unlikely to show anysignificant symptom due to other 11g changes meaning that free buffers are nolonger kept on the object queue.

对与该Bug 的解决方法：

setting _db_fast_obj_truncate=FALSE <--did not fix the issue
enabling asyn i/o <-- customer refused to implement to avoid corruptionsrisk
applying 7287289 <-- did not fix the issue

2.2 文档二

'enq: RO - fastobject reuse' contention when gathering schema/table statistics in parallel [ID762085.1]

Symptoms：

（1）Database has been recently upgradedfrom 10.2.0.1 to 10.2.0.4.
（2）There is'enq: RO - fastobject reuse' contention when gathering schema/table statistics in parallelusing DBMS_STATS package (with DEGREE>1).

其也是因为Bug 7385253导致这个问题。

解决方法：

1) Flushing the buffer cache.
OR
2) Setting "_db_fast_obj_truncate" =FALSE. This reverts back to the9i way of invalidating buffers in the buffer cache.

Kindly note thatboth workarounds could have an impact on the database performance. Instead, itis recommended applying the corresponding patch.

--这2种解决方法对db 性能都有很大影响，建议应用合适的patch。

2.3 文档三

Bug8544896 - Waits for "enq: RO - fast object reuse" with high DBWR CPU[ID 8544896.8]

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versionsbelievedto be affected	Versions >= 10.2.0.4 but BELOW 10.2.0.5
Versionsconfirmedas being affected	10.2.0.4
Platforms affected	Generic (all / most platforms affected)

It is believed to be aregressionindefaultbehaviour thus:
Regression introduced in 10.2.0.4

Fixed:

This issue is fixed in

10.2.0.4.3 (Patch Set Update) 10.2.0.4 Patch 27 on Windows Platforms

This problem is introduced in 10.2.0.4.

Sessions can wait on "enq: RO - fastobject reuse" while DBWR consumes lots of CPU when performing truncatetype operations.

Workaround:

（1）Flush the buffer cache beforetruncating

OR

（2） set _db_fast_obj_truncate = FALSE.

我这里出现这2个等待事件都与Truncate 操作有关。

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Skype: tianlesoftware

QQ: [e-mail]

Email: [e-mail]

Blog: http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823

DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940