Note 1：

從策劃到設(shè)計(jì)制作，每一步都追求做到細(xì)膩，制作可持續(xù)發(fā)展的企業(yè)網(wǎng)站。為客戶提供成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站、網(wǎng)站策劃、網(wǎng)頁(yè)設(shè)計(jì)、域名申請(qǐng)、虛擬主機(jī)、網(wǎng)絡(luò)營(yíng)銷、VI設(shè)計(jì)、 網(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。在此過(guò)程中，可能會(huì)遇到等待你本地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 表示會(huì)話在等待自己的寫操作。在磁盤發(fā)生嚴(yán)重問(wèn)題時(shí)會(huì)發(fā)生(例如RAID 5的一個(gè)磁盤崩潰,或者磁盤控制器錯(cuò)誤),這在正常的系統(tǒng)中極少發(fā)生，在TRUNCATE 一個(gè)大表而這個(gè)表在緩存中的時(shí)候，會(huì)話必需進(jìn)行一個(gè)local checkpoint,這個(gè)時(shí)候會(huì)話會(huì)等待local session wait.

 

在MOS 的文檔：

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

提到了這個(gè)等待事件。

 

1          Cause：

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

涉及到臨時(shí)表被以并發(fā)多路并行形式truncate和repopulate，可能會(huì)出現(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. 

 

另外個(gè)例子（from internet）：

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

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

DESCRIPTION

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

Coordinates flushing of multiple objects

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

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

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

      該問(wèn)題不能通過(guò)增大BUFFER CACHE來(lái)解決，BUFFER CACHE 愈大，搜尋相關(guān)數(shù)據(jù)塊的時(shí)間愈長(zhǎng)。

      處理的方法

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

2 保證DBWn寫入的效率。

網(wǎng)頁(yè)名稱：Oraclelocalwritewait等待事件
轉(zhuǎn)載來(lái)于：http://chinadenli.net/article12/gshsgc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、軟件開(kāi)發(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)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)