通過案例學調優之--Oracle參數（db_file_multiblock_read_count）

通過案例學調優之--Oracle參數（db_file_multiblock_read_count）

應用環境：

操作系統： RedHat EL55

Oracle：   Oracle 10gR2

  Oracle DB_FILE_MULTIBLOCK_READ_COUNT是Oracle比較重要的一個全局性參數，可以影響系統級別及sessioin級別。主要是用于設置最小化表掃描時Oracle一次按順序能夠讀取的數據塊數。通常情況下，我們看到top events中的等待事件db file scattered read時會考慮到增加該參數的值。

1、參數DB_FILE_MULTIBLOCK_READ_COUNT(MBRC)
       參數DB_FILE_MULTIBLOCK_READ_COUNT簡寫為(MBRC)。
       該參數是最小化表掃描的重要參數，用于指定Oracle一次按順序能夠讀取的數據塊數。理論上該值越大則能夠讀取的數據塊越多。
       實現全表掃描，索引全掃描及索引快速掃描所需的I/O總數取決于該參數，以及表自身的大小，是否使用并行等等。
       Oracle 10gR2以后會根據相應的操作系統及buffer cache以最優化的方式來自動設定該參數的值。通常情況下該值為1MB/db_block_size。
       在最大I/O為1MB的情況下，block的大小為8KB，則參數的值為128。如果在最大I/O為64KB，block為8KB，則參數的值為8。
       對于OLTP和batch環境該參數的值為4到16，DSS環境應設置大于16以上或大的值。
       該參數的變化對數據庫性能產生整體性的影響，過大的設置會導致大量SQL訪問路徑發生變化，如原先的索引掃描傾向于使用全表掃描。
       按照Oracle的建議在10g R2之后盡可能使用oracle自動設置的值。

2、參數DB_FILE_MULTIBLOCK_READ_COUNT與SSTIOMAX
     In Release 9.2 and above; follow the explanation below:
     Each version of Oracle on each port, is shipped with a preset maximum of how much data can be transferred in a single read (which of course is equivalent to the db_file_multiblock_read_count since the block size is fixed). 
     For 8i and above (on most platforms) this is 1Mb and is referred to as SSTIOMAX.
     To determine it for your port and Oracle version, simply set db_file_multiblock_read_count to a nonsensical value and Oracle will size it down for you.
     從上面的描述可知，Oracle 9.2之后，有一個名叫SSTIOMAX的東東，限制了MBRC的設置。
     由于SSTIOMAX大多數平臺最大單次I/O為1MB，db_block_size為8kb，因此MBRC參數的最大值通常為128。128*8kb=1mb。
     對于設置大于1MB的情形，即MBRC*db_block_size>SSTIOMAX的情形，則設置的值并不生效，而是使用符合SSTIOMAX的最大MBRC值。

3、如何計算MBRC
     The formula as internally used is as below:
         db_file_multiblock_read_count = min(1048576/db_block_size , db_cache_size/(sessions * db_block_size))

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

       如果必須創建小的區間，創建其大小是操作系統I/O緩沖區大小的整數倍

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

案例分析：

案例1

15:21:10 SYS@ test1 >show parameter mult

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count        integer     128

1）查看表中extent分配

14:46:03 SCOTT@ test1 >col segment_name for a20

14:46:15 SCOTT@ test1 >select segment_name,extent_id,bytes,blocks from user_extents

14:46:36   2   where segment_name='T1';

SEGMENT_NAME          EXTENT_ID      BYTES     BLOCKS
-------------------- ---------- ---------- ----------
T1                            0      65536          8
T1                            1      65536          8
T1                            2      65536          8
T1                            3      65536          8
T1                            4      65536          8
T1                            5      65536          8
T1                            6      65536          8
T1                            7      65536          8
T1                            8      65536          8
T1                            9      65536          8
T1                           10      65536          8
T1                           11      65536          8
T1                           12      65536          8
T1                           13      65536          8
T1                           14      65536          8
T1                           15      65536          8
T1                           16    1048576        128
SEGMENT_NAME          EXTENT_ID      BYTES     BLOCKS
-------------------- ---------- ---------- ----------
T1                           17    1048576        128
T1                           18    1048576        128
T1                           19    1048576        128
T1                           20    1048576        128
T1                           21    1048576        128
22 rows selected.

