淺析oracle常見等待事件之 db file scattered read

淺析oracle常見等待事件之 db file scattered read(轉） 

原文位址： http://www.hellodml.com/2011/12/%E6%B5%85%E6%9E%90oracle%E5%B8%B8%E8%A7%81%E7%AD%89%E5%BE%85%E4%BA%8B%E4%BB%B6%E4%B9%8B-db-file-scattered-read/  

Oracle 在執行全表掃描（full table scan，FTS）或全索引掃描（index full scan）時，為了保障性能，盡量一次性讀取多個塊，這成為multi block I/O。每次執行multi block I/O，都會等待實體I/O結束，此時出現等待事件：db file scattered read。利用 db file scattered read等待事件的P1=file#，P2=初始 block#，P3=要讀取的塊數資訊，可以确認哪些段會發生multi block I/O。

Oracle 按照 db_file_multiblock_read_count（以下簡稱MBRC）參數值進行 multi block I/O.這個值在不同的OS上都有最大值的界定，可以通過如下方法确認最大值。

使用盡量這個詞彙時需要注意，因為oracle在執行FTS時也執行single block I/O，這時即使FTS也會發生db file sequential read等待，注意，這裡的sequential與scattered，不是說被讀取的表或者索引上的塊是連續的還是分散的，而是指讀到記憶體中的時候，是連續的還是分散的 。FTS上使用single block I/O可能讀取比MBRC定義的值小的塊數的情況如下：

1,達到區的邊界線時：如一個區有9個塊，一次multi block I/O讀取了8個塊，則一次以multi block I/O讀取之後的剩餘一個塊通過single block I/O讀取。如果剩下的塊有兩個，就會執行multi block I/O，而且隻讀取兩個塊。

2,掃描過程中讀取被緩存的塊時:如果讀取8個塊時，若其中第三個塊被緩存，oracle将前兩個塊通過multi block I/O讀取，對于第三塊執行一次logical I/O,剩下的5個塊通過multi block I/O讀取。這種情況經常發生時，因引發多次的I/O，可能成為FTS速度下降的原因。

3,存在行連結時：在執行FTS的過程中，如果發現了行連結，oracle為了讀取剩下的行而引起的附加I/O，此時執行single block I/O，對于行連結和行遷移的（migrated row)的不同點需要準确了解。行連結時在行的大小比塊的大小大的時候發生，是以使用更大的塊或者減少pctfree值之外，在沒有可消除行連結的方法。行遷移起初記錄到一個塊中，但随着行的大小不斷增大，塊内沒有空間時發生遷移。這時實際上行移動到另外的塊，在原來的行裡插入了指明轉以後的塊和行位置的rowid。行遷移特别是在通過索引掃描表時會給性能帶來嚴重影響，這是因為讀取一個行時需要讀取兩個或兩個以上的塊。行遷移對FTS的 影響需要稍微留意。FTS是對HWM以下所有的塊從頭開始順序讀取的工作。在執行FTS過程中，oracle如果遇到行遷移，則不會引發更多的single block I/O，而是繼續工作。這是因為知道反正要在掃描過程中需要重新讀取。是以如果HWM的位置相同，則不管行遷移存在與否，FTS自身性能幾乎不變。當然，如果發生了遷移，就會追加配置設定區，如果HWM移動的更遠，就會給FTS的性能造成影響，消除行遷移是要留心以上部分。

db file scattered read 事件與db file sequential read 事件相同，是oracle中最經常發生的等待事件。因為從資料檔案讀取塊時隻能執行multi block I/O或者single block I/O，大家對db file scattered read等待有所顧忌的原因是它與實體I/O相關，還有它與FTS一起出現。最終其還是與FTS合不合适想聯系。

下面，我們針對oracle的I/O層，讨論對以db file scattered read等待的解決方法。

一 應用程式層

需要篩選出主要發生db file scattered read等待的SQL語句。如果不必要地執行FTS或者index ful scan，修改sql語句或者建立更合理的索引就可以解決。大量讀取資料時，多數情況下FTS性能更好。我們要考慮相應的SQL語句後，來判斷FTS有利還是index range scan有利，而不是盲目的建立索引。

二 oracle 記憶體層

如果buffer cache過小，就會反複需要實體I/O，相應地 db file scattered read等待也會增加。這時free buffer waits等待事件一同出現的幾率較高。FTS引起的db filescattered read等待的嚴重性不僅在于需要I/O，而且在于降低告訴緩沖區的效率。進而影響會話的工作。從這個角度出發，處理FTS的有效方法之一就是使用多重緩沖池。讀取一次就不在使用的資料，沒有必要儲存到告訴緩沖區進而導緻影響其他使用者額工作。多重緩沖區雖然是有效管理緩沖區的強有力的方法，但是并未被廣泛使用。多重緩沖區從3個方面改善高速緩沖區的性能：第一，将經常通路的對象儲存于記憶體，進而将實體I/O最小化。第二，臨時性資料所占用的記憶體被快速的重寫，進而将記憶體的浪費最小化。第三，因為每個緩沖池各使用不同的cache buffers lru chain 鎖存器，是以有減少鎖存器争用的效果。

