CBO学习笔记

cost of b-tree access

这一节我们看看CBO是如何来计算最普通的b-tree索引的访问的cost的。

我们知道B-tree索引的结构是一个树状结构，索引中包括root block，branch block, leaf block，这些结构分别相当与树的根，茎和叶。
当我们使用一个索引的时候，我们首先访问索引的root block，确定需要访问大branch block。
然后选择需要访问的branch blcok，通过访问branch block，我们知道符合我们条件的第一个leaf block，可以叫做start key
我们从start key访问leaf block的列表，知道找到我们需要的最后一个leaf block--->stop key。
访问符合条件的leaf block的时候，并视情况而定要不要访问相应的表。我们知道leaf block上的信息包括被索引的列的值和在表中相应行的rowid，所以如果我们只需要查询被索引的列上的信息，我们就无需访问index对应的表，否则就需要访问表。

我们知道CBO的Cost包括IO的cost和CPU的cost，而Oracle认为每一个single block access都是一个真实的IO，所以如果我们启动CPU的cost，访问索引的Cost就是访问索引的Block个数。

访问索引的cost可以用下面的公式来表示：

cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

第一行的blevel是指为了访问一个leaf block，我们需要访问的block的个数（不包括leaf block本身），Oracle总是维护一个Balanced B-trees，也就是说不管访问哪桓鰈eaf block，我们需要访问的中间block（包括root block和branch block）的个数都是一样多的。这个blevel也常常被称为索引的高度。

第二行是指我们的查询需要访问的leaf block的个数。

第三行是指为了获取我们需要的所有数据，我们需要访问的表的block的个数。

我们来做个实验来验证上面的公式。

1) 建表，建索引并收集统计数据：
create table t1
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 => null,
method_opt => 'for all columns size 1'
);
end;
/

我们查询表和索引的统计信息：
select
table_name,
blocks,
num_rows
from user_tables
where table_name = 'T1'
;

TABLE_NAME BLOCKS NUM_ROWS
------------------------------ ---------- ----------
T1 371 10000

（HWM下有371个block，一共有10000行）

select
num_rows, distinct_keys,
blevel, leaf_blocks, clustering_factor,
avg_leaf_blocks_per_key, avg_data_blocks_per_key
from
user_indexes
where table_name = 'T1'
and index_name = 'T1_I1'
;

NUM_ROWS DISTINCT_KEYS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- ----------------------- -----------------------
10000 500 2 1112 9745 2 19

（索引上一共有10000行，
索引的列上的distinct key是500个，
这个索引的blevel=2，也就是说有一个level 2的root block和多个level 1的branch block，
一共有1112个leaf block，
cluster_factor=9745，
平均的说，索引上每一个distinct key占用2个索引leaf block，
平均的说，索引上每一个distinct key占用19个table block。

这里简单解释一下几个概念：
1，distinct key，
因为我们被索引的列是n1, ind_pad，n2。其中n1有25个不同的值，ind_pad的值总是一样的，n2有20个不同的值，所以索引上distinct key=25*1*20=500。

2，cluster_factor，
这是一个判断表上的数据分布是不是和索引一样有序的一个值，它的取值范围在表的block的个数和表的行数之间，越接近block的个数说明表的数据分布越有序（和索引一样有序），越是接近行数越说明表上的数据分布是混乱的。
可以这样理解cluster_factor，当我们按照索引上的顺序访问表的时候，每当我们需要的数据不在当前block上的时候，我们就要“跳到”其他block上进行访问，如果表上的数据排列和索引是完全一样的话，我们跳的次数等于表的Block的个数，如果是另一个极端，表的数据分布极其的混乱，我们访问每一行数据都要跳一次，那我们最后跳的次数就等于行数。这里跳的次数就是cluster_factor。从这个角度来说，如果我们访问索引之后还有相应的访问表，假设我们访问了X%的索引block，那我们需要访问表的IO次数应该是X% * cluster_factor，取决于X和cluster_factor的大小，X% * cluster_factor有可能比表的所有block的个数还有多（当然这种情况下很有可能CBO已经选择全表扫描了），因此cluster_factor对于计算索引访问的Cost是很重要的，经常可以用来回答类似与“为什么我们索引没有被使用这类问题”。

下面是一个cluster_factor=10的图示：

需要明确的是根据公式
cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
的第三个部分我们知道cluster_factor越小访问索引COST的就越小（当然是在访问完索引后不得不也访问表的情况下），但cluster_factor大小其实不是衡量索引好坏的标准，这个值是因为表本身的数据分布和索引排列数据顺序不符所造成的，即使我们rebuild索引，cluster_factor也不会改变（只要索引的定义没有改变）。

