绑定变量与直方图的最佳实践

测试DB版本：

sys@OCM> select * from v$version where rownum=1;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

一、绑定变量的窥探(peek)

1、Oracle在处理带有绑定变量的SQL时候，只会在硬解析的时候才会“窥探”一下SQL中绑定变量的值，然后会根据窥探到的值来决定整个SQL的执行计划。参数：_optim_peek_user_binds

2、绑定变量窥探的案例

create table t8(id int ,name varchar2(100));

begin

for i in 1 .. 1000 loop

insert into t8 values(i,'gyj'||i);

end loop;

commit;

end;

create index t_idx on t8(id);

exec dbms_stats.gather_table_stats(user,'T8',cascade=>true);

　select id,count(*) from t8 group by id;

variable n number;

exec :n := 1;

select count(*) from t8 where id = :n;

select * from table(dbms_xplan.display_cursor);

--再插入值

for i in 1 .. 10000 loop

insert into t8 values(1,'gyj'||i);

exec dbms_stats.gather_table_stats(user,'T8',cascade=>true);

variable n number;

exec :n := 1;

select count(*) from t8 where id = :n;

select * from table(dbms_xplan.display_cursor);

二、直方图

1、作用：当某列数据分布不均衡，为了让CBO能生成最佳的执行计划，我们可能需要对表收集直方图，直方图最大的桶数(Bucket)是254。

2、两种直方图

　（1）频率直方图，当列中Distinct_keys小于254，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于Distinct_Keys

（2）高度平衡直方图，当列中Distinct_keys大于254，Oracle就会自动的创建高度平衡直方图

（3）生成直方图

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'GYJ',

tabname => 'T8',

estimate_percent => 100,

method_opt => 'for all columns size skewonly',

no_invalidate => FALSE,

degree => 1,

cascade => TRUE);

END;

/

三、绑定变量窥视bind peek与直方图相互作用

1、收集直方图并 不绑定变量 采用hard coding 硬编码

　　select * from v$version;

　　select id,count(*) from t8 group by id;

select count(*) from t8 where id =1;

select count(*) from t8 where id =2;

不适用用绑定变量硬解析依据直方图可以获得很好的基数(cardinality)

2、以下是 使用绑定变量并窥视 + 直方图存在时的情况

（1）清除缓存

alter system flush shared_pool;

alter system flush buffer_cache;

（2）统计直方图

BEGIN

method_opt => 'for all columns size 254',

（3）测试1和2不匀均的值

select id,count(*) from t8 group by id;

variable n number;

exec :n := 1;

select count(*) from t8 where id = :n;

select * from table(dbms_xplan.display_cursor);

select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,plan_hash_value from v$sql where sql_id = '7zy48bjbwdjff';

exec :n := 2;

select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware,plan_hash_value from v$sql where sql_id = '7zy48bjbwdjff';

（4）再用2值来执行一个查询

　exec :n := 2;

（5）再用１值来执行一个查询：

3、绑定变量但不窥视　+　直方图的情况

alter session set "_optim_peek_user_binds"=false;

4、总结：

（1）不绑定变量的情况下对于列倾斜严重的情况,直方图可以提供最好的数据分布参考

（2）绑定变量窥视的情况下 可以利用到直方图,但是11g adaptive cursor sharing之前无法区别绑定敏感游标和非敏感游标

（3）不窥视绑定变量的情况下虽然加载直方图信息,但实际计算cardinality不参考HISTOGRAM