綁定變量與直方圖的最佳實踐

測試DB版本：

sys@OCM> select * from v$version where rownum=1;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

一、綁定變量的窺探(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);

--再插入值

for i in 1 .. 10000 loop

insert into t8 values(1,'gyj'||i);

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、兩種直方圖

　（1）頻率直方圖，當列中Distinct_keys小于254，Oracle就會自動的建立頻率直方圖，并且桶數(BUCKET)等于Distinct_Keys

（2）高度平衡直方圖，當列中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;

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

method_opt => 'for all columns size 254',

（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 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;

（5）再用１值來執行一個查詢：

3、綁定變量但不窺視　+　直方圖的情況

alter session set "_optim_peek_user_binds"=false;

4、總結：

（1）不綁定變量的情況下對于列傾斜嚴重的情況,直方圖可以提供最好的資料分布參考

（2）綁定變量窺視的情況下 可以利用到直方圖,但是11g adaptive cursor sharing之前無法差別綁定敏感遊标和非敏感遊标

（3）不窺視綁定變量的情況下雖然加載直方圖資訊,但實際計算cardinality不參考HISTOGRAM