Oracle Local Write Wait event

Note 1:

TYPICALLYDBWR have to free up some buffers when you want to read something from the disk. During This process there is chances that you'll be a waiting for your local buffer (i.e blocks dirtied/invalidated by R session) to is written to disk. Duringthis time the waits is shown as local write waits.

when you want to read the data from this disk, DBDW had to empty some buffer. During this process, you may experience waiting for your local buffer to write to the disk (such as dirty blocks, failed blocks).

Note 2:

         basically   ' localwrite ' Wait happens (as the name Indicates) when the session was waiting for itslocal (means writes pending because of its own operation)   write O Peration. This could happen typically if the underlying disc have some serious problems (one of the member disk crash in Raid-05-for example, or a controllerfailure). That's why I might has said ' you never see this wait in the normaldatabases! '.   you the during (rarely) truncating a largetable while the very most of the buffers of that table in cache. During truncates thesession have to a local checkpoint and During this process, the session may waitfor ' local write ' wait.

basically ' localwrite ' Wait indicates that the session is waiting for its own write operation. Occurs when there is a serious problem with the disk ( for example , a RAID 5 disk crash , or a disk controller error ), which rarely occurs in a normal system, in TRUNCATE A large table While this table is in the cache, the session must have a local checkpoint, at which time the session waits for the local session to wait.

in the MOS Documentation:

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

Mention of this waiting event.

1. cause :

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

This can occur when a temporary table is involved in concurrent multipath parallel form truncate and repopulate.

The underlying problem is we had to writethe object ' s dirty buffers to disk prior to actually truncating or dropping Theo Bject. This ensures instance recoverability and avoids a stuck recovery. Itseems at first glance perfectly reasonable to simply truncate a temporarytable and then repopulate for another usage. 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 B Uffer Cache. You'll 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-make sure a ' temp ' table that's frequently truncated has storage defined so tha T Itoccupies one extent. But this workaround was only available as long as Theextent was no more than 50% the size of the buffer cache. In non-racenvironments the table still had to being smaller than 50% of the buffercache, but it allows the table to having up t o 5 extents before falling backto the old algorithm.

an additional example (from the Internet):

a system of data warehouses, in The pre-wait time in the AWR report appears as LOCAL WRITE wait and Eq:ro Fast Object reuse. the parsing-related statements are TRUNCATE an intermediate table. The meaning of the RO queue can be retrieved from the V$lock_type .

Sql> SELECT DESCRIPTION from V$lock_type WHERE type= ' RO ';

DESCRIPTION

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

Coordinates flushing of multiple objects

the literal meaning is to coordinate the emptying of multiple objects. Analysis application, the statement occurs in the ETL process, the main step is to fill the intermediate table, modify the intermediate table,TRUNCATE the intermediate table. There are more processes in the scheduler.

in the TRUNCATE and DROP TABLE , ORACLE must invalidate or flush the data blocks of all the objects in data BUFFER to the disk, and the step is to request RO at this time Queue Lock, find the block of the object in the buffer, and invalidate it or flush to disk, and then release the RO lock, if more than one process concurrently TRUNCATE, will be in the RO Queue competition, performance as waiting events Eq:ro Fast object reuse. if TRUNCATE waits for the relevant block to flush to disk, which is expressed as the wait time local write wait.

This problem occurs for two reasons:1 I/O slow 2 parallel TRUNCATE.

the problem cannot be increased by increasing Buffer cache To resolve,the larger the buffer cache , the longer the search for the relevant data block.

Methods of processing

1. You can use a non-default block-size tablespace for this type of intermediate table and set a different block-size buffer in CACHE buffer. Reduce the time to search for relevant chunks of data, reducing competition.

2. Guarantee The efficiency of DBWN writes.

This article is from the "90SirDB" blog, be sure to keep this source http://90sirdb.blog.51cto.com/8713279/1931831

Oracle Local Write Wait event