CBO学习笔记5 --part1

selectivity

selectivity是Oracle的CBO中比较重要的一个概念。和selectivity经常一起提到的一个概念是cardinality，其实这两个概念的本质上是一样的。selectivity是指一个SQL操作的得出结果集占原来结果集的百分比，而cardinality就是指一个SQL操作的得出结果集的行数，CBO是通过selectivity来计算cardinality的，也就是说cardinality=selectivity*原结果集行数。

特别提醒一下，我们本节做的讨论都是不考虑Histograms的情况的。

我们知道当Oracle要访问一个表的时候，可以做全表扫描，也可以通过B-tree索引进行访问。CBO一般会在需要访问的数据占整个表的总数据量比较少的时候倾向使用索引，那么Oracle是通过什么来判断需要访问的数据占总数据量的多少呢，就是通过selectivity。
selectivity的另外一个主要的用途就是当SQL里面需要做多个join的时候，CBO通过比较不同的join的selectivity来决定先做哪个join，后做哪个join。

下面我们就来看一看CBO是如何来计算selectivity的。
加入我们有1200个听众，从统计的角度，这些听众里面会有多少人是12月份出生的呢？
按照我们正常的想法：
1. 一共有12个不同的月份。
2. 人们的出生日期很可能是在一年12个月份当中平均分配的。
3. 因此任何一个月份出生的人占所有听众的比率是1/12。
4. 我们一共有1200个听众。
5. 因此12月份出生的人应该是 1200/12=100人

CBO做的事情其实是和我们上面的推断非常类似的：

实验1.
我们先建立这样一个audience 表并收集统计信息：
create table audience as
select
trunc(dbms_random.value(1,13)) month_no
from
all_objects
where
rownum <= 1200
;

Table created.

begin
dbms_stats.gather_table_stats(
user,
'audience',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 1'
);
end;
/

PL/SQL procedure successfully completed.

通过查询user_tab_col_statistics和user_tab_histograms这两张字典视图，可以看到Oracle对这张表的统计情况：

select
column_name,
num_distinct,
num_nulls,
density,
value_to_number(low_value) low,
value_to_number(high_value) high
from
user_tab_columns
where table_name = 'AUDIENCE'
and column_name = 'MONTH_NO'
;

COLUMN_NAM NUM_DISTINCT NUM_NULLS DENSITY LOW HIGH
---------- ------------ ---------- ---------- ---------- ----------
MONTH_NO 12 0 .083333333 1 12

我们看到month_no这个列上的不同的值为12个，没有null值， 密度为0.83（即1/12），最大值为12，最小值为1。

select
column_name, endpoint_number, endpoint_value
from
user_tab_histograms
where
table_name = 'AUDIENCE'
order by
column_name, endpoint_number
;

COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------
MONTH_NO 0 1
MONTH_NO 1 12

从USER_TAB_HISTOGRAMS我们只能看到一个最大值和一个最小值，说明Oracle认为1~12之间的数据是均匀分配的。所以如果我们查询month_no为12的行数，Oracle会认为month_no=12的行占整个表的1/12，所以这个查询的cardinality将会等于(1/12) * 1200 = 100行， 从下面的查询我们也可以看到这一点：

set autotrace traceonly explain

select count(*)
from audience
where month_no = 12
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=100 Bytes=300)

 

实验2. 如果我们修改一下我们的SQL，把等于的条件改成in：
select count(*)
from audience
where month_no in (6,7,8)
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=300 Bytes=900)

这一次CBO对cardinality的计算结果是300，这应该也是正确的。但是如果我们进一步修改我们的SQL的条件，把in (6,7,8) 改成in (6, 16)：
select count(*)
from audience
where month_no in (6,16)
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'AUDIENCE' (Cost=2 Card=200 Bytes=600)

问题出现了，因为我们知道我们的月份里面是没有16的，其实Oracle本身通过数据字典也是知道这一点的，但在计算selectivity和cardinality的时候并没有把这一点考虑进去。这其实是Oracle9i的CBO的一个问题。如果我们进一步做一些类似的实验，我们发现这个问题相当的普遍：

条件	cardinality
where month_no = 25	100	Incorrect
where month_no in (4, 4)	100	Correct
where month_no in (3, 25)	200	Incorrect
where month_no in (3, 25, 26)	300	Incorrect
where month_no in (3, 25, 25, 26)	300	Incorrect
where month_no in (3, 25, null)	300	Incorrect
where month_no in (:b1, :b2, :b3)	300	Incorrect

条件 cardinality
where month_no = 25 100 Incorrect
where month_no in (4, 4) 100 Correct
where month_no in (3, 25) 200 Incorrect
where month_no in (3, 25, 26) 300 Incorrect
where month_no in (3, 25, 25, 26) 300 Incorrect
where month_no in (3, 25, null) 300 Incorrect
where month_no in (:b1, :b2, :b3) 300 Incorrect

上面的几个测试唯一结果正确的就是在in里面出现重复值的时候，但是当出现不可能的值或null值的时候CBO对selectivity就有问题了。这是9i的CBO的bug，在10g里已经改善了，在10g里的测试结果是这样的：

条件	Cardinality(10.1.0.2)	Cardinality(10.1.0.4)
month_no=13	100	91
month_no=15	100	73
month_no in (13,15)	200	164
month_no in (16,18)	200	109

 

条件 Cardinality(10.1.0.2) Cardinality(10.1.0.4)
month_no=13 100 91
month_no=15 100 73
month_no in (13,15) 200 164
month_no in (16,18) 200 109

从10.1.0.4开始，当条件里面的值不在表的取值范围的时候（即条件里给出的值超出过小于统计数据的max_value和min_value的时候），CBO计算的Cardinality会随着给出的值离表的取值范围越远而逐渐减少。这无疑是一个进步，但是有时候也会有一些问题：如果一张表的一个列就是日期，如果我们长期忘记收集表的统计数据，随着新的日期
的增加，当我们对新日期进行查询的时候，我们会在10.1.0.4及以后的版本里得到比较小的Cardinality，而在9i或10.1.0.4以前，我们反倒是可以误打误撞的得到一个正确的“Cardinality”。

10.1.0.4里的这个改进可以用下图来描述：