oracle之 db file sequential read等待事件優化思想

為什麼db file sequential read事件在full table scan操作中顯現，為什麼在多塊讀中為什麼會有單塊讀存在 ？

extent的大小 :當擴充區中的最後一組塊僅是1個塊，oracle使用單塊讀來提取這個塊。這正常來說不是一個問題，除非你擴充區尺寸太小。以下是一個event 10046的trace檔案，顯示在全表掃描操作中包圍的db file sequential read事件。表塊尺寸是8K，MBRC是8個塊，擴充區尺寸是72K（9個塊）。如果表是大的，對表的全表掃描将導緻許多db file sequential read事件。如果是這種情況，全表掃描操作将完成的較快，如果表以一個較大的擴充區尺寸重建的話。

cached block: 在multiblock讀的一組中的1個或多個塊已經在buffer cache中了，是以oracle把fetch分割成2個或多個讀，它可以有單塊或多塊I/O組成。例如，如果MBRC是8，塊3和塊7是在buffer cache中，oracle将提出3個讀呼叫――第一個是塊1和塊2，第二個是塊4 和塊6，第三個是塊8。是以第三個fetch是單資料塊，等待事件就是db file sequential read。然而，對于前2個讀呼叫，這等待事件是db file scattered read，因為塊的數量是超過1的。是以，被緩存的塊能導緻全表掃描操作來執行比所需更多的讀。

chained or migrated rows: 這就是一個問題，當sql語句的執行計劃請求一個全表掃描的時候，如果你看到很多對該表的db file sequential read等待。這象征了表有許多連結或移植的行。Oracle使用單塊讀i/o來通路每一個連結的或移植的行。檢查在DBA_TABLES視圖中的表的CHAIN_CNT。當然，CHAIN_CNT是LAST_ANALYZED日期開始的。被移植的行能通過重組表來糾正（譬如export和import，或alter table move）。

補充：

行遷移 成因 ：當發出update導緻記錄行長增加，block的剩餘空間不足以存放這條記錄，就會産生行遷移，發生行遷移時rowid不會改變，原來的block 中會用一個指針存放這條記錄在新的block中的位址，發生行遷移會對性能産生影響，因為讀這條記錄會讀兩個BLOCK。

行連結 成因 ：當一個BLOCK不足以存放下一條記錄的時候，就會發生行連接配接，這個時候oracle會把這條記錄分成幾個部分，分别存放在幾個block中，然後把這幾個block chain起來。行連接配接同樣會影響性能，因為讀一條記錄至少會讀兩個BLOCK.

index entry creation : 它不是一個問題，當你在sql語句執行計劃呼叫一個全表掃描的時候，如果你看見許多針對index的db file sequential read等待。在以下例子中，TABLE_A有一個索引，db file sequential read等待是讀index塊到SGA來充滿來自TABLE_B資料的結果。注意db file sequential read等待與db file scattered read統計資料上比較的數量。這暗示你不能再假設你将從執行計劃上就能看到是哪個瓶頸。許多DBAs希望看到許多db file scattered read事件。另一個一文不值的觀點就是db file sequential read等待事件不會應用于insert語句。一般的誤解就是它僅應用到update和delete語句。

解決對策：

1、應用程式層

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

2、oracle記憶體層

如果高速緩存區過小，就會反複需要實體I/O，相應的db file scattered read等待也會增加。這時free buffer waits等待事件一同出現的幾率較高。FTS引起的db file scattered read等待的嚴重性不僅在于需要I/O，而且在于降低高速緩存區的效率，進而影響會話工作。從這種角度出發，處理FTS的有效方法之一就是使用多重緩沖池。讀取一次後不再使用的資料，有必要儲存到高速緩存區進而導緻影響其他使用者的工作嗎？多重緩沖池雖然是有效管理高速緩存區的強有力的方法，但是遺憾的是沒有被廣泛使用。多重緩沖池從三個方面改善高速緩存區的性能。第一，将經常通路的對象儲存與記憶體，進而将實體I/O最小化。第二，臨時性資料所占用的記憶體被快速的重新使用，進而将記憶體的浪費最小化。第三，因為每個緩沖池各使用不同的cache buffers lru chain鎖存器，是以有減少鎖存器争用的效果。指定DEFAULT将适用預設的緩沖池。這個選項适用于沒有配置設定給KEEP緩沖池和RECYCLE緩沖池的其它資料庫對象。通常将經常通路的對象放入KEEP緩沖池中，指定KEEP将把資料塊放入KEEP緩沖池中。維護一個适當尺寸的KEEP緩沖池可以使Oracle在記憶體中保留資料庫對象而避免I/O操作。通常将偶爾通路的大表放入RECYCLE緩沖池中，指定RECYCLE将把資料塊放入RECYCLE緩沖池中。一個适當尺寸的RECYCLE緩沖池可以減少預設緩沖池為RECYCLE緩沖池的資料庫對象的數量，以避免它們占用不必要的緩沖空間。

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

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

3、oracle段層

3.1、 需要檢查，通過合理執行partition能否減少FTS範圍。例如為獲得100萬個資料中10萬個資料而執行FTS時，将10萬個資料相應的範圍利用partition分開，則可以将FTS的範圍縮小至1/10。

3.2、 檢視表的行遷移、行連結、pctfree，如果行遷移數量過高，pctfree 過低，則增大pctfree ，重建表。如果行連結過高，則加大BLOCK塊

-- 檢視行遷移/行連結

SQL> analyze table emp list chained rows;

SQL> select count(*) from chained_rows where table_name='EMPLOYEES_TEMP';

行遷移加大表的 pctfree ，重建表。

行連結隻有通過加大BLOCK塊的方式才可以避免，如下：

create tablespace dba_16k 

blocksize 16K

datafile '/home/oracle/dba_16k.DBF' size 100M 

autoextend on 

extent management local 

segment space management auto;

alter table EMPLOYEES_TEMP move tablespace dba_16k;

alter index idx_emp_id rebuild ;

delete from chained_rows ;

commit;

analyze table EMPLOYEES_BK list chained rows into chained_rows;

select count(*) from chained_rows where table_name='EMPLOYEES_TEMP';

4、OS/裸裝置層

如果利用sql的優化或高速緩存區的優化也不能解決問題，就應該懷疑I/O系統本身的性能。将db file scattered read事件的等待次數和等待時間比較後，如果平均等待時間長，緩慢的I/O系統成為原因的可能性高。之前也讨論過，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 平均等待時間（cs） 

round((s.readtim - s.singleblkrdtim) / 

nullif((s.phyblkrd - s.singleblkrds), 0), 

3) as multiblk_avgtim --Multi Block I/O 平均等待時間（cs） 

from v$filestat s, v$datafile f 

 where s.file# = f.file#;

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

說明：總結于網絡

http://blog.csdn.net/zq9017197/article/details/7925338

本文轉自 張沖andy 部落格園部落格，原文連結：  http://www.cnblogs.com/andy6/p/7444986.html ，如需轉載請自行聯系原作者

http://www.cnblogs.com/andy6/p/7444986.html