验证《cost base fundamentals》第六章节selectivity issues中deadly defaults对优化器的影响。
deadly defaults指致命的默认值，如order表中的date的default值设置为"9999-12-31"，造成数据人为的严重不均衡。
测试平台：
OS：Red Hat Enterprise Linux Server release 5.3 (Tikanga)
DB：Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

创建表t9,id列生成数据1，2……2000000，左边以‘0’补齐18位。
建立索引t9。
create table t9 (id char(18),name varchar2(100));
create sequence t1_seq;
begin
for i in 1..2000000 loop
insert into t9
select lpad(to_char(t1_seq.nextval),18,'0'),to_char(t1_seq.nextval)
from dual;
end loop;
end;
commit;

create index t9_id on t9(id);

一、数据分布均匀

收集统计信息。

begin
dbms_stats.gather_schema_stats(ownname => 'MEISHD',estimate_percent => 100);
end;

统计信息如下：
user_tab_col_statistics.id.low_value='000000000000000001'
user_tab_col_statistics.id.high_value='000000000002000000'
user_tab_col_statistics.id.num_distinct=2000000
user_indexes.t9_id.blevel=2
user_indexes.t9_id.leaf_blocks=8369
user_indexes.t9_id.clustering_factor=8683

计算IO_COST为88

index_selectivity = required range / total range + 1/num_distinct + 1/num_distinct
= (70000-60000)/(2000000-1)+2/2000000
= 0.005001

table_selectivity = index_selectivity = 0.005001

cost=blevel + ceiling(leaf_blocks * index_selectivity) + ceiling(clustering_factor * table_selectivity)
=2+ceiling(8369*0.005001)+ceiling(8683*0.005001)
=2+ceiling（41.85）+ceiling(43.42)
=2+42+44
=88

确认IO_COST为88，总COST为88，CPU_COST为0.46。
这里有个疑问，为什么CPU_COST没有统计进去，不知道是因为太小而忽律不计，还是因为disable CPU_COST了（我已经收集了系统信息，应该已经enable CPU_COST了）。

SQL> select * from t9 where id between '000000000000060000' and '000000000000070000';

Execution Plan
----------------------------------------------------------
Plan hash value: 3577433401

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10002 | 253K| 88 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T9 | 10002 | 253K| 88 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T9_ID | 10002 | | 44 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">='000000000000060000' AND "ID"<='000000000000070000')

SQL>


SQL> select a.OPTIONS,a.OBJECT_NAME,a.ID,a.COST,a.CPU_COST,a.IO_COST from v$sql_plan a where plan_hash_value='3577433401';

OPTIONS OBJECT_NAME ID COST CPU_COST IO_COST
-------------------- --------------- ---------- ---------- ---------- ----------
0 88
BY INDEX ROWID T9 1 88 4327797 88
RANGE SCAN T9_ID 2 44 2313943 44

SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN' and pname in ('CPUSPEED','SREADTIM');

PNAME PVAL1
------------------------------ ----------
CPUSPEED 1468
SREADTIM 6.39


CPU_COST=(v$sql_plan.CPU_COST/CPUSPEED)/SREADTIM
=(4327797/1486*1000000)/(6.39/1000)
=0.46


二、数据分布不均匀

如将order表中的date的default值设置为"9999-12-31"，这里通过将id修改模拟。

update t9 set id='100000000000000000'where id='000000000002000000';
commit;

收集统计信息：
begin
dbms_stats.gather_schema_stats(ownname => 'MEISHD',estimate_percent => 100);
end;

统计信息如下：
user_tab_col_statistics.id.low_value='000000000000000001'
user_tab_col_statistics.id.high_value='100000000000000000'
user_tab_col_statistics.id.num_distinct=2000000
user_indexes.t9_id.blevel=2
user_indexes.t9_id.leaf_blocks=8369
user_indexes.t9_id.clustering_factor=8683

计算IO_COST为4

index_selectivity = required range / total range + 1/num_distinct + 1/num_distinct
= (70000-60000)/(100000000000000000-1)+2/2000000
= 0.000001

cost=blevel + ceiling(leaf_blocks * index_selectivity) + ceiling(clustering_factor * table_selectivity)
=2+ceiling(8369*0.000001)+ceiling(8683*0.000001)
=2+ceiling（0.008369）+ceiling(0.008683)
=2+1+1
=4

