绑定变量与直方图的最佳实践
测试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);
--再插入值
begin
for i in 1 .. 10000 loop
insert into t8 values(1,'gyj'||i);
end loop;
commit;
end;
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;
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;
/
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
DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'GYJ',
tabname => 'T8',
estimate_percent => 100,
method_opt => 'for all columns size 254',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
（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 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';
（4）再用2值来执行一个查询
exec :n := 2;
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';
（5）再用１值来执行一个查询：
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';
3、绑定变量但不窥视 + 直方图的情况
alter session set "_optim_peek_user_binds"=false;
alter system flush shared_pool;
alter system flush buffer_cache;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'GYJ',
tabname => 'T8',
estimate_percent => 100,
method_opt => 'for all columns size 254',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/
variable n number;
exec :n := 1;
select count(*) from t8 where id = :n;
select * from table(dbms_xplan.display_cursor);
exec :n := 2;
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';
4、总结：
（1）不绑定变量的情况下对于列倾斜严重的情况,直方图可以提供最好的数据分布参考
（2）绑定变量窥视的情况下 可以利用到直方图,但是11g adaptive cursor sharing之前无法区别绑定敏感游标和非敏感游标
（3）不窥视绑定变量的情况下虽然加载直方图信息,但实际计算cardinality不参考HISTOGRAM
源文档 <http://www.itpub.net/forum.php?mod=viewthread&tid=1773166>
