Oracle local write wait等待事件

Note 1：

         TypicallyDBWR has 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 local buffer(i.e blocks dirtied/invalidated by your session) to be written to disk. Duringthis time the waits are shown as local write waits.

當你想從此盤讀取數據，DBDW不得不清空一些buffer。在此過程中，可能會遇到等待你本地buffer寫入磁盤（如臟塊、失效的塊）。

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)  write operation.This could happen typically if the underlying disc has some serious problems(one of the member disk crash in RAID-05 - for example, or a controllerfailure). That is why I might have said ' you never see this wait in the normaldatabases!'.  You may see this during (rarely) Truncating a largetable while most of the buffers of that table in cache. During TRUNCATEs thesession has to a local checkpoint and during this process, the session may waitfor 'local write' wait.

         基本上'localwrite' wait 表示會話在等待自己的寫操作。在磁盤發生嚴重問題時會發生(例如RAID 5的一個磁盤崩潰,或者磁盤控制器錯誤),這在正常的系統中極少發生，在TRUNCATE 一個大表而這個表在緩存中的時候，會話必需進行一個local checkpoint,這個時候會話會等待local session wait.

在MOS 的文檔：

Truncates Taking Too Long... [ID334822.1]

提到了這個等待事件。

1          Cause：

Processes that involve temporary tablesbeing truncated and repopulated in multiple, concurrent batch streams maypresent this situation.

涉及到臨時表被以并發多路并行形式truncate和repopulate，可能會出現此類情況。

The underlying problem is we have to writethe object's dirty buffers to disk prior to actually truncating or dropping theobject. This ensures instance recoverability and avoids a stuck recovery. Itseems at first glance perfectly reasonable to simply truncate a temporarytable, then repopulate for another usage. And then to do the temporarypoplulate/truncate operations in concurrent batches to increase throughput.

However, in reality the concurrenttruncates get bogged down as dbwr gets busy flushing those dirty block buffersfrom the buffer cache. You will see huge CI enqueue waits. The multipletruncate operations in concurrent streams absolutely kill throughput. This isspecially critical with large buffers.

2          Solution：

         In9.2.0.5 and higher, it may also help to make sure a "temp"table that is frequently truncated have storage defined so that itoccupies one extent. But this workaround is only available as long as theextent is no more than 50% the size of the buffer cache. In non-RACenvironments the table still has to be smaller than 50% of the buffercache, but it allows the table to have up to 5 extents before falling backto the old algorithm. 

另外個例子（from internet）：

一個數據倉庫的系統，在AWR報告中出現靠前的等待時間為LOCAL WRITE WAIT和Eq:RO Fast Object Reuse. 分析相關的語句為TRUNCATE一個中間表。RO隊列的意義可以從V$LOCK_TYPE中檢索到。

SQL> SELECT DESCRIPTION FROM V$LOCK_TYPE WHERE TYPE='RO';

DESCRIPTION

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

Coordinates flushing of multiple objects

字面上的意思是協調清空多個對象。分析應用，該語句發生在ETL過程中，主要步驟為填充中間表，修改中間表，TRUNCATE中間表。在調度程序中有較多該流程的過程 .

       在TRUNCATE 和DROP TABLE的時候, ORACLE必須使DATA BUFFER中所有該對象的數據塊失效或者刷新到磁盤，步驟為此時請求RO隊列鎖，找緩沖區中該對象的塊，并使其無效化或者刷新到磁盤，然后釋放RO鎖，如果多個進程并發地進行TRUNCATE的時候，就會在RO隊列上發生競爭，表現為等待事件Eq: RO fast object reuse. 如果TRUNCATE等待相關的塊刷新到磁盤，就表現為等待時間local write wait.

      這個問題的發生有兩個原因：1 I/O慢2 并行TRUNCATE.

      該問題不能通過增大BUFFER CACHE來解決，BUFFER CACHE 愈大，搜尋相關數據塊的時間愈長。

      處理的方法

1  可以對這種類型的中間表使用非默認塊大小的表空間，在CACHE BUFFER中設定不同塊大小的緩沖區。減小搜尋相關數據塊的時間，降低競爭。

2 保證DBWn寫入的效率。