实验1

 

先做个简单的实验：
select
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad('x',40)
and n2 = 3
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=20 Bytes=1160)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=25 Card=20 Bytes=1160)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=5 Card=20)

我们看到索引本身RANGE SCAN的Cost=5，Card=20， 最后的总cost是25，也就是table的访问cost是20，按照我们个公式：

cost = blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
我们算一下：
blevel =2
leaf_blocks=1112
effective index selectivity = (selectivity of n1)*(selectivity of ind_pad)*(selectivity of n2)= (1/25)*1*(1/20)=1/500=0.002
clustering_factor=9745
effective table selectivity在这里其实是和索引的effective index selectivity一样的=(selectivity of n1)*(selectivity of ind_pad)*(selectivity of n2)= (1/25)*1*(1/20)=1/500=0.002

放在一起我们得到
cost = 2+ ceil(1112*0.002) + ceil(9745*0.002)=2+ 3 + 20 = 25。
其中2+3是索引访问本身的cost，20是表的访问cost。

从这个例子里我们发现表本身的cost占绝大多数，这是因为这个索引的clustering_factor很大。很多时候我们都希望重建索引可以大大提高提高索引的效率，但要记住重建索引只能减少leaf_blocks的值（能减少多少取决于索引的具体情况），但实际上有时候真正影响更大的是clustering_factor（当然有时候是leaf_blocks，取决于具体情况）。

实验2

 

我们在条件里面加一个范围查询：

select
/*+ index(t1) */
small_vc
from
t1
where
n1 = 2
and ind_pad = rpad('x',40)
and n2 between 1 and 3
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=93 Card=82 Bytes=4756)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=93 Card=82 Bytes=4756)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=12 Card=82)

首先我们加了一个/*+ index(t1) */强制使用索引，否则这个查询会选择full table scan，因为如果你实验一下或按照我们以前介绍的方法算一下，这个表的full table scan的cost只有58。
我们再算一下cost：

selectivity (n2 between 1 and 3) =
required range / total range + 1/num_distinct + 1/num_distinct =
(3 – 1) / (19 – 0) + 1/20 + 1/20 = 0.205263

Effective index selectivity = 1 * 0.04 * 0.205263 = 0.0082105
Effective table selectivity = 1 * 0.04 * 0.205263 = 0.0082105
Cost = 2 + ceiling(1,112 * 0.0082105) + ceiling(9,745 * 0.00082105) = 12 (index的扫描cost) + 81(table的扫描cost) = 93

实验3

 

我们修改一下上面的SQL：

alter session set "_optimizer_skip_scan_enabled"=false;
select
/*+ index(t1) */
small_vc
from
t1
where
n1 between 1 and 3
and ind_pad = rpad('x',40)
and n2 = 2
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=264 Card=82 Bytes=4756)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=264 Card=82 Bytes=4756)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=184 Card=1633)

我们这里修改_optimizer_skip_scan_enabled为false，这样CBO就会使用Cost更低的Index skip scan。

这次我们发现总的cost相当高，但是按照公式计算：
selectivity (n1 between 1 and 3) =
required range / total range + 1/num_distinct + 1/num_distinct =
(3 – 1) / (24 – 0) + 1/25 + 1/25 = 0.163333

Effective index selectivity= 0.163333*1*0.05=0.0081667
Effective table selectivity = 0.163333*1*0.05=0.0081667
cost= 2 +
ceil(0.0081667 * 1,112)
ceil(0.0081667 * 9745)
= 92

我们的公式结果和explain plan里的显示差很多，这是为什么呢？
原因在于复合索引存储的方式，因为复合索引存储数据的时候首先是按照第一个被索引的列来排序的，也就是说把n1=0的放在，然后是n1=1的放在一起，然后n1=2......
类似于（这里只是大概描述一下概念，不一定准确）：
第一组：
n1=0
{ind_pad='x ' (n2=0, n2=1,n2=2....n2=19)}

第二组：
n1=1
{ind_pad='x ' (n2=0, n2=1,n2=2....n2=19)}

.....

第二十五组：
n1=24
{ind_pad='x ' (n2=0, n2=1,n2=2....n2=19)}

