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

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2144256/，如需轉載，請註明出處，否則將追究法律責任。