优化器计算如何索引访问代价

                                      优化器计算如何索引访问代价

        Oracle优化器(以下简称CBO)在评估使用全表的访问方式，还是索引的访问方或是不同的索引访问方式，主
要取决于:统计信息中的cardinality评估，索引的cluster factor,索引叶子节点数(lf)和表的总行数．

 
      假设我们不考虑cpu成本(参数_optimizer_cost_mode设置）．使用索引访问路径的IO成本主要分为两部分：访问
索引IO成本和通过索引进行表访问IO成本.

      索引访问IO成本通过公式为: cardinality/(rows/lb)+blevel.表访访问IO成本通过公式  
cardinality*(cluster factor/rows)

我们创建一个测试表t2(oracle版本为12.1.0.2),并且加载数据

DONGDONGTANG> create table t2 select * from dba_objects

DONGDONGTANG> select count(*) from t2;

  COUNT(*)
----------
   2925216

DONGDONGTANG> create index ix_t2_a on t2(object_id);

Index created.

DONGDONGTANG> exec dbms_stats.gather_table_stats('SYS','T2');

PL/SQL procedure successfully completed.

打开10053跟踪事件，level级别8

DONGDONGTANG> select /*17*/ count(object_name) from t2 where object_id between 100 and 1001;

COUNT(OBJECT_NAME)
------------------
      28800

查看跟踪文件内容

Table Stats::
  Table: T2  Alias: T2
  #Rows: 2925216  SSZ: 0  LGR: 0  #Blks:  48907  AvgRowLen:  115.00  NEB: 0  ChainCnt:  0.00  SPC: 0  RFL: 0  RNF: 0  CBK: 0 

CHR: 0  KQDFLG: 1
  #IMCUs: 0  IMCRowCnt: 0  IMCJournalRowCnt: 0  #IMCBlocks: 0  IMCQuotient: 0.000000

 

Index Stats::
  Index: IX_T2_A  Col#: 4
  LVLS: 2  #LB: 6603  #DK: 92496  LB/K: 1.00  DB/K: 32.00  CLUF: 2980490.00  NRW: 2980490.00 SSZ: 0.00 LGR: 0.00 CBK: 0.00

GQL: 0.00 CHR: 0.00 KQDFLG: 1 BSZ: 1
  KKEISFLG: 1

  Table: T2  Alias: T2
    Card: Original: 2925216.000000  Rounded: 28203  Computed: 28203.240783  Non Adjusted: 28203.240783

  Access Path: TableScan
    Cost:  5686.000000  Resp: 5686.000000  Degree: 0
      Cost_io: 5686.000000  Cost_cpu: 0
      Resp_io: 5686.000000  Resp_cpu: 0

  Access Path: index (RangeScan)
    Index: IX_T2_A
    resc_io: 28803.000000  resc_cpu: 0
    ix_sel: 0.009641  ix_sel_with_filters: 0.009641
    Cost:

28803.000000  Resp: 28803.000000  Degree: 1
 

 Best:: AccessPath: TableScan
         Cost: 5686.000000  Degree: 1  Resp: 5686.000000  Card: 28203.240783  Bytes: 0.000000

 Card: Original: 2925216.000000  Rounded: 28203  Computed: 28203.240783  Non Adjusted: 28203.240783 这一行表示评估的cardinality值，使用computed的值,

  CLUF: 2980490.00 表示索引的cluster factor,
 
  NRW: 2980490.00  表示其中的rows
 
  #LB: 6603   表示该索引的叶子节(lb)点数

   LVLS: 2   表过索引节点的层级或是高度.

 
RESC 表访访问IO成本通过公式   cardinality*(cluster factor/rows)=28203*(2980490/292521)=28736
     索引访问IO成本通过公式　　　cardinality/(rows/lb)+blevel=28203/(2980490/6603)+2=64

     在本例中索引访问的IO和访问表的IO开销相加之后，基本接近优化器评估的值了.所以在这里我们可以看出精确的统计信息，
对于oracle选择正确的执行计划重要性.相比其他的因素，cardinality的评估可能最为复杂.