柱状图(Histogram)，绑定变量,bind peeking，cursor_sharing 之间的关系1 柱状图

柱状图(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)与绑定变量之间的关系