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写入的效率。