10.2版本以上對db_file_multiblock_read_count變化分析

       在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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-630410/，如需轉載，請註明出處，否則將追究法律責任。