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步的差值，並重複測過程。

記住，不要將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緩衝區大小的整數倍