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 time the waits 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" table that isfrequently truncated have storage defined so that it occupies one extent.But this workaround is only available as long as the extent is no morethan 50% the size of the buffer cache. In non-RAC environments the tablestill has to be smaller than 50% of the buffer cache, but it allows thetable to have up to 5 extents before falling back to the old algorithm.

二．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 versions believed* to be affected**	Versions >= 10 but BELOW 11.2
*Versions confirmed* as 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

该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 versions believed* to be affected**	Versions >= 10.2.0.4 but BELOW 10.2.0.5
*Versions confirmed* as being affected**	10.2.0.4
Platforms affected	Generic (all / most platforms affected)