2）配置10046進行分析

14:48:00 SCOTT@ test1 >alter session set events '10046 trace name context forever,level 8';
Session altered.

14:48:57 SCOTT@ test1 >select count(*) from t1;
  COUNT(*)
----------
      5002

14:49:09 SCOTT@ test1 >alter session set events '10046 trace name context off';
Session altered.

3）查看trace文件

[oracle@rh7 ~]$ more  /u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_4160.trc

WAIT #2: nam='db file sequential read' ela= 9 file#=11 block#=691 blocks=1 obj#=16394 tim=1408604404294713
WAIT #2: nam='Disk file operations I/O' ela= 26 FileOperation=2 fileno=4 filetype=2 obj#=16394 tim=1408604404294846
WAIT #2: nam='db file sequential read' ela= 9 file#=4 block#=143 blocks=1 obj#=16394 tim=1408604404294872
WAIT #2: nam='db file scattered read' ela= 23 file#=4 block#=157 blocks=3 obj#=16394 tim=1408604404294998
WAIT #2: nam='db file sequential read' ela= 8 file#=4 block#=164 blocks=1 obj#=16394 tim=1408604404295041
WAIT #2: nam='db file sequential read' ela= 8 file#=11 block#=727 blocks=1 obj#=16394 tim=1408604404295069
WAIT #2: nam='db file sequential read' ela= 8 file#=4 block#=183 blocks=1 obj#=16394 tim=1408604404295097
WAIT #2: nam='db file scattered read' ela= 33 file#=4 block#=187 blocks=5 obj#=16394 tim=1408604404295156
WAIT #2: nam='db file sequential read' ela= 8 file#=4 block#=199 blocks=1 obj#=16394 tim=1408604404295191
WAIT #2: nam='db file scattered read' ela= 51 file#=11 block#=153 blocks=8 obj#=16394 tim=1408604404295272
WAIT #2: nam='db file scattered read' ela= 50 file#=11 block#=203 blocks=8 obj#=16394 tim=1408604404295355
WAIT #2: nam='db file scattered read' ela= 52 file#=11 block#=213 blocks=8 obj#=16394 tim=1408604404295442
......

      從以上文件可以看出，在“db file scattered read”中每次讀取的數據塊個數（blocks=8）不超過extent中blocks的大小（8個）；在“db file sequential read”中每次讀取的數據塊個數（blocks）為1個。

案例2：

15:21:10 SYS@ test1 >show parameter mult

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_file_multiblock_read_count        integer     128

1）創建新的segment

15:08:28 SCOTT@ test1 >create table t2 (id int)

15:08:35   2  storage (initial 2048k next 2048k pctincrease 0);

Table created.

15:08:41 SCOTT@ test1 >col segment_name for a20

15:08:53 SCOTT@ test1 >select segment_name,tablespace_name,extent_id,bytes,blocks from user_extents

15:09:19   2   where segment_name='T2';

SEGMENT_NAME         TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
-------------------- ------------------------------ ---------- ---------- ----------
T2                   USERS                                   0    1048576        128
T2                   USERS                                   1    1048576        128

2）配置10046進行分析

15:11:00 SCOTT@ test1 >alter session set events '10046 trace name context forever,level 8';
Session altered.

15:14:57 SCOTT@ test1 >select count(*) from t2;
  COUNT(*)
----------
      32002

15:15:09 SCOTT@ test1 >alter session set events '10046 trace name context off';
Session altered.

3）查看trace文件

[oracle@rh7 ~]$ more  /u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_4260.trc

PARSE #5:c=0,e=302,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1408605696796384
EXEC #5:c=1000,e=1131,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=3321871023,tim=1408605696797594
WAIT #5: nam='db file sequential read' ela= 75 file#=11 block#=512 blocks=1 obj#=16404 tim=1408605696797838
WAIT #5: nam='db file sequential read' ela= 15 file#=11 block#=514 blocks=1 obj#=16404 tim=1408605696797913
WAIT #5: nam='db file scattered read' ela= 1123 file#=11 block#=528 blocks=48 obj#=16404 tim=1408605696799202

從以上文件可以看出，在“db file scattered read”中每次讀取的數據塊個數（blocks=48）不超過extent中blocks的大小（128個）；在“db file sequential read”中每次讀取的數據塊個數（blocks）為1個。

附注：