所以当我们第一个条件是n1 between 1 and 3的时候，如果没有开启从oracle9i里面开始引进的index skip scan，其实CBO是很郁闷的，因为他要扫描很多block才能获得n1 between 1 and 3的结果，实际上在这种情况下，CBO忽略了后面的ind_pad和n2的查询条件，而是查询了所有n1 between 1 and 3的leaf block再从中选出符合ind_pad和n2的查询条件的leaf block，所以这里
Effective index selectivity = index selectivity of n1 = 0.163333，但是Effective table selectivity还是= 0.163333*1*0.05=0.0081667，这样我们再次计算：

cost= 2 +
ceil(0.163333 * 1,112)
ceil(0.0081667 * 9745)
= 2 + 182 + 80 = 264

在Oracle9i的plan_table里面引入了两个新的列：
SQL> desc plan_table
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(255)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER(38)
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER(38)
PARENT_ID NUMBER(38)
POSITION NUMBER(38)
COST NUMBER(38)
CARDINALITY NUMBER(38)
BYTES NUMBER(38)
OTHER_TAG VARCHAR2(255)
PARTITION_START VARCHAR2(255)
PARTITION_STOP VARCHAR2(255)
PARTITION_ID NUMBER(38)
OTHER LONG
DISTRIBUTION VARCHAR2(30)
CPU_COST NUMBER(38)
IO_COST NUMBER(38)
TEMP_SPACE NUMBER(38)
ACCESS_PREDICATES VARCHAR2(4000)
FILTER_PREDICATES VARCHAR2(4000)

这里access_predicates列出访问索引是被使用了的条件，filter_predicates列出被忽略了的条件。

实验4

 

我们知道8i开始有一个和索引有关的参数：OPTIMIZER_INDEX_CACHING，官方关于这个参数的解释是：
OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of
that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.

也就是说，这个参数仅仅影响nested loops和in list中的index的使用，我们在其他的部分介绍nested loops，现在现看看对In-list的影响：

默认情况optimizer_index_caching = 0

select
/*+ index(t1) */
small_vc
from
t1
where n1 = 5
and ind_pad = rpad('x',40)
and n2 in (1,6,18);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=68 Card=60 Bytes=3480)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=68 Card=60 Bytes=3480)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=9 Card=60)

alter session set optimizer_index_caching = 25;

select
/*+ index(t1) */
small_vc
from
t1
where n1 = 5
and ind_pad = rpad('x',40)
and n2 in (1,6,18)
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=65 Card=60 Bytes=3480)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=65 Card=60 Bytes=3480)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=6 Card=60)

alter session set optimizer_index_caching = 50;

select
/*+ index(t1) */
small_vc
from
t1
where n1 = 5
and ind_pad = rpad('x',40)
and n2 in (1,6,18)
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=63 Card=60 Bytes=3480)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=63 Card=60 Bytes=3480)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=4 Card=60)

 

alter session set optimizer_index_caching = 75;

select
/*+ index(t1) */
small_vc
from
t1
where n1 = 5
and ind_pad = rpad('x',40)
and n2 in (1,6,18)
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=61 Card=60 Bytes=3480)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=61 Card=60 Bytes=3480)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=2 Card=60)

alter session set optimizer_index_caching = 100;

select
/*+ index(t1) */
small_vc
from
t1
where n1 = 5
and ind_pad = rpad('x',40)
and n2 in (1,6,18)
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=60 Bytes=3480)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=59 Card=60 Bytes=3480)
3 2 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE)

不是很清楚具体是怎么计算了，看上来好像是只影响Index部分的cost，比如原来的cost是9，当设置optimizer_index_caching =75之后，那么新的cost= trunc(9*0.25)=2。

8i引入的和index有关的另一个重要的参数是optimizer_index_cost_adj，我们知道在8i里面CBO是不知道单块读和多块读的区别的，而这个参数就是为了弥补这个问题，默认值100是说单块读和多块读的cost一样，当我们调小这个参数值以后，CBO认为单块读比多块读的cost小，否则如果调大，CBO会认为单块读比多块读cost大。optimizer_index_cost_adj的问题在于它并不是影响CBO对多块读的cost，而是影响CBO对单块读的cost，这其实是个有问题的逻辑，因为合理的思维是应该调整多块读的cost，所有有的时候可能会导致CBO本来选择了一个较好的索引，但调整了optimizer_index_cost_adj后会选择一个较差的索引。从这个角度来说9i的system statistics就是调整多块读的cost，所以system statistics是更加合理的，而且system statistics是可以在真实环境中收集信息的，所以也比我们估计optimizer_index_cost_adj来的合理。