cbo对于快速全索引扫描io成本评估法则

【环境】

 

  SELECT * FROM v$version;
  /*
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  PL/SQL Release 11.2.0.1.0 - Production
  CORE  11.2.0.1.0  Production
  TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
  NLSRTL Version 11.2.0.1.0 - Production
  */

  ALTER SESSION SET db_file_multiblock_read_count=8;
  ALTER SESSION SET optimizer_mode=ALL_ROWS;

 

【实验表建立】

 

  DROP TABLE indx_scan PURGE;

  CREATE TABLE indx_scan(ID,des)
  TABLESPACE dict_tbs  --非assm管理的表空间
  PCTFREE 99
  PCTUSED 1
    AS
  SELECT LEVEL,dbms_random.string('p',80) FROM dual CONNECT BY LEVEL<=10000;

  --收集统计信息
  BEGIN
    dbms_stats.gather_table_stats(ownname          => USER,
                                  tabname          => 'INDX_SCAN',
                                  estimate_percent => 100,
                                  method_opt       => 'for all columns size 1');
  END;
  /

  --创建索引
  CREATE INDEX indx_scan_idx ON indx_scan(ID) COMPUTE STATISTICS TABLESPACE dict_tbs;

 

  --查看索引信息
  SELECT uis.blevel,
         uis.LEAF_BLOCKS,
         uis.DISTINCT_KEYS,
         uis.AVG_LEAF_BLOCKS_PER_KEY,
         uis.AVG_DATA_BLOCKS_PER_KEY,
         uis.CLUSTERING_FACTOR
  FROM   user_ind_statistics uis
  WHERE  uis.INDEX_NAME = 'INDX_SCAN_IDX';
  --1  21  10000  1  1  10000

 

  SELECT tablespace_name,us.blocks
  FROM   user_segments us
  WHERE  us.segment_name = 'INDX_SCAN_IDX';
  --DICT_TBS  128

 

【io成本评估】

 

  BEGIN
    dbms_stats.delete_system_stats;
  END;
  /

 

  DELETE FROM plan_table;
  EXPLAIN PLAN FOR SELECT /*+no_cpu_costing*/ID FROM indx_scan WHERE ID IS NOT NULL;
  SELECT io_cost FROM plan_table WHERE ID=0;
  --5

 

  SELECT round(21 / (POWER(8, 0.6581) * 1.6765) ) COST FROM dual;
  --3

 

 

【设置blevel】

 

  BEGIN
    dbms_stats.set_index_stats(ownname  => USER,
                               indname  => 'INDX_SCAN_IDX',
                               indlevel => 6);
  END;
  /

  再次评估成本值依旧为5，看来快速全索引扫描的io成本与索引高度无关。

 

 

【设置叶块数量】

 

  BEGIN
    dbms_stats.set_index_stats(ownname  => USER,
                               indname  => 'INDX_SCAN_IDX',
                               numlblks => 200);
  END;
  /

  再次评估成本值为32，得知快速全索引扫描的io成本与叶块数量有关。

 

  SELECT round(200 / (POWER(8, 0.6581) * 1.6765) ) COST FROM dual;
  --30

 

 

【结论】

 

    快速全索引扫描的io成本与表扫描成本基本一致，只是表扫描成本会
  在原来基础上加1（由于_table_scan_cost_plus_one参数的原因），而
  快速索全索引扫描会加2，这个2的由来不得而知。