DB_FILE_MULTIBLOCK_READ_COUNT的設定

我們知道Oracle透過兩種方式從表中獲取資料：

· 透過ROWID（通常使用索引掃描時）

· 透過全表掃描

如果透過ROWID讀取資料，表中的區間數就不是讀效能的一個因素（如果使用並行查詢，那麼一個表中有較多的區間的數量可以明顯提高I/O的效能），Oracle將透過ROWID直接找到需要的行，並獲取相應資料。

如果是全表掃描，那麼區間的尺寸大小就有可能導致效能問題。因為全表掃描時，Oracle會一次讀取多個Blocks。每次讀取的塊數將受初始化引數DB_FILE_MULTIBLOCK_READ_COUNT和作業系統的I/O緩衝區大小的限制。比如說，如果Oracle Block的大小是4KB，作業系統I/O緩衝區大小是64KB，那麼在全表掃描時每次最多可以讀取16各塊（Oracle Blocks），所以此時將DB_FILE_MULTIBLOCK_READ_COUNT的值設定為超過16也改變不了全表掃描的效能了。

通常，設定DB_FILE_MULTIBLOCK_READ_COUNT引數是如下考慮的：

（1） 使用一個單獨的資料檔案建立一個新的表空間

（2） 在該表空間中建立一個單獨的未索引的表

（3） 查詢V$FILESTAT以驗證該測試的初始統計值

（4） 在表上執行全表掃描

（5） 查詢V$FILESTAT以確定該測試的終止統計值，並從中減去開始統計值。將PhyBlkRds值除以PhyRds以確定有效的多塊讀計數。

（6） 刪除這個用於測試的表空間

C:>sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 6月 28 10:11:22 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. 已連線到空閒例程。 SQL> startup ORACLE 例程已經啟動。 Total System Global Area 93395628 bytes Fixed Size 453292 bytes Variable Size 67108864 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 資料庫裝載完畢。 資料庫已經開啟。 SQL> show parameter db_block_size; NAME TYPE VALUE ------------------------------------ ----------- --------------------- db_block_size integer 8192 SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT; NAME TYPE VALUE ------------------------------------ ----------- ---------- db_file_multiblock_read_count integer 16 SQL> 使用一個單獨的資料檔案建立一個新的表空間： SQL> create tablespace lunar 2 datafile 'd:lunar.dbf' size 10m 3 default storage(initial 1m next 1m pctincrease 0); 表空間已建立。 在該表空間中建立一個單獨的未索引的表： SQL> create table lunar 2 tablespace lunar 3 as select * from dba_objects; 表已建立。 SQL> select relative_fno from dba_data_files 2 where tablespace_name='LUNAR'; RELATIVE_FNO ------------ 15 查詢V$FILESTAT以驗證該測試的初始統計值： SQL> select phyrds,phyblkrd from v$filestat 2 where file#=15; PHYRDS PHYBLKRD ---------- ---------- 0 0 在表上執行全表掃描： SQL> select count(*) from lunar; COUNT(*) ---------- 27547 查詢V$FILESTAT以確定該測試的終止統計值： SQL> select phyrds,phyblkrd from v$filestat 2 where file#=15; PHYRDS PHYBLKRD ---------- ---------- 24 376 SQL> PHYRDS 和 PHYBLKRD 的初始統計的值都是0； PHYRDS 和 PHYBLKRD 的終止統計的值分別是24和 376； PHYRDS 的終止統計的值 － PHYRDS 的初始統計的值 ＝24； PHYBLKRD 的終止統計的值 － PHYBLKRD 的初始統計的值 ＝376； PHYBLKRD 的差值 / PHYRDS 的差值 ＝ 15.67 所以，有效的多塊讀計數是16 需要注意的是，如果不是用新的表空間測試，那麼測試得到第3步和第5步的差值後，在會話級改變DB_FILE_MULTIBLOCK_READ_COUNT引數，然後再次得到第3步和第5步的差值，並重複測過程。 補充，重復第3步和第5步前，注意 alter session set events 'immediate trace name flush_cache' 因為，再次執行會從buffer內讀取，必須清空後，才會有disk io讀取 記住，不要將DB_FILE_MULTIBLOCK_READ_COUNT引數設定的比計算的值高。

設定區間尺寸大小的考慮思路應該是合理的利用Oracle的能力以便在全表掃描時執行多塊存取，同時讀操作又是不能跨區間的。舉個例子，假設作業系統I/O緩衝區大小是64KB，考察讀取一個640KB大小的表，如果設定為每個區間64KB，一共10個區間，如果執行全表掃描，則Oracle需要10次讀操作（相當於一次讀一個區間）；如果設定為一個640KB的區間，則Oracle還是需要10次讀操作（因為作業系統I/O緩衝區大小是64KB），可見壓縮區間並不能提高效能；如果設定為每個區間80KB，一共8個區間，則每個區間Oracle需要讀兩次，第一次讀64KB，第二次讀這個區間剩餘的16KB（讀操作不能跨區間），所以總共需要16次讀操作（相當於一次讀一個區間）。區間尺寸的設定對效能的影響是顯而易見的。

