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

前面讨论了有了柱状图的统计信息的时候，对于分布不均衡的列，如果没有使用绑定变量，CBO将会选择正确的执行计划，下面来谈谈对于分布不均衡的列，使用了绑定变量，不收集柱状图统计信息，收集了柱状图统计信息分别会发生什么情况。

本测试同样使用TEST表，表结构和内容请看前一篇内容

在不收集柱状图统计信息的情况下：

session 1中

SQL> exec dbms_stats.gather_table_stats('robinson','test',method_opt=>'for columns size 1 status');

PL/SQL 过程已成功完成。

----将size 置为1，可以删除柱状图统计信息

session 2中

SQL> alter system flush shared_pool;

系统已更改。

session 1中

SQL> variable a varchar2(20);
SQL> exec :a:='UNKONWN';

PL/SQL 过程已成功完成。

SQL> set autot trace

SQL> select owner from test where status=:a;

已选择12行。

执行计划
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16639 | 211K| 142 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TEST | 16639 | 211K| 142 (3)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter("STATUS"=:A)

此处我没有贴上统计信息
SQL> set autot off
SQL> select operation,options,object_name,id,parent_id,cost from v$sql_plan where object_name='TEST';

OPERATION OPTIONS OBJECT_NAME ID
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------ ----
TABLE ACCESS FULL TEST 1

查看V$sql_plan是为了验证行我们通过autotrace看到的执行计划是否与真实的执行计划一样，此处一样

由此可见，在没有柱状图统计信息的情况下，使用绑定变量的执行计划与不使用绑定变量的执行计划一样，CBO都选择了错误(不是最优)的执行计划。

现在收集柱状图的统计信息

SQL> exec dbms_stats.gather_table_stats('robinson','test',method_opt=>'for columns size 10 status');

PL/SQL 过程已成功完成。

SQL> set autot trace

SQL> select owner from test where status=:a;

已选择12行。

执行计划
----------------------------------------------------------
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"=:A)

SQL> select operation,options,object_name,id,parent_id,cost,plan_hash_value from v$sql_plan where object_name='TEST';

OPERATION OPTIONS OBJECT_NAM ID PARENT_ID COST PLAN_HASH_VALUE
-------------------- ---------- ---------- ---------- ---------- ---------- ---------------
TABLE ACCESS FULL TEST 1 0 142 1357081020
TABLE ACCESS FULL TEST 2 1 141 1950795681
TABLE ACCESS SAMPLE TEST 2 1 21 3141299468

收集了柱状图统计信息之后还是没有走索引

session 2中

SQL> alter system flush shared_pool;

系统已更改。

session 1中

SQL> select owner from test where status=:a;

已选择12行。

执行计划
----------------------------------------------------------
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"=:A)

SQL> set autot off
SQL> select operation,options,object_name,id,parent_id,cost,plan_hash_value from v$sql_plan where object_name='TEST';

OPERATION OPTIONS OBJECT_NAM ID PARENT_ID COST PLAN_HASH_VALUE
-------------------- ---------- ---------- ---------- ---------- ---------- ---------------
TABLE ACCESS BY INDEX ROWID TEST 1 0 2 3251734315
为什么 AUTOTRACE 和v$sql_plan查出来的执行计划不一样？,这点不能明白，不过这个时候就只能相信v$sql_plan了

事实上这个时候是走了索引的，我们通过autotrace看到的执行计划是错误的

SQL> exec :a:='VALID';

PL/SQL 过程已成功完成。

SQL> set autot trace
SQL> select owner from test where status=:a;

已选择26942行。

执行计划
----------------------------------------------------------
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"=:A)

SQL> set autot off
SQL> select operation,options,object_name,id,parent_id,cost,plan_hash_value from v$sql_plan where object_name='TEST';

OPERATION OPTIONS OBJECT_NAM ID PARENT_ID COST PLAN_HASH_VALUE
-------------------- ---------- ---------- ---------- ---------- ---------- ---------------
TABLE ACCESS BY INDEX ROWID TEST 1 0 2 3251734315

由此可见AUTOTRACE显示执行计划也是错误的，此处通过观察发现V$SQL_PLAN里面执行计划也没有改变，但是此处选择了26942行，不该走索引，而且COST居然也是2,此处发现AUTOTRACE 和v$sql_plan都有问题，不过可以肯定的是这个时候没有走全表扫描，而是走的第一次硬解析的时候走的索引范围扫描。

事实上引起这个问题的原因就是bind peeking（绑定窥视)

BIND PEEKING:如果收集了柱状图统计信息，并且使用了绑定变量，在第一次硬解析的时候CBO就会执行BIND PEEKING，以便选择最佳的执行计划，再次执行的时候就不会bind peeking了，始终要记住bind peeking发生的条件:硬解析，存在柱状图，使用了绑定变量或者设置cursor_sharing=similar，下一篇就专门讨论(cursor_sharing=similar)这个问题。

通过实验基本上知道了这个事实:当列分布很倾斜的时候，如果对该列使用了绑定变量，如果不收集柱状图统计信息，那么ORACLE按照全表扫描检索，如果收集了柱状图统计信息，在第一次硬解析的时候CBO会考虑柱状图，选择走索引或者走全表扫描，一旦确定了执行计划，那么第二次执行该SQL，就会利用原来生成的执行计划，而不会去选择最优的执行计划。

就像上面的例子:检索status 为UNKONWN的时候应该走index range scan,而检索status为VALID的时候应该走FULL TABLE SCAN，

但是如果第一次查询STATUS 为UNKONWN ，走了index range scan，这个时候CBO选择正确，但是下一次查询status为VALID的时候应该走full table scan，然后CBO依然选择了index range scan，这个时候CBO选择错误执行计划，而且查看autotrace的执行计划显示有问题，查看v$sql_plan cost有问题.呵呵我应该用10046再追踪一下。

不过可以得到2个提示:1绑定变量并不是在所有情况下都可以提高效率，当然绝大多数情况下会，在数据分布不均衡的情况下，还是别用绑定变量了，从上面的例子可以看出，使用了绑定变量，如果表记录达到百万级别，然后通过索引扫描，那将会对性能带来多大影响！，然而这个时候不使用绑定变量，只有三种情况 select owner from test where status='VALID'，select owner from test where status='INVALID',select owner from test where status='UNKONWN'，并不会给shared pool带来多大的问题，因为status值只有3个，重复解析最多3次。

2不要轻易相信autotrace(大多数情况下还是准确的)。