在oracle 10.2版本以上，oracle建议在参数文件中不设置db_file_multiblock_ read_count值或设置为0，同时又引进了两个隐含：_db_file_optimizer_read_ count 和 _db_ file_ exec_read_count，以下是对该隐含参数进行分析：

一、机制分析
    
       我们知道db_file_multiblock_read_count 在CBO在计算表扫描中是一个比较重要因子, 其计算公式如下：
    
      cost=1+HWM/k

传统方法存在问题：

1、如果提高db_file_multiblock_read_count值，对应K相应提高，从而cost值降低，导致优化器选择全表扫描的机率增加，但很多时候，采用索引方式有更好的执行计划；

 2、如果降低db_file_multiblock_read_count值，cost计算值增加，提高了执行计划中采用索引的机率，但如果执行计划选择了全表扫描，因为db_file_multiblock_ read_count值太小，导致执行时候I/O效率太低。

  基于上述分析，10.2版本以上引进了_db_file_optimizer_read_ count 和_db_ file_ exec_read_ count解决了所遇到瓶颈：

1、_db_file_optimizer_read_ count 该值为较低值，缺省值为8，用于计算成本，增加执行计划使用索引的机率；

2、_db_ file_ exec_read_count值较大，在执行过程中一旦选择全表扫描时候，提高I/O吞吐能力

    如果在10.2版本以上设置db_file_multiblock_read_count参数，将导致隐含参数失效。具体将在试验中进行验证。

二、实验测试

1、创建测试表
SQL>execute dbms_random.seed(0);

SQL>create table t1
    pctfree 99
    pctused 1
    as
    with generator as (
      select --+ materialize
        rownum id
      from all_objects
      where rownum <=3000
     )
    select /*+ ordered use_nl(v2) */
      rownum id,
      trunc(100*dbms_random.normal) val,
      rpad('x', 100) padding
    from
       generator v1,
       generator v2
    where rownum <=10000;

2、统计分析

SQL>begin
       dbms_stats.gather_table_stats(
          user,
          't1',
          cascade =>true,
          estimate_percent => null,
          method_opt => 'for all columns size 1'
          );
      end;
      /
3、在参数文件中不设置db_file_multiblock_read_count, 跟踪trace文件

SQL>alter system reset db_file_multiblock_read_count scope=spfile sid='*';
SQL>重启数据库

a)查看隐含参数

SQL>select a.ksppinm, b.ksppstvl, b.ksppstdf
           from x$ksppi a, x$ksppsv b
           where a.indx = b.indx
          and a.ksppinm like '%db_file%'
 输出结果：
db_file_multiblock_read_count=10;
_db_file_exec_read_count=10;
_db_file_optimizer_read_count=8;

b)使用10053和10046进行跟踪

SQL>alter session set events '10053 trace name context forever, level 2';
SQL>alter session set events '10046 trace name context forever, level 12';
SQL>select max(val) from t1;
SQL>alter session set events '10046 trace name context off'
SQL>alter session set events '10053 trace name context off';

输出结果：

*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 792 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  10000  AvgRowLen:  108.00 
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  2717.67  Resp: 2717.67  Degree: 0
      Cost_io: 2710.00  Cost_cpu: 72914400
      Resp_io: 2710.00  Resp_cpu: 72914400
  Best:: AccessPath: TableScan
         Cost: 2717.67  Degree: 1  Resp: 2717.67  Card: 10000.00  Bytes: 0
 ......................
'db file scattered read' ela= 47 file#=8 block#=42743 blocks=10 obj#=200578 tim=17446052034238
........................

c)分析 跟踪文件

优化器进行成本计算时候，使用_db_file_optimizer_read_count=8

cost_io=ceiling(#MRDs * MREADTIM/SREADTIM) +1
#MRDs=#Blks/mbrc=10000/8=1250
MREADTIM=IOSEEKTIM + mbrc*db_block_size/IOTFRSPEED=10+8*8/4=26
SREADTIM=IOSEEKTIM + db_block_size/IOTFRSPEED=10+8/4=12

==> cost_io=ceiling(1250*26/12+1)=2710
 
而在实际进行多数据块读的时候，db_file_multiblock_read_count =_db_file_ exec_ read_count=10;

'db file scattered read' ela= 47 file#=8 block#=42743 blocks=10 obj#=200578 tim=17446052034238

4、参数文件设置db_file_multiblock_read_count=4

SQL>alter system set db_file_multiblock_read_count=4 scope=spfile;
SQL>重启数据库

查看隐含参数
SQL>select a.ksppinm, b.ksppstvl, b.ksppstdf
           from x$ksppi a, x$ksppsv b
           where a.indx = b.indx
          and a.ksppinm like '%db_file%'
 输出结果：
db_file_multiblock_read_count=4;
_db_file_exec_read_count=4;
_db_file_optimizer_read_count=4;

一旦设置该参数，将发现其值将变成相等

三、小结

      首先，在10.2版本以上不建议参数文件设置db_file_multiblock_read_count值，其次， 经常在论坛中看到别人讨论optimizer_index_cost_adj的设置问题，以降低索引成本计算值，从而提高优化器选择索引的几率，10.2建议不使用该参数值，我们完全可以设置_db_file_optimizer_read_count为较小值，设置_db_file_exec_ read_count较大值，执行计划将能会优先考虑采用索引，但如果优化器选择全表扫描，也能最大提高IO性能。 Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE