Oracle db_file_mulitblock_read_count参数

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级别)

[sql] 

a、MBRC为16的情形  

--演示环境  

sys@SYBO2SZ> ho cat /etc/issue  

  

Welcome 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  

  

--查看MBRC的值  

sys@SYBO2SZ> show parameter read_count;  

  

NAME                                 TYPE        VALUE  

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

db_file_multiblock_read_count        integer     16  

  

--查看和read_count相关的隐藏参数       

sys@SYBO2SZ> @hidden_para  

Enter value for para: read_count  

  

KSPPINM                               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 sort  

  

sys@SYBO2SZ> get trace_enable_cur.sql  

  1* alter session set events '