有效使用FTS的另一種方法是将db_file_multiblock_read_count參數值提高。這個參數決定執行multi block I/O時一次讀取的塊數。是以随着這個值的提高，FTS速度也相應會提升。而且db file scattered read 等待也會相應減少。但将該值在全系統上設定過高并不妥當，最好是利用alter session set 指令，隻在執行sql語句期間臨時提升這個值，因為這個值如果升高，有關FTS的cost會計算的較低，可能導緻不可預料的SQL執行計劃的變更。

使用較大的塊也是提高FTS性能的方法，較大的塊在如下兩個方面改善FTS的性能，第一，增加一個塊所包含的行數，這樣組成相同大小的表時使用更少的塊數，相應地multi block I/O次數也會減少。第二，如果資料塊較大，則發生行連結或者行遷移的機率會降低，附加的I/O也随之降低。大部分OLTP系統上一般隻使用标準塊大小（8K），但是經常掃描大量資料的DSS上使用更大的塊能改善性能。

三 oracle段層

設定合理的表分區可以有效的減少FTS範圍，例如為了獲得100萬資料中的10萬個資料而執行FTS時，将10萬個資料相應的範圍利用partitioning分開，則可以将FTS的範圍縮小至十分之一。

四 OS/裸裝置層

如果利用SQL的優化或者buffer cache的優化也不能解決問題，就應該懷疑I/O系統本身的性能。将db file scattered read 事件的等待次數和等待時間比較後，如果平均等待時間長，緩慢的I/O系統可能是主要的原因。

利用v$filestat視圖，可以分别獲得各資料檔案關于multi block I/O和single block I/O的活動資訊。

select f.file#, f.name,

s.phyrds,s.phyblkrd,s.readtim,–所有的讀取工作資訊

s.singleblkrds,s.singleblkrdtim,–single block I/O

(s.phyblkrd – s.singleblkrds) as multiblkrd,– multi block I/O次數

(s.readtim – s.singleblkrdtim) as multiblkrdtim,– multi block I/O時間

round(s.singleblkrdtim/decode(s.singleblkrds,0,1,s.singleblkrds),3) as singleblk_avgtim,–single block I/O平均等待時間（毫秒）

round((s.readtim – s.singleblkrdtim)/(s.phyblkrd – s.singleblkrds),3) as multiblk_avgtim — multi block I/O平均等待時間（毫秒）

from v$filestat s, v$datafile f

where s.file#=f.file#

and s.phyblkrd – s.singleblkrds !=0;

如果特定檔案上平均執行時間表現的過高，則應該通過該檔案所在的I/O系統的性能，以此改善性能。沒有關于multi block I/O的最合理的平均等待時間值，但一般應該維持在10微秒左右的平均等待時間。

Metalink相關note：

WAITEVENT: “db file scattered read” Reference Note [ID 34558.1]

Systemwide Waits:

If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at <> whereBLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).

It can also be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited

FROM v$session_event

WHERE event=’db file scattered read’

and total_waits>0

ORDER BY 3,2

Delete or Update running slow — db file scattered read waits on index range scan [ID 296727.1]

适用範圍：9.2.0.6和更高版本。

問題描述：

Sql運作緩慢，通過設定10046事件發現db file scattered read 等待事件，并且db file scattered read 發生在一個索引上。

然而執行計劃表明表在使用index range scan，并且沒有迹象表明出現全表掃描或者全索引掃描，但是，對索引的index range scan應該會造成db file sequential reads等待事件。

同時，單次db file scattered read所取得的資料塊數是11，這個數字這與

db_file_multiblock_read_count并不一樣。

原因：

這是由在index range scan操作期間對索引葉子節點的預提取造成的。oracle可以在對索引預提取的同時為了維護是以而進行删除和更新操作。并且，預設的，所有索引開啟預提取功能，預提取的資料塊數目取決于隐含參數_db_file_noncontig_mblock_read_count ，該參數預設值為11.

_index_prefetch_factor 參數影響關于提取索引期間需要執行的I/O操作數量的優化估計。

解決方案：

索引預提取操作隻有很小的幾率會對查詢造成影響，可以通過設定_db_file_noncontig_mblock_read_count 為0或者1來關閉預提取功能。然而通常不建議這麼做。

下面的選項可以嘗試用來提高查詢的性能：

1 重建目前索引

2 基于where子句對應列建立更優化的索引。

How to Tell if the IO of the Database is Slow [ID 1275596.1] （未全部閱讀）

判斷I/O相應時間：

下面是常見等待時間以及通常情況下可接受的等待時間。

Troubleshooting I/O-related waits [ID 223117.1] （未閱讀）

Full Table Scans On A Table Is Reading 1 Block At A Time. (Due To Chained / Migrated Rows) [ID 554366.1]

大概：某表的pctfree過低（僅僅設定為5），并且更新操作非常頻繁，導緻大量行遷移的産生，表中90%的行屬于遷移行，這導緻了全表掃描一次僅讀取一個資料塊。

當使用pctfree為20重建表後，行遷移消失，問題得到解決。

适用範圍：

Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.1.0.6 – Release: 8.1.7 to 11.1

References

NOTE:102989.1 – How to Find and Eliminate Migrated and Chained RowsNOTE:122020.1 – Row Chaining and Row Migration

疑問：與OWI中關于行遷移對db file scattered read介紹有所出入，需要用實驗驗證。

TROUBLESHOOTING: Advanced Query Tuning [ID 163563.1] （未閱讀）