绑定变量窥探和直方图

  一、绑定变量的窥探(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, 'a' || i);
  end loop;
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);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	7zy48bjbwdjff, child number 0
-------------------------------------
select count(*) from t8  where id = :n

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id