Cbo评估索引访问成本计算公式初探

【环境描述】

 

8k块大小的非assm表空间;
表空间统一区大小为1M(128个块);
禁用cpu_costing。

 

begin
  dbms_stats.delete_system_stats;
  execute immediate 'alter session set "_optimizer_cost_model"=io';
end;
/

 

【测试表建立】

 

drop table t1;

create table t1 TABLESPACE dict_tbs
nologging
as
select trunc(dbms_random.value(0, 25)) n1,
       rpad('x', 40) ind_pad,
       trunc(dbms_random.value(0, 20)) n2,
       lpad(rownum, 10, '0') small_vc,
       rpad('x', 200) padding
from   all_objects
where  rownum <= 10000;

 

create index t1_i1 on t1(n1, ind_pad, n2)
nologging
pctfree 91
;

 

--收集统计信息
begin
  dbms_stats.gather_table_stats(user,
                                't1',
                                cascade          => true,
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1');
end;
/

 

--查看统计信息
select table_name,
       blocks,
       num_rows
from   user_tab_statistics
where  table_name = 'T1';
--T1 371 10000

 

select num_rows,
       distinct_keys,
       blevel,
       leaf_blocks,
       clustering_factor,
       avg_leaf_blocks_per_key,
       avg_data_blocks_per_key
from   user_ind_statistics
where  table_name = 'T1'
and    index_name = 'T1_I1';
--10000 500 2 1111 9728 2 19

 

select column_name,
       num_nulls,
       num_distinct,
       density,
       low_value,
       high_value
from   user_tab_columns
where  table_name = 'T1'
and    column_name in ('N1', 'N2', 'IND_PAD')
order  by column_name;
/*
CCOLUMN_NAME                     NUM_NULLS NUM_DISTINCT    DENSITY LOW_VALUE                                                HIGH_VALUE
------------------------------ ---------- ------------ ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
IND_PAD                                 0            1          1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020
N1                                      0           25        .04 80                                                       C119
N2                                      0           20        .05 80                                                       C114
*/

 

 

【索引计算公式】

 

首先我们给出索引io成本计算公式如下：

blevel +
CEIL(leaf_blocks * ix_sel) +
CEIL(cluster_factor * tab_sel)

其中blevel为B*tree索引高度;
ix_sel为【有效索引选择率】，它依赖于索引访问谓词;
tab_sel为【有效表选择率】，它依赖于表的访问谓词。

【公式验证】

 

DELETE FROM plan_table;
EXPLAIN PLAN SET statement_id='ix_formula' FOR
select small_vc
from   t1
where  n1 = 2
and    ind_pad = rpad('x', 40)
and    n2 = 3;

 

SELECT *
FROM   TABLE(dbms_xplan.display(NULL, NULL, 'advanced', NULL));

 

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    20 |  1160 |    25 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    20 |  1160 |    25 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    20 |       |     5 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("N1"=2 AND "IND_PAD"='x                                  
                  ' AND "N2"=3)
                 
SELECT COST,io_cost FROM plan_table WHERE ID=0;
--25 25
                 
  在上面的执行计划中我们看access部分（即访问谓词），它表明where条件中的三个
限定条件都对索引的选择性有效，所以我们可以放心的使用前面介绍过的选择率计算
公式来计算有效索引选择率：

 

Se(n1) = ( 1 / num_distinct )*( (num_rows - num_nulls) / num_rows) = 1/25 * 1 = 0.04
Se(ind_pad) = 1
Se(n2) = 1 / 20 = 0.05

 

所以联合谓词的选择率为：
SELECT 0.04*1*0.05 ix_sel FROM dual;
--0.002

 

  而且这里的ix_sel即为tab_sel，所以将这些值代入成本计算公式，计算出成本值为：
 
SELECT 2 + ceil(1111 * 0.002) + ceil(9747 * 0.002) FROM dual;
--25

 

【访问谓词与过滤谓词】

 

DELETE FROM plan_table;
EXPLAIN PLAN FOR
SELECT --+index(t1) no_index_ss(t1)
 small_vc
from   t1
WHERE  n1 BETWEEN 1 AND 3
AND    ind_pad = RPAD('x', 40)
AND    n2 = 2;

 

查看执行计划：

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    82 |  4756 |   264 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    82 |  4756 |   264 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    82 |       |   184 |
---------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("N1">=1 AND "IND_PAD"='x                                 
                   ' AND "N2"=2 AND "N1"<=3)

       filter("IND_PAD"='x                                       ' AND
              "N2"=2)
             
SELECT io_cost,COST FROM plan_table WHERE ID=0;
--264 264

 

  我们看到，在2操作（INDEX RANGE SCAN）中，同时出现了access和filter谓词，而
在这里就要从access谓词中去除filter谓词，所以有效的索引访问谓词是n1>=1 AND n1<=3，
即n1 BETWEEN 1 AND 3，所以这里的ix_sel为Se(n1)即：

 

SELECT (3 - 1) / (24) + 2 / 25 FROM dual;
--0.163333333333333

 

但是这里的有效表访问谓词依然是三个条件的联合计算值，即:

SELECT 0.163333333333333 * 1 * 0.05 FROM dual;
--0.00816666666666665

 

所以最终将以上值代入成本计算公式得：

SELECT 2 + CEIL(1111 * 0.163333333333333) +
       CEIL(9728 * 0.00816666666666665)
FROM   dual;
--264

  【总结】

 

  当cbo打算（或你强制它）使用索引做为访问方式时，cbo计算成本开销的关键的两个
参数分别为ix_sel和tab_sel，即有效索引选择率和有效表选择率，而这两个选择率
可以很容易的从执行计划的访问谓词中推导得出。

 

  关于cbo评估索引访问开销的进一步深入将在以后给出。