Oracle db_file_mulitblock_read_count參數

     Oracle DB_FILE_MULTIBLOCK_READ_COUNT是Oracle比較重要的一個全域性參數，可以影響系統層級及sessioin層級。主要是用於設定最小化表掃描時Oracle一次按順序能夠讀取的資料區塊數。通常情況下，我們看到top events中的等待事件db file scattered read時會考慮到增加該參數的值。但是否增加了DB_FILE_MULTIBLOCK_READ_COUNT的值就一定可以減少db file scattered read? 本文描述了DB_FILE_MULTIBLOCK_READ_COUNT的設定並給出示範。

 

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))
  
     Without WORKLOAD stats, CBO computes multiblock reads as:
          io_cost = blocks/(1.6765 * power(db_file_multiblock_read_count,0.6581))
  
     With WORKLOAD stats, then:
          io_cost = blocks/mbrc * mreadtim/sreadtim
  
     下面是不同情形設定所致的最大i/o
        db_blocks_size      tablespace block size      db_file_multiblock_read_count   max_fetch_blocks_in_single_read
     ---------------     ----------------------     -----------------------------   -------------------------------
     8k                  8k                         32                              8*32=256kb(i/o)
     8k                  4k                         32                              8*32/4=64kb(i/o)
     8k                  8k                         not explicitly set              determined by OS and db_cache_size
     8k                  8k                         >128                            8*128=1MB(i/o)

 

4、哪些情形導致單次多塊讀少於預設定
     a、讀段頭時單塊讀(此情形顯而易見，通常一個extent包含一個段頭header)
     b、物理讀不能跨越多個區(extent)
     c、部分資料區塊已經位於快取則不會從I/O子系統再次讀取，除非是直接讀(direct path read)。

 

5、示範不同值的MBRC單次讀的block(system層級)

a、MBRC為16的情形--示範環境sys@SYBO2SZ> ho cat /etc/issueWelcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l).sys@SYBO2SZ> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Production--Author: Robinson--Blog  : http://blog.csdn.net/robinson_0612--查看MBRC的值sys@SYBO2SZ> show parameter read_count;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count        integer     16--查看和read_count相關的隱藏參數     sys@SYBO2SZ> @hidden_paraEnter value for para: read_countKSPPINM                               KSPPSTVL             DESCRIB-----------------------------------   -------------------- ---------------------------------------------_db_file_exec_read_count              48                   multiblock read count for regular clients_db_file_optimizer_read_count         8                    multiblock read count for regular clients_db_file_noncontig_mblock_read_count  11                   number of noncontiguous db blocks to be prefetched_sort_multiblock_read_count           2                    multi-block read count for sortsys@SYBO2SZ> get trace_enable_cur.sql  1* alter session set events '10046 trace name context forever,level 8';sys@ORA11G> @trace_enable_curSession altered.sys@SYBO2SZ> select count(*) from scott.big_table where owner='SYS';  COUNT(*)----------    468066sys@SYBO2SZ> get trace_disable_cur.sql  1* alter session set events '10046 trace name context off';sys@SYBO2SZ> @trace_disable_cur.sqlSession altered.sys@SYBO2SZ> @my_envSPID                SID    SERIAL# USERNAME        PROGRAM------------ ---------- ---------- --------------- ------------------------------------------------24472              1094         30 robin           oracle@SZDB (TNS V1-V3)sys@SYBO2SZ> @get_spec_sess_trace_fileEnter value for input_sid: 1094Enter value for input_serial: 30       SID    SERIAL# SPID         TRACE_FILE---------- ---------- ------------ ----------------------------------------------------------      1094         30 24472        /u02/database/SYBO2SZ/udump/sybo2sz_ora_24472.trc--下面的blocks即為單次讀取時的塊數sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24472.trc |tail WAIT #7: nam='db file scattered read' ela= 1491 file#=5 block#=18363 blocks=16 obj#=52884 tim=1337624821997106WAIT #7: nam='db file scattered read' ela= 5148 file#=5 block#=18443 blocks=2 obj#=52884 tim=1337624822005413WAIT #7: nam='db file scattered read' ela= 32363 file#=5 block#=18571 blocks=16 obj#=52884 tim=1337624822041788WAIT #7: nam='db file scattered read' ela= 1930 file#=5 block#=18587 blocks=16 obj#=52884 tim=1337624822044227WAIT #7: nam='db file scattered read' ela= 345 file#=5 block#=18603 blocks=16 obj#=52884 tim=1337624822045165WAIT #7: nam='db file scattered read' ela= 1712 file#=5 block#=18619 blocks=16 obj#=52884 tim=1337624822047555WAIT #7: nam='db file scattered read' ela= 58 file#=5 block#=18635 blocks=2 obj#=52884 tim=1337624822048219b、MBRC大於128的情形sys@SYBO2SZ> alter system set db_file_multiblock_read_count=256;System altered.sys@SYBO2SZ> show parameter read_count;   --->從這個查詢可知，大於128的情形並沒有生效NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count        integer     128--對於MBRC為128的情形示範步驟同上，下面僅僅列出最終結果--注意在使用不同的MBRC在系統層級測試前應將buffer cache清空(alter system flush buffer_cache)sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24750.trc |tailWAIT #7: nam='db file scattered read' ela= 2070 file#=5 block#=20875 blocks=126 obj#=53208 tim=1337625626875769WAIT #7: nam='db file scattered read' ela= 1991 file#=5 block#=21003 blocks=126 obj#=53208 tim=1337625626880199WAIT #7: nam='db file scattered read' ela= 1970 file#=5 block#=21131 blocks=126 obj#=53208 tim=1337625626884412WAIT #7: nam='db file scattered read' ela= 2078 file#=5 block#=21259 blocks=126 obj#=53208 tim=1337625626888846WAIT #7: nam='db file scattered read' ela= 2035 file#=5 block#=21387 blocks=126 obj#=53208 tim=1337625626893039WAIT #7: nam='db file scattered read' ela= 2040 file#=5 block#=21515 blocks=126 obj#=53208 tim=1337625626897021WAIT #7: nam='db file scattered read' ela= 1048 file#=5 block#=21643 blocks=66 obj#=53208 tim=1337625626900379c、MBRC為預設值的情形sys@SYBO2SZ> alter system reset db_file_multiblock_read_count scope=spfile sid='*';System altered.sys@SYBO2SZ> show parameter read_count;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count        integer     128sys@SYBO2SZ> startup force;   --->reset mbrc後需要重新啟動dbsys@SYBO2SZ> show parameter read_countNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_file_multiblock_read_count        integer     44  sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24890.trc | tailWAIT #14: nam='db file scattered read' ela= 682 file#=5 block#=21431 blocks=44 obj#=53208 tim=1337626216154292WAIT #14: nam='db file scattered read' ela= 4497 file#=5 block#=21475 blocks=38 obj#=53208 tim=1337626216163586WAIT #14: nam='db file scattered read' ela= 586 file#=5 block#=21515 blocks=44 obj#=53208 tim=1337626216166611WAIT #14: nam='db file scattered read' ela= 691 file#=5 block#=21559 blocks=44 obj#=53208 tim=1337626216170137WAIT #14: nam='db file scattered read' ela= 4860 file#=5 block#=21603 blocks=38 obj#=53208 tim=1337626216177698WAIT #14: nam='db file scattered read' ela= 592 file#=5 block#=21643 blocks=44 obj#=53208 tim=1337626216180540WAIT #14: nam='db file scattered read' ela= 292 file#=5 block#=21687 blocks=22 obj#=53208 tim=1337626216183493