确认IO_COST为4，总COST为4，CPU_COST为0.00308。
关于CPU_COST的疑问同上。

SQL> select * from t9 where id between '000000000000060000' and '000000000000070000';

Execution Plan
----------------------------------------------------------
Plan hash value: 3577433401

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T9 | 1 | 26 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T9_ID | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">='000000000000060000' AND "ID"<='000000000000070000')

SQL>

SQL> select a.OPTIONS,a.OBJECT_NAME,a.ID,a.COST,a.CPU_COST,a.IO_COST from v$sql_plan a where plan_hash_value='3577433401';

OPTIONS OBJECT_NAME ID COST CPU_COST IO_COST
-------------------- --------------- ---------- ---------- ---------- ----------
0 4
BY INDEX ROWID T9 1 4 29226 4
RANGE SCAN T9_ID 2 3 21764 3

CPU_COST=(v$sql_plan.CPU_COST/CPUSPEED)/SREADTIM
=(29226/1486*1000000)/(6.39/1000)
=0.00308

三、数据分布不均匀，收集了histogram statistics

收集histogram statistics
begin
dbms_stats.gather_table_stats(ownname => 'MEISHD',tabname => 'T9',estimate_percent => 100,method_opt => 'FOR COLUMNS SIZE 20 id');
end;
SQL> select table_name,column_name,num_buckets,histogram from user_tab_col_statistics where column_name='ID';

TABLE_NAME COLUMN_NAME NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ----------- ---------------
T9 ID 20 HEIGHT BALANCED

SQL> select endpoint_number,endpoint_actual_value from user_tab_histograms where table_name='T9' and column_name='ID';

ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE
--------------- ------------------------------
0 000000000000000001
1 000000000000100000
2 000000000000200000
3 000000000000300000
4 000000000000400000
5 000000000000500000
6 000000000000600000
7 000000000000700000
8 000000000000800000
9 000000000000900000
10 000000000001000000
11 000000000001100000
12 000000000001200000
13 000000000001300000
14 000000000001400000
15 000000000001500000
16 000000000001600000
17 000000000001700000
18 000000000001800000
19 000000000001900000
20 100000000000000000

21 rows selected.

SQL>

计算IO_COST为88

index_selectivity_bucket = required range / total range + 1/num_distinct + 1/num_distinct)
= (70000-60000)/(100000-1)+2/2000000
= 0.100002
index_selectivity = index_selectivity_bucket/num_buckets = 0.0050001


cost=blevel + ceiling(leaf_blocks * index_selectivity) + ceiling(clustering_factor * table_selectivity)
=2+ceiling(8369*0.0050001)+ceiling(8683*0.0050001)
=2+ceiling（41.84）+ceiling(43.42)
=2+42+44
=88

SQL> select * from t9 where id between '000000000000060000' and '000000000000070000';

Execution Plan
----------------------------------------------------------
Plan hash value: 3577433401

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 253K| 88 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T9 | 10000 | 253K| 88 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T9_ID | 10000 | | 44 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">='000000000000060000' AND "ID"<='000000000000070000')

SQL>

确认IO_COST为88，总COST为88，CPU_COST为0.46。
关于CPU_COST的疑问同上。

SQL> select a.OPTIONS,a.OBJECT_NAME,a.ID,a.COST,a.CPU_COST,a.IO_COST from v$sql_plan a where plan_hash_value='3577433401';

OPTIONS OBJECT_NAME ID COST CPU_COST IO_COST
-------------------- --------------- ---------- ---------- ---------- ----------
0 88
BY INDEX ROWID T9 1 88 4327057 88
RANGE SCAN T9_ID 2 44 2313543 44

CPU_COST=(v$sql_plan.CPU_COST/CPUSPEED)/SREADTIM
=(4327057/1486*1000000)/(6.39/1000)
=0.46

总结
在数据分布严重不均衡的情况下（尤其是认为设置了致命的default值），根据统计信息算出的COST严重不符事实。因此：
1、设置default值时，应在正常的数据范围内；
2、如果数据自身很不均衡，则需要通过收集histogram统计信息来保证COST的准确性。