绑定变量窥探和直方图

  一、绑定变量的窥探(peek)1、Oracle在处理带有绑定变量的SQL时候，只会在硬解析的时候才会“窥探”一下SQL中绑定变量的值，然后会根据窥探到的值来决定整个SQL的执行计划。参数：_optim_peek_user_binds2、绑定变量窥探的案例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_ID7zy48bjbwdjff, child number 0-------------------------------------select count(*) from t8  where id = :nPlan hash value: 293504097---------------------------------------------------------------------------| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT  |  |  |  |1 (100)|  ||   1 |  SORT AGGREGATE   |  |1 |4 |       |  |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------|*  2 |   INDEX RANGE SCAN| T_IDX |1 |4 |1   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=:N)19 rows selected.   --再插入值beginfor i in 1 .. 10000 loop  insert into t8 values(1,'a'||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;SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID7zy48bjbwdjff, child number 0-------------------------------------select count(*) from t8  where id = :nPlan hash value: 293504097---------------------------------------------------------------------------| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT  |  |  |  |1 (100)|  ||   1 |  SORT AGGREGATE   |  |1 |4 |       |  |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------|*  2 |   INDEX RANGE SCAN| T_IDX |1 |4 |1   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=:N)19 rows selected.不管以后数据量是什么情况,只按第一次硬解析的执行计划run SQL二、直方图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=> 'HR',  tabname          => 'T8',  estimate_percent => 100,  method_opt       => 'for all columns size skewonly',  no_invalidate    => FALSE,  degree           => 1,  cascade          => TRUE);END;method_opt       => 'for all columns size skewonly',---正式的生产环境中，最好别用allcolumns方式收集直方图，因为all columns 几乎会对所有列都收集直方图信息，但是有些列并不会出现在where条件中，我们去收集并不会出现在where条件中的列就浪费了资源。三、绑定变量窥视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;/SQL> select count(*) from t8  where id =1;Execution Plan----------------------------------------------------------Plan hash value: 2157757785---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |4 |9   (0)| 00:00:01 ||   1 |  SORT AGGREGATE    |  |1 |4 |       |  ||*  2 |   TABLE ACCESS FULL| T8   | 10004 | 40016 |9   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("ID"=1)Statistics----------------------------------------------------------  1  recursive calls  0  db block gets 31  consistent gets  0  physical reads  0  redo size424  bytes sent via SQL*Net to client419  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processedSQL> select count(*) from t8  where id =2;Execution Plan----------------------------------------------------------Plan hash value: 293504097---------------------------------------------------------------------------| Id  | Operation  | Name  | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT  |  |1 |4 |1   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   |  |1 |4 |       |  ||*  2 |   INDEX RANGE SCAN| T_IDX |1 |4 |1   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("ID"=2)Statistics----------------------------------------------------------  1  recursive calls  0  db block gets  2  consistent gets  0  physical reads  0  redo size422  bytes sent via SQL*Net to client419  bytes received via SQL*Net from client  2  SQL*Net roundtrips to/from client  0  sorts (memory)  0  sorts (disk)  1  rows processed不适用用绑定变量硬解析依据直方图可以获得很好的基数(cardinality)