6、示範不同MBRC所耗用的時間(session層級)

--下面的PL/SQL代碼用於測試不同的MBRC時所讀的塊數以及耗用的時間scott@SYBO2SZ> get assess_mbrc.sql  1  DECLARE  2    l_count PLS_INTEGER;  3    l_time NUMBER(10,1);  4    l_starting_time PLS_INTEGER;  5    l_ending_time PLS_INTEGER;  6    l_blocks PLS_INTEGER;  7    l_starting_blocks PLS_INTEGER;  8    l_ending_blocks PLS_INTEGER;  9    l_dbfmbrc PLS_INTEGER; 10  BEGIN 11    dbms_output.put_line('dbfmbrc    blocks    seconds'); 12    dbms_output.put_line('-------    ------    -------'); 13    FOR i IN 1..32 14    LOOP 15      l_dbfmbrc := i * 4; 16      EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = '||l_dbfmbrc; 17      EXECUTE IMMEDIATE 'ALTER SYSTEM SET EVENTS ''IMMEDIATE TRACE NAME FLUSH_CACHE'''; 18      SELECT value INTO l_starting_blocks 19      FROM v$mystat ms JOIN v$statname USING (statistic#) 20      WHERE name = 'physical reads'; 21      l_starting_time := dbms_utility.get_time(); 22      SELECT count(*) INTO l_count FROM big_table; 23      l_ending_time := dbms_utility.get_time(); 24      SELECT value INTO l_ending_blocks 25      FROM v$mystat ms JOIN v$statname USING (statistic#) 26      WHERE name = 'physical reads'; 27      l_time := l_ending_time-l_starting_time; 28      l_blocks := l_ending_blocks-l_starting_blocks; 29      dbms_output.put_line(l_dbfmbrc||'         '||l_blocks||'         '||to_char(l_time)||'hsec'); 30    END LOOP; 31* END; 32  /dbfmbrc    blocks    seconds-------    ------    -------4         2119         18hsec8         2093         15hsec12         2093         7hsec16         2093         8hsec20         2093         9hsec24         2093         8hsec28         2093         9hsec32         2093         8hsec36         2093         8hsec40         2093         8hsec44         2093         9hsec48         2093         9hsec52         2093         9hsec56         2093         8hsec60         2093         9hsec64         2093         8hsec68         2093         9hsec72         2093         8hsec76         2093         9hsec80         2093         9hsec84         2093         9hsec88         2093         8hsec92         2093         9hsec96         2093         9hsec100         2093         9hsec104         2093         8hsec108         2093         9hsec112         2093         8hsec116         2093         9hsec120         2093         8hsec124         2093         9hsec128         2093         9hsecPL/SQL procedure successfully completed.--從上面的結果可以看出，當設定MBRC為16時，所讀取的塊數以及所耗用的時間基本上不再受到MBRC的影響。--由此可知，單純增加MBRC的值也不能夠徹底地解決db file scattered read。--到底如何設定多少，還是按照Oracle的建議保留預設值。再在預設值的基礎之上作相應調整。--Reference:[841444.1] [473740.1] [1398860.1] [291239.1]

 

更多參考

DML Error Logging 特性 

PL/SQL --> 遊標

PL/SQL --> 隱式遊標(SQL%FOUND)

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