一、绑定变量的窥探(peek)
1、Oracle在处理带有绑定变量的SQL时候,只会在硬解析的时候才会“窥探”一下SQL中绑定变量的值,然后会根据窥探到的值来决定整个SQL的执行计划。参数:_optim_peek_user_binds
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
a.频率直方图,当列中Distinct_keys小于254,Oracle就会自动的创建频率直方图,并且桶数(BUCKET)等于Distinct_Keys。
b.高度平衡直方图,当列中Distinct_keys大于254,Oracle就会自动的创建高度平衡直方图。
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与直方图相互作用
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)再用1值来执行一个查询:
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';
(1)不绑定变量的情况下对于列倾斜严重的情况,直方图可以提供最好的数据分布参考
(2)绑定变量窥视的情况下 可以利用到直方图,但是11g adaptive cursor sharing之前无法区别绑定敏感游标和非敏感游标
(3)不窥视绑定变量的情况下虽然加载直方图信息,但实际计算cardinality不参考HISTOGRAM
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036