Oracle local write wait and Enq: Ro-fast Object reuse wait Event Description

 

In AWR, you can see the waiting events for local write waits and Enq: Ro-fast Object reuse.

 

 

 

1. Description of local write waits waiting

 

Description of local write waits on the Internet:

 

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 cocould 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 databa Ses! '. 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.

Basically, 'local write' wait indicates that the session is waiting for its write operation. When a disk encounters a serious problem (such as a disk crash in RAID 5 or a disk controller error), this rarely happens in a normal system, when truncate a large table and this table is in the cache, the session must have a localcheckpoint. At this time, the session will wait for localsession wait.

 

In the mos documentation:

Truncates taking too long ...... [ID 334822.1]

This wait event is mentioned.

 

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 lelesoft 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.

 

 

Ii. Enq: Ro-fast Object reuse wait event

 

This wait event is mostly related to bugs.

 

2.1 bug 1: Bug 7385253

 

Bug 7385253-slow truncate/dbwr useshweigh 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

 

 

Three manifestations of this bug:

(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 previusly 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.

 

Solution to this bug:

Setting _ db_fast_obj_truncate = false <-- did not fix the issue
Enabling Asyn I/O <-- customer refused to implement to avoid upload uptionsrisk
Applying 7287289 <-- did not fix the issue

 

2.2 document 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 ).

 

This problem is also caused by bug 7385253.

 

Solution:

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 cocould have an impact on the database performance. Instead, itis recommended applying the corresponding patch.

-- These two solutions have a great impact on DB performance. We recommend that you apply appropriate patches.

 

2.3 document 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 a regression inDefaultBehaviour 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 refreshing Ming truncatetype operations.

 

 

Workaround:

(1) flush the buffer cache beforetruncating

Or

(2) Set _ db_fast_obj_truncate = false.

 

 

These two wait events are related to the truncate operation.

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

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

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

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940