绑定变量与直方图-CSDN博客

一、绑定变量的窥探(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);
3、再插入值
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、两种直方图
　 a.频率直方图，当列中Distinct_keys小于254，Oracle就会自动的创建频率直方图，并且桶数(BUCKET)等于Distinct_Keys。

　 b.高度平衡直方图，当列中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);