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

前面讨论了柱状图对于绑定变量的影响，现在讨论柱状图对于cursor_sharing的影响，本实验继续以TEST表实验，关于TEST表的具体结构和完整内容请查看前面的内容。

SESSION 1中

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

SQL> alter system flush shared_pool;

系统已更改。

SQL> alter system set cursor_sharing=similar;

系统已更改。

SESSION 2中

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='VALID';

已选择26942行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020

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

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 PARENT_ID COST
---------- ------------------------- ------------------------- ---------- ---------- ----------
TABLE ACCE FULL TEST 1 0 142

SQL> set autot trace
SQL> select owner from test where status='UNKONWN';

已选择12行。
执行计划
----------------------------------------------------------
Plan hash value: 3251734315

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 221 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 17 | 221 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | STATUSIND | 17 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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 PARENT_ID COST
-------------------- ------------------------- ------------------------- ---------- ---------- ----------
TABLE ACCESS BY INDEX ROWID TEST 1 0 2
TABLE ACCESS FULL TEST 1 0 142

SQL> set autot trace
SQL> select owner from test where status='INVALID';

已选择22964行。
执行计划
----------------------------------------------------------
Plan hash value: 1357081020

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

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 PARENT_ID COST
-------------------- ------------------------- ------------------------- ---------- ---------- ----------
TABLE ACCESS FULL TEST 1 0 142
TABLE ACCESS BY INDEX ROWID TEST 1 0 2
TABLE ACCESS FULL TEST 1 0 142

SQL> select sql_text,version_count from v$sqlarea where sql_text like ' select owner from test%';

SQL_TEXT VERSION_COUNT
------------------------------ -------------
select owner from test where 3
status=:"SYS_B_0"

select owner from test where 3
status=:"SYS_B_0"

在参数cursor_sharing=similar的情况下，如果存在柱状图，那么在该SQL语句执行的时候会进行bind peeking，选择适当的执行计划，此处的行为与使用绑定变量不一样。

现在我将柱状图统计信息删除，再测试一下

SESSION 1中

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area 268435456 bytes
Fixed Size 1290112 bytes
Variable Size 159383680 bytes
Database Buffers 100663296 bytes
Redo Buffers 7098368 bytes
数据库装载完毕。
数据库已经打开。

SQL> alter system flush shared_pool;

系统已更改。

SESSION 2中

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

PL/SQL 过程已成功完成。

SQL> set autot trace

SQL> select owner from test where status='INVALID';

已选择22964行。
执行计划
----------------------------------------------------------
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 |
--------------------------------------------------------------------------

SQL> col operation format a20;
SQL> col options format a25
SQL> col object_name format a20
SQL> col plan_hash_value format 999999999999

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_NAME ID PARENT_ID COST PLAN_HASH_VALUE
-------------------- ------------------------- -------------------- ---------- ---------- ---------- ---------------
TABLE ACCESS FULL TEST 1 0 142 1357081020

SQL> set autot trace
SQL> select owner from test where status='UNKONWN';

已选择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 |
--------------------------------------------------------------------------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';

SQL> col sql_text format a30
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select owner from test%';

SQL_TEXT VERSION_COUNT
------------------------------ -------------
select owner from test where s 1
tatus=:"SYS_B_0"

由此可知，缺乏柱状图，cursor_sharing=similar表现和cursor_sharing=force一样，我前面的cursor_sharing version_count的文章已经讨论过了这个问题，下面继续实验，我首先查询status='UNKONWN'

SESSION 1中:

SQL> alter system flush shared_pool;

系统已更改。

SESSION 2中：

SQL> set autot trace
SQL> select owner from test where status='UNKONWN';

已选择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 |
--------------------------------------------------------------------------

SQL> set autot off;
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select owner from test%';

SQL_TEXT VERSION_COUNT
------------------------------ -------------
select owner from test where s 1
tatus=:"SYS_B_0"

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

总结：

连续的三个实验说明了，在10gR2中，如果某列倾斜严重，数据分布不平衡，收集了该列柱状图的统计信息，如果查询要利用到该列的索引，那么在编写SQL的时候，不要使用绑定变量，可以设置CURSOR_SHARING=SIMILAR，那么CBO可能会选择比较优的执行计划，但是设置CURSOR_SHARING=SIMILAR又会带来另外一个问题---VERSION_COUNT 过高，仔细看看上面的测试你会发现VERSION_COUNT=3,不过这不是问题，通常数据列倾斜严重，那么VERSION_COUNT也不会太高，不需担心，对于version_count带来的性能开销，肯定没有执行计划选错带来的大。如果你其他应用使用了绑定变量了，就不会担心VERSION_COUNT这个问题了，所以设置CURSOR_SHARING=SIMILAR还是有用的，前提是其他SQL一定要使用绑定变量。SQL一定要使用绑定变量吗？不一定，一定要考虑数据分布，考虑业务需求!!!