柱状图(histogram):柱状图用于记录表中的列的分布情况，有了柱状图的统计信息之后，CBO就能决定到底是否使用使用该列的索引，如果数据分布不均匀，CBO可能仅仅依据索引的选择性(selectivity)来判断是否使用该索引，从而导致选择不到最优的执行计划。
下面是实验步骤:
SQL> create table test as select * from dba_objects;
表已创建。
SQL> update test set status='VALID';
已更新49916行。
SQL> commit;
提交完成。
SQL> update test set status='INVALID' where owner='SYS';
已更新22964行。
SQL> commit;
提交完成。
SQL> update test set status='UNKONWN' where owner='SCOTT';
已更新12行。
SQL> commit;
提交完成。
SQL> create index statusind on test(status) online;
索引已创建。
SQL> exec dbms_stats.gather_table_stats('robinson','test');
PL/SQL 过程已成功完成。
SQL> select index_name,last_analyzed from user_indexes;
INDEX_NAME LAST_ANALYZED
------------------------------ --------------
STATUSIND 09-12月-09
SYS_IL0000052108C00036$$
SQL> exec dbms_stats.gather_table_stats('robinson','test',method_opt=>'for columns size 1 status');
删除柱状图的统计
SQL> set autot trace
SQL> select owner from test where status='VALID';
已选择26940行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24958 | 316K| 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 24958 | 316K| 142 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='VALID')
SQL> select owner from test where status='UNKONWN';
已选择12行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24958 | 316K| 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 24958 | 316K| 142 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='UNKONWN')
SQL> select index_name,distinct_keys/num_rows from user_indexes;
INDEX_NAME DISTINCT_KEYS/NUM_ROWS
------------------------------ ----------------------
STATUSIND .000060101
此列(status)的选择率确实很低，CBO认为选择率很低所以直接走了全表扫描，但是status为UNKNOWN的列仅仅有12行，此时如果走索引将大大降低COST。
现在我将柱状图的统计信息加上
SQL> exec dbms_stats.gather_table_stats('robinson','test',method_opt=>'for columns size 10 status');
SQL> select owner from test where status='UNKONWN';
已选择12行。
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 3251734315
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 234 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 18 | 234 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | STATUSIND | 18 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
由此可见有了柱状图的统计信息，CBO将更加智能的选择是否走索引，而不是仅仅根据选择率来判断是否走索引
继续研究，此刻我将柱状图统计信息删除
SQL> exec dbms_stats.gather_table_stats('robinson','test',method_opt=>'for columns size 1 status');
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.23
SQL> select owner from test where status='UNKONWN';
已选择12行。
已用时间: 00: 00: 00.02
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24959 | 316K| 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 24959 | 316K| 142 (3)| 00:00:02 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='UNKONWN')
我使用HINT提示走索引，发现居然 COST高很多不正常
SQL> select /*+ index(test statusind) */ owner from test where status='UNKONWN';
已选择12行。
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 3251734315
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24959 | 316K| 669 (1)| 00:00:09 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 24959 | 316K| 669 (1)| 00:00:09 |
|* 2 | INDEX RANGE SCAN | STATUSIND | 24959 | | 64 (2)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='UNKONWN')
为什么加HINT提示后 COST居然这么高呢?这是一个问题，希望看过此博客的人给个提示(估计我得好好研究基于成本的优化法则了)
我开始怀疑AUTOTRACE 有bug 于是查看v$sql_plan ,查看这个视图之前我flush 了shared_pool 并且直接运行了如下语句
SQL> select /*+ index(test statusind) */ owner from test where status='UNKONWN';
OWNER
------------------------------
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
已选择12行。
SQL> select operation,options,object_name,id,parent_id,cost from v$sql_plan where object_name='TEST';
OPERATION OPTIONS OBJECT_NAME ID PARENT_ID COST
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ---------- ---------- ----------
TABLE ACCESS BY INDEX ROWID TEST 1 0 669
发现此处并无BUG。nnd 看来还得继续努力学习啊，今天到此为止，明天继续探讨柱状图(histogram)与绑定变量之间的关系