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

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

 It is believed to be a regression in default behaviour 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:              tianlesoftware@gmail.com

Email:   tianlesoftware@gmail.com

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