Note 1：

從策劃到設(shè)計制作，每一步都追求做到細(xì)膩，制作可持續(xù)發(fā)展的企業(yè)網(wǎng)站。為客戶提供成都網(wǎng)站設(shè)計、做網(wǎng)站、網(wǎng)站策劃、網(wǎng)頁設(shè)計、域名申請、虛擬主機(jī)、網(wǎng)絡(luò)營銷、VI設(shè)計、 網(wǎng)站改版、漏洞修補(bǔ)等服務(wù)。為客戶提供更好的一站式互聯(lián)網(wǎng)解決方案,以客戶的口碑塑造優(yōu)易品牌,攜手廣大客戶,共同發(fā)展進(jìn)步。

         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.

當(dāng)你想從此盤讀取數(shù)據(jù)，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 表示會話在等待自己的寫操作。在磁盤發(fā)生嚴(yán)重問題時會發(fā)生(例如RAID 5的一個磁盤崩潰,或者磁盤控制器錯誤),這在正常的系統(tǒng)中極少發(fā)生，在TRUNCATE 一個大表而這個表在緩存中的時候，會話必需進(jìn)行一個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.

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

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）：

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

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

DESCRIPTION

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

Coordinates flushing of multiple objects

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

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

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

      該問題不能通過增大BUFFER CACHE來解決，BUFFER CACHE 愈大，搜尋相關(guān)數(shù)據(jù)塊的時間愈長。

      處理的方法

1  可以對這種類型的中間表使用非默認(rèn)塊大小的表空間，在CACHE BUFFER中設(shè)定不同塊大小的緩沖區(qū)。減小搜尋相關(guān)數(shù)據(jù)塊的時間，降低競爭。

2 保證DBWn寫入的效率。

網(wǎng)頁名稱：Oraclelocalwritewait等待事件
轉(zhuǎn)載來于：http://www.rwnh.cn/article12/gshsgc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、軟件開發(fā)、自適應(yīng)網(wǎng)站、標(biāo)簽優(yōu)化、靜態(tài)網(wǎng)站、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)