綜上，總結起來設定區間大小時需要考慮下面的問題：

· 建立明顯大於或者等於作業系統I/O緩衝區大小的區間（最好是作業系統I/O緩衝區大小的整數倍）。這樣，如果區間非常大，即使區間大小不是作業系統I/O緩衝區大小的整倍數，也只需要很少的附加讀操作（如果上面的640KB和80KB的差異）。

· 設定DB_FILE_MULTIBLOCK_READ_COUNT以充分利用作業系統I/O緩衝區的大小。應考慮DB_FILE_MULTIBLOCK_READ_COUNT <= 作業系統I/O緩衝區 / Oracle Block的大小，如果DB_FILE_MULTIBLOCK_READ_COUNT設定的太大，會使最佳化器認為全表掃描更有效而改變執行計劃，然後實際情況並非如此。

· 如果必須建立小的區間，建立其大小是作業系統I/O緩衝區大小的整數倍

補充：

從另一trace file獲得oracle單次讀取的block數量

alter session set events '10046 trace name context forever,level 12'

select count(*) from lunar;

PARSING IN CURSOR #1 len=68 dep=0 uid=0 oct=42 lid=0 tim=9298786450 hv=1346161232 ad='85354328'

alter session set events '10046 trace name context forever,level 12'

END OF STMT

PARSE #1:c=0,e=272,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=9298786446

BINDS #1:

EXEC #1:c=0,e=64,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=9298786578

WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0

WAIT #1: nam='SQL*Net message from client' ela= 5998717 p1=1413697536 p2=1 p3=0

=====================

PARSING IN CURSOR #1 len=26 dep=0 uid=0 oct=3 lid=0 tim=9304785606 hv=955040781 ad='853da7c4'

select count(*) from lunar

END OF STMT

PARSE #1:c=0,e=69,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=9304785602

BINDS #1:

EXEC #1:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=9304785750

WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0

WAIT #1: nam='db file sequential read' ela= 11040 p1=8 p2=9 p3=1

WAIT #1: nam='db file scattered read' ela= 12056 p1=8 p2=10 p3=16

WAIT #1: nam='db file scattered read' ela= 2770 p1=8 p2=50 p3=16

WAIT #1: nam='db file scattered read' ela= 2447 p1=8 p2=90 p3=16

WAIT #1: nam='db file scattered read' ela= 912 p1=8 p2=130 p3=7

WAIT #1: nam='db file scattered read' ela= 2414 p1=8 p2=137 p3=16

WAIT #1: nam='db file scattered read' ela= 2452 p1=8 p2=177 p3=16

WAIT #1: nam='db file scattered read' ela= 2711 p1=8 p2=217 p3=16

WAIT #1: nam='db file scattered read' ela= 679 p1=8 p2=257 p3=8

WAIT #1: nam='db file scattered read' ela= 2733 p1=8 p2=265 p3=16

WAIT #1: nam='db file scattered read' ela= 2502 p1=8 p2=305 p3=16

WAIT #1: nam='db file scattered read' ela= 2459 p1=8 p2=345 p3=16

WAIT #1: nam='db file scattered read' ela= 675 p1=8 p2=385 p3=8

WAIT #1: nam='db file scattered read' ela= 2492 p1=8 p2=393 p3=16

注意，上敘的p3=16，記錄了單次io的block數

假如，將db_file_multiblock_read_count改為1000

alter system set db_file_multiblock_read_count=1000;

alter session set events 'immediate trace name flush_cache';

alter session set events '10046 trace name context forever,level 12';

select count(*) from lunar;

再看trace file，如下

PARSING IN CURSOR #1 len=26 dep=0 uid=0 oct=3 lid=0 tim=10603977582 hv=955040781 ad='853da7c4'

select count(*) from lunar

END OF STMT

PARSE #1:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=10603977578

BINDS #1:

EXEC #1:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=10603977722

WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1413697536 p2=1 p3=0

WAIT #1: nam='db file sequential read' ela= 8290 p1=8 p2=9 p3=1

WAIT #1: nam='db file scattered read' ela= 12303 p1=8 p2=10 p3=127

WAIT #1: nam='db file scattered read' ela= 8081 p1=8 p2=137 p3=128

WAIT #1: nam='db file scattered read' ela= 8401 p1=8 p2=265 p3=128

WAIT #1: nam='db file scattered read' ela= 7918 p1=8 p2=393 p3=128

WAIT #1: nam='db file scattered read' ela= 7877 p1=8 p2=521 p3=128

WAIT #1: nam='db file scattered read' ela= 8118 p1=8 p2=649 p3=128

WAIT #1: nam='db file scattered read' ela= 7649 p1=8 p2=777 p3=128

WAIT #1: nam='db file scattered read' ela= 7492 p1=8 p2=905 p3=128

WAIT #1: nam='db file scattered read' ela= 8053 p1=8 p2=1033 p3=128

WAIT #1: nam='db file scattered read' ela= 4329 p1=8 p2=1161 p3=85

注意P3引數仍為128塊

[@more@]