绑定变量与直方图

一、绑定变量的窥探(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);

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

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name:    guoyJoe

QQ:        252803295

Email:    oracledba_cn@hotmail.com

Blog:      http://blog.youkuaiyun.com/guoyJoe

ITPUB:   http://www.itpub.net/space-uid-28460966.html

OCM:     http://education.oracle.com/education/otn/YGuo.HTM
 _____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：http://blog.youkuaiyun.com/guoyjoe/article/details/8624392

Oracle@Paradise 　总群:127149411

Oracle@Paradise No.1群:177089463(已满)

Oracle@Paradise No.2群:121341761

Oracle@Paradise No.3群:140856036