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