Oracle直方圖解析

一、    何謂直方圖：

直方圖是一種統計學上的工具，並非Oracle專有。通常用於對被管理物件的某個方面的質量情況進行管理，通常情況下它會表現為一種幾何圖形表，這個圖形表是根據從實際環境中所收集來的被管理物件某個方面的質量分佈情況的資料所繪製成的，通常會畫成以數量為底邊，以頻度為高度的一系列連線起來的矩形圖，因此直方圖在統計學上也稱為質量分佈圖。比如下圖所示，是一個以關學生化學考試成績分數分佈情況繪製的直方圖：

      

     

二、       Oracle中直方圖的作用：

既然直方圖是一種對被管理物件某一方面質量進行管理的描述工具，那麼在Oracle中自然它也是對Oracle中某個物件質量的描述工具，這個物件就是Oracle中最重要的東西——“資料”。

在Oracle中直方圖是一種對資料分佈質量情況進行描述的工具。它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪製資料的分佈情況，以便能夠指導最佳化器根據資料的分佈做出正確的選擇。在某些情況下，表的列中的數值分佈將會影響最佳化器使用索引還是執行全表掃描的決策。當 where 子句的值具有不成比例數量的數值時，將出現這種情況，使得全表掃描比索引訪問的成本更低。這種情況下如果where 子句的過濾謂詞列之上上有一個合理的正確的直方圖，將會對最佳化器做出正確的選擇發揮巨大的作用，使得SQL語句執行成本最低從而提升效能。

三、       Oracle中使用直方圖的場合：

在分析表或索引時，直方圖用於記錄資料的分佈。透過獲得該資訊，基於成本的優  化器就可以決定使用將返回少量行的索引，而避免使用基於限制條件返回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上構建直方圖。

構造直方圖最主要的原因就是幫助最佳化器在表中資料嚴重偏斜時做出更好的規劃：例如，如果一到兩個值構成了表中的大部分資料(資料偏斜)，相關的索引就可能無法幫助減少滿足查詢所需的I/O數量。建立直方圖可以讓基於成本的最佳化器知道何時使用索引才最合適，或何時應該根據WHERE子句中的值返回表中80％的記錄。

          通常情況下在以下場合中建議使用直方圖：

          （1）、當Where子句引用了列值分佈存在明顯偏差的列時：當這種偏差相當明顯時，以至於 WHERE 子句中的值將會使最佳化器選擇不同的執行計劃。這時應該使用直方圖來幫助最佳化器來修正執行路徑。（注意：如果查詢不引用該列，則建立直方圖沒有意義。這種錯誤很常見，許多 DBA 會在偏差列上建立柱狀圖，即使沒有任何查詢引用該列。）

           （2）、當列值導致不正確的判斷時：這種情況通常會發生在多表連線時，例如，假設我們有一個五項的表聯接，其結果集只有 10 行。Oracle 將會以一種使第一個聯接的結果集（集合基數）儘可能小的方式將表聯接起來。透過在中間結果集中攜帶更少的負載，查詢將會執行得更快。為了使中間結果最小化，最佳化器嘗試在 SQL 執行的分析階段評估每個結果集的集合基數。在偏差的列上擁有直方圖將會極大地幫助最佳化器作出正確的決策。如最佳化器對中間結果集的大小作出不正確的判斷，它可能會選擇一種未達到最最佳化的表聯接方法。因此向該列新增直方圖經常會向最佳化器提供使用最佳聯接方法所需的資訊。

四、如何使用直方圖：

          （1）、建立直方圖：

                透過使用早先的analyze命令和最新的dbms_stats工具包都可以建立直方圖。Oracle推薦使用後者來建立直方圖，而且直方圖的建立不受任何條件限制，可以在一張表上的任何你想建立直方圖的列上建立直方圖。我們這裡主要介紹如何透過dbms_stats包來建立直方圖。

                Oracle 透過指定 dbms_stats 的 method_opt 引數，來建立直方圖。在method_opt 子句中有三個相關選項，即 skewonly、repeat 和 auto。

                “skewonly” 選項，它的時間性很強，因為它檢查每個索引中每列值的分佈。如果 dbms_stats 發現一個索引中具有不均勻分佈的列，它將為該索引建立直方圖，以幫助基於成本的 SQL 最佳化器決定是使用索引還是全表掃描訪問。示例如下：

            begin

              dbms_stats. gather_table_stats (

                         ownname          => '',

tabname=>''，

                      estimate_percent =>dbms_stats.auto_sample_size,

                   method_opt       => 'for all columns size skewonly',

                   cascade=>true,

                   degree           => 7);

end;

     其中degree指定了並行度視主機的CPU個數而定，estimate_percent指定了取樣比率，此處使用了auto目的是讓oracle來決定取樣收集的比率，繪製直方圖時會根據取樣的資料分析結果來繪製，當然也可以人為指定取樣比率。如：estimate_percent=>20指定取樣比率為20%，cascade=>true指定收集相關表的索引的統計資訊，該引數預設為false，因此使用dbms_stats收集統計資訊時抹人事部收集表的索引資訊的。

     在對錶實施監視 (alter table xxx monitoring;) 時使用 auto 選項，它基於資料的分佈以及應用程式訪問列的方式（例如由監視所確定的列上的負載）來建立直方圖。示例如下：

begin

             dbms_stats.gather_ table _stats(

              ownname          => '',

tabname=>''，

              estimate_percent => dbms_stats.auto_sample_size,

              method_opt       => 'for all columns size auto',

              cascade=>true，

              degree           => 7

           );

end;

     重新分析統計資料時，使用repeat選項，重新分析任務所消耗的資源就會少一些。使用repeat選項時，只會為現有的直方圖重新分析索引，不再生成新的直方圖。示例如下：

                dbms_stats.gather_ table _stats(

ownname => '',

tabname=>''，

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for all columns size repeat',

cascade=>true，

degree => 7

);

end;

          （2）、建立直方圖的考慮因素：

            如果想為某一列建立直方圖，示例如下：

            dbms_stats.gather_ table _stats(

ownname => '',

tabname=>''，

estimate_percent => dbms_stats.auto_sample_size,

method_opt => 'for columns size 10 列名',

cascade=>true，

degree => 7

);

end;

其中size 10指定的是直方圖所需的儲存桶(bucket)數，所謂儲存桶可以理解為儲存資料的容器，這個容器會按照資料的分佈將資料儘量平均到各個桶裡，如一張表如果有6000條記錄，那麼每個桶中平均就會有600條記錄，但這只是一個平均數，每個桶中的記錄數並不會相等，它會將高頻出現記錄集中在某一些桶中，低頻記錄會存放在少量桶中，因此如果儲存桶(bucket)數合適的增加就會減少高頻記錄使用的桶數，統計結果也會更加準確（可以避免被迫將低頻記錄存入高頻桶中，影響最佳化器生成準確的執行計劃）。所以我們最後得到的直方圖資訊的準確性就由兩個數值決定，一個是BUCTET的個數，一個NUM_DISTINCT的個數。所以建立直方圖時首先要正確地估計儲存桶(bucket)數。預設情況時，Oracle的直方圖會產生75個儲存桶。可以把SIZE的值指定在1~254之間。

（3）、刪除直方圖資訊：

 

             在oracle中要刪除直方圖資訊就是設定bucket的資料為1，可以使用如下兩個命令來實現：

              Analyze table 表  compute statistics for table for columns id size 1；

              exec dbms_stats.gather_table_stats('使用者', '表',cascade=>false, method_opt=>'for columns 列size 1');

 

五、           Oracle直方圖的種類：

Oracle利用直方圖來提高非均勻資料分佈的選擇率和技術的計算精度。但是實際上Oracle會採用另種不同的策略來生成直方圖：其中一種是針對包含很少不同值的資料集；另一種是針對包含很多不同的資料集。Oracle會針對第一種情況生成頻率直方圖，針對第二種情況生成高度均衡直方圖。通常情況下當BUCTET ，而當BUCTET = 表的NUM_DISTINCT值的時候得到的是FREQUENCY（頻率）直方圖。由於滿足BUCTET = 表的NUM_DISTINCT值機率較低，所以在Oracle中生成的直方圖大部分是HEIGHT BALANCED（高度平衡）直方圖。在Oracle 10GR2之前如果使用dbms_stats包來建立直方圖，那麼如果指定需要建立的直方圖的桶的數目與表的NUM_DISTINCT值相等，那麼幾乎無法建立出一個FREQUENCY（頻率）直方圖，此時為了得到頻率直方圖只能使用analyze命令的“for all columns size 表的NUM_DISTINCT值”，這在某種程度上來說是一個退步，但這個問題在Oracle 10GR2後被修正。但是如果列中有180 - 200個不同值時，還是無法建立FREQUENCY（頻率）直方圖.此時需要手工建立直方圖,並寫入資料字典方能完成FREQUENCY（頻率）直方圖的建立。

對於含有較少的不同值而且資料分佈又非常不均勻的資料集來說，建立FREQUENCY（頻率）直方圖將會更加合適，因為它不存在會將低頻出現的記錄存入高頻桶中的情況，而HEIGHT BALANCED（高度平衡）直方圖在儲存桶(bucket)數分配不合理時就可能會出現這種情況。因此一定要在建立直方圖前確定使用何種直方圖，並且要合理估計儲存桶(bucket)數。

      （1）、頻率直方圖：

            Oracle中的頻率直方圖是按照累積某一列值的出現次數來生成資料分佈描述的。我們舉一個例子如下所示：我們建立一張表表t1，其中包含一個skew列，我們規定如下資料填充策略：skew=1出現一次，skew=2出現兩次......skew=80出現80次。因此程式碼如下：

        create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects 
where rownum <= 5000
)
select

v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= 80
and v2.id <= 80
and v2.id <= v1.id
order by 
v2.id,v1.id;

          收集生成frequency histograms：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 120'
);
end;
/

        收集後查詢user_table_histograms：
select
endpoint_number, endpoint_value
from
user_tab_histograms
where
column_name = 'SKEW'
and table_name = 'T1'
order by
endpoint_number;

        結果如下：

        ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 1
3 2
6 3
10 4
15 5
21 6
..................
..................
..................

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
3081 78
3160 79
3240 80

我們可以看出頻率直方圖對t1裡每一個distinct都保留了一行（所以才說frequency histograms是隻能用在distinct key <255的表上，因為histograms的最大bucket數是254）從這個輸出裡面我們可以看到等於1的值有一個，等於1和2的值有3個（因此等於2的值有2個），等於1/2/3的值有6個。。。。。。因此從這個角度來說，我們常常把frequency histograms稱為累計的frequency histograms。

在Oracle 10GR2之前用dbms_stats來收集統計資訊的時候，你會發現如果你使用的bucket是80，你不會得到一個frequency histograms的統計資訊，而會得到一個height balanced histograms的統計資訊，因此只能使用analyze命令的for all columns size 80來得到frequency histograms的統計資訊。經過多次實驗你會發現對於表t1，如果你想得到frequency histograms，你的bucket至少得設定為107，這個問題在Oracle10GR2後得到了修正，但是因為histograms的最大bucket為254個，所以對於distinct key是大於180的話，用dbms_stats還是永遠得不到frequency histograms的，這時候必須使用analyze。或者自己來生成統計資訊並且在生成後更新資料字典。

接下來我們看一下頻率直方圖對於查詢語句成本的影響。在討論這個話題前我們要先明晰一個概念——查詢基數，所謂查詢基數可以簡單的理解為一個查詢語句將要預計返回的查詢結果的行數，計算基數的基本公式為：總記錄數*選擇率，對於選擇率的計算比較複雜，不同情況下回採用不同的計算方法，但通常都會參照表的num_distinct值，在有些使用繫結變數的情況下甚至直接用1/num_distinct值來作為選擇率。在得到基數後最佳化器會使用基數來生成查詢成本，因此基數對於查詢成本來說非常重要。因此按照對於SKEW列的不同過濾條件會生成不同得基數如下所示：

       Select * from t1 where SKEW=40;該語句基數會得到40，因為SKEW=40的值共出現了40次；

       Select * from t1 where SKEW between 1 and 2; 該語句基數會得到3, 因為SKEW=1和2 的值共出現了3次；

       Select * from t1 where SKEW=40.5; 該語句基數會得到1，因為SKEW=40.5的值不存在。CBO在我們認為是0行的地方統一的看作是1行，實際上除非你的條件裡面加入1=0這樣的條件，否則CBO一般是不允許cardinality為0的。

        Select * from t1 where SKEW between 20.5 and 24.5;對於t1表該語句將會得到與Select * from t1 where SKEW between 21 and 24;相同的基數。

        總結如下：

        .基本查詢基數會採用作為過濾條件列的不同值數量作為基數；

        .對於繫結變數如果對於等值比較如：column=:bind的選擇率會直接使用1/num_distinct值，然後在用選擇率*資料行數得到基數，對於範圍比較的繫結變數，選擇率會固定為5%，如使用

column between :bind1 and :bind2那麼選擇率將等於5%*5%=0.25%；

.如果生成直方圖的關鍵列值作了修改發生了變化，那麼要重新生成直方圖，否則得到的資料描述將會是過時的；

.對於頻率直方圖我們可以看出，對於走出最大值/最小值範圍的區間謂詞CBO會得到更好的結果；

（2）、高度均衡直方圖：

     當我們給出的bucket數目不夠大的時候，Oracle會以height balanced的方式記錄histograms，也就是按照buckets的值把所有的資料平分，如果bucket是50，就把所有的資料平分為50等份，再告訴我們處於每個邊界的值。如下所示：

        create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects 
where rownum <= 5000
)
select

v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= &m_demo_size
and v2.id <= &m_demo_size
and v2.id <= v1.id
order by 
v2.id,v1.id
;

create index t1_i1 on t1(skew);

然後我們以bucket為75建立histograms資訊。
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 75'
);
end;
/

 查詢histograms的資訊：

        ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1
1 9
2 13
3 16
4 19
5 21
..................
..................
..................
62 73
64 74
65 75
67 76
69 77
71 78
73 79
75 80

59 rows selected.

發現查詢user_tab_histograms只有59行輸出，在仔細看看發現Oracle確實是產生了75個bucket，因為ENDPOINT_NUMBER的最大值是75，只不過在記錄統計的資訊Oracle進行了壓縮，省略了一些bucket的輸出。上面的輸出其實可以展開成如下形式：
    ENDPOINT_NUMBER ENDPOINT_VALUE 
    60 72 
    61 73 
    62 73 
    63 74 
    64 74 
    65 75 
    66 76 
    67 76 
    68 77 
    69 77 
    70 78 
    71 78 
    72 79 
    73 79 
    74 80 
    75 80

在進一步解釋其他關於histograms的資訊之前，我們要先介紹一個資料density的概念。Density的出現就是為了分析高頻率出現的值的影響。沒有histograms資訊的時候，DENSITY永遠等於1/NUM_DISTINCT，但是當我們統計了histograms之後，DENSITY就會發生改變。

2.1 DENSITY的計算方法如下：

如果想計算一張表的DENSITY，首先執行下列語句收集資訊：

select min(minbkt),

         maxbkt,

         substrb(dump(min(val), 16, 0, 32), 1, 120) minval,

         substrb(dump(max(val), 16, 0, 32), 1, 120) maxval,

         sum(rep) sumrep,

         sum(repsq) sumrepsq,

         max(rep) maxrep,

         count(*) bktndv,

         sum(case

             when rep = 1 then

              1

             else

              0

           end) unqrep

        from (select val,

               min(bkt) minbkt,

               max(bkt) maxbkt,

               count(val) rep,

               count(val) * count(val) repsq

          from (select

                

                 目標列 val, ntile(10) over(order by目標列) bkt

                  from 目標表名 t

                 where目標列is not null)

         group by val)

         group by maxbkt

order by maxbkt;

透過上面收集的資訊計算densitiy：

（1）、基本公式：

   (Sum(sumrepsq)-sum(maxrep(i)*maxrep(i))/(sum(sumrep)*(sum(sumrep)-sum(maxrep(i))))

注：i表示只有某些特定的行從查詢結果中選出來計算maxrep值，這些行必須滿足條件：

maxbkt>min(minbkt)+1

或者

min(val)=max(val)

（2）、簡化公式（在沒有高頻值出現的情況下使用）：

    Sum(sumrepsq)/(sum(sumrep)*sum(sumrep))

 

2.2 使用高度均衡直方圖成本計算方法：

在知道了densitiy的概念後我們再回頭關注我們的表t1的histograms資訊，我們尤其關注一下ENDPOINT_VALUE=75的情況，之所以關注75是因為我們發現75周圍的數值（比如74）都在輸出當中出現了多次，只有75只出現了一次，我們自己知道75其實出現的次數應該是不比74在表裡出現的次數少的，但不巧的是75在統計Histograms的時候處在了一個特殊的位置，如下圖所示：

 

這種不巧會導致Oracle使用不同的機制來計算skew=75的基數，在oracle看來當把histograms資料完全展開成上面查詢結果的形式後，凡是在histograms資訊裡面出現2次或2次以上的資料都是常見的資料，叫做popular的資料，只出現一次或沒出現的資料都是un-popular的資料。

對於popular的資料，對於=的條件，計算基數的公式是：
基數=總行數*（出現次數/bucket總數）

比如我們上面的74的基數= 3240 * （2/75) = 86.4，和我們下面的實驗相符：

SQL> select count(*) from t1 where skew=74;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=86 Bytes=258)

對於un-popular的資料，對於=的條件，計算基數的公式是：
總行數*DENSITY，這裡就是3240*0.013885925=44.99：

SQL> select count(*) from t1 where skew=75;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=45 Bytes=135)

 

range scan的公式是：

如果不跨整個的bucket:
選擇率 = (required range) / (high value - low value) + 2 * density
基數=每個bucket的行數*選擇率

如果跨整個的bucket：
選擇率= (required range) / (high value - low value) + 2 * density + 整個的bucket的個數
基數=每個bucket的行數*選擇率

       我們一個例項來說明：

       create table t1 
as

select

3000 + trunc(2000 * dbms_random.normal) n1,
lpad(rownum,10) small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000;

         

          insert into t1 
select
500 * (1 + trunc((rownum-1)/500)),
lpad(rownum,10),
rpad('x',100)
from
t1;

commit;

 

begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 250'
);
end;
/

 

histograms的資訊大致如下：
.......
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
8 -120
9 17
10 117
11 251
12 357
13 450
19 500
20 520
21 598

22 670

首先如果不跨整個的bucket:
select 
small_vc
from t1
where n1 between 100 and 200;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=63 Bytes=945)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=63 Bytes=945)

按照公式計算：
選擇率 = (required range) / (high value - low value) + 2 * density =
(200–117)/(251-117) + (117-100)/(117-17) + 2 * 0.000177746 =
0.619403 + 0.17 + .000355486 =
0.789047508

基數 = 選擇率 * number of rows IN A BUCKET =
0.789047508 * 80 = 63.1238

 

如果跨多個bucket：
select 
small_vc
from t1
where n1 between 400 and 600;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=685 Bytes=10275)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=685 Bytes=10275)

按照公式計算：
選擇率 = (required range) / (high value - low value) + 2 * density + 整個的bucket的個數

該查詢中共垮了從13-21的8個bucket，因此計算結果如下：
(450 - 400) / (450 - 357) + (600 - 598) / (670 - 598) + 2 * 0.000177746 + 8 =
50 / 93 + 2 / 72 + 0.000355486 +8 =
0.537634 + 0.0277778 + 0.000355486 + 8=
8.565768

基數 = 基數 * number of rows IN A BUCKET =
8.565867 * 80 = 685.3

六、手工生成頻率直方圖：

        上面我們曾經講過，如果一張表中的不同值超過了180，那麼是無法生成頻率直方圖的，因此在這種情況下我們要手動生成頻率直方圖，手動生成頻率直方圖的程式碼如下：

  declare

  m_statrec   dbms_stats.statrec;

  m_val_array dbms_stats.numarray;

  --  m_val_array    dbms_stats.datearray;

  --  m_val_array    dbms_stats.chararray;    -- 32 byte char max

  --  m_val_array    dbms_stats.rawarray;    -- 32 byte raw max

  m_distcnt number;

  m_density number;

  m_nullcnt number;

  m_avgclen number;

begin

  dbms_stats.get_column_stats(ownname =>目標使用者,

                              tabname => '目標表',

                              colname => '目標列',

                              distcnt => m_distcnt,

                              density => m_density,

                              nullcnt => m_nullcnt,

                              srec    => m_statrec,

                              avgclen => m_avgclen);

  --

  --  Load column information into the two critical arrays

  --

  select 目標列, count(*) bulk collect

    into m_val_array, m_statrec.bkvals

    from 目標表

   group by 目標列

   order by 目標列;

  m_statrec.epc := m_val_array.count;

  --

  --  Should terminate here if the count exceeds 254

  --

  dbms_stats.prepare_column_values(srec    => m_statrec,

                                   numvals => m_val_array);

  select 1 / (2 * count(*)) into m_density from 目標表;

  dbms_stats.set_column_stats(ownname =>目標使用者,

                              tabname => '目標表',

                              colname => '目標列',

                              distcnt => m_distcnt,

                              density => m_density,

                              nullcnt => m_nullcnt,

                              srec    => m_statrec,

                              avgclen => m_avgclen);

end;

該方法透過dbms_stats.get_column_stats包來收集需要建立直方圖的目標表和目標列資訊，然後透過1 / (2 * num_rows)來生成頻率直方圖的density，最後透過dbms_stats.set_column_stats將收集的資訊和計算結果更新到存放統計資訊資料字典中。

在頻率直方圖中有一些特殊的需要注意之處，在頻率直方圖中density始終等於1 / (2 * num_rows)，對於查詢返回的行數來說density非常關鍵。如果直方圖中存在一個特定值一共有X行，但是如果X小於density*（user_tables.num_rows-user_tables_columns.num_nulls），那麼最佳化器將採用後者來取代X。

七、    直方圖與繫結變數：

      我們知道當我們擁有了histograms的統計資訊之後我們就可以使用這些資訊計算我們的選擇率和基數。但是如果我們使用了繫結變數的時候，情況總會有所改變。
    首先，在Oracle9i裡面新引入了bind variable peeking的功能，這個功能我們前面講過，是一個帶繫結變數的SQL第一次parse的時候，讓CBO可以根據繫結的具體的值來決定所要使用的執行計劃，而以後如果遇到同樣的SQL，即使繫結變數的值不一樣，也不會在peek繫結變數的值，而是使用已經生成的計劃。這裡的一個潛在的問題就是如果我們有了histograms資訊，而且我們的資料分佈是一小部分資料的分佈和其他部分的分佈相差很遠，那麼當我們在做bind variable peeking，如果很不幸運的peek到了那一小部分的資料，就會導致以後所有的同樣的SQL都使用了不恰當的執行計劃。

      當然這個bind variable peeking有時候也有意外，那就是如果我們存在shared pool裡的執行計劃資訊或其他相關的資訊由於某種原因失效了或者被age out of shared pool，那當我們再次執行這個SQL的時候，就會重新peek繫結變數的值，從而重新生成計劃。關於執行計劃資訊或其他相關的資訊的失效或age out，可以透過v$sql的reloads和invalidations欄位獲得。

     和繫結變數有關的另一個就是引數cursor_sharing，cursor_sharing這個引數有三個取值：FORCE、EXACT、SIMILAR。

       有時候，很可能是在OLTP的系統中，為了最大限度的減少SQL PARSE的消耗，讓類似的SQL可以儘可能的重用，我們會考慮設定cursor_sharing為force。當cursor_sharing被設定為force的時候，最佳化器會用系統指定的繫結變數來替代SQL裡面所有的literal constants，然後以此為基礎判斷我們的shared pool裡面是不是有可以重用的cursor。按照我們上面的討論，設定cursor_sharing為force對histograms影響最大的，因為系統指定的繫結變數替換後很可能與histograms收集的資料分佈不符。

       這個問題可以有兩個解決辦法,一是在我們認為影響會很到的SQL裡面加上hint ，這回告訴CBO對於這個SQL採用cursor_sharing=exact的策略。另一個解決方法是設定cursor_sharing=similar，按照Oracle文件的說法，設定cursor_sharing為similar也會首先把SQL裡的literals替換為繫結變數，並且也會在第一次分析SQL的時候做bind variable peeking，但是當以後重新執行類似的SQL的時候，CBO會檢視如果發現新的繫結變數會影響到執行計劃（當然，之所以會產生不同的執行計劃往往是因為存在histograms），就會重新生成執行計劃。經過一些實驗，我們可以發現，當設定cursor_sharing=similar的時候，如果我們的條件是range scan或等於的條件，並且條件涉及的列上有histograms資訊的時候，CBO會在分析SQL的時候對繫結變數做檢查，如果發現新的繫結變數有可能影響SQL的執行計劃，則會重新評估並生成新的計劃。

     但是往往我們在最佳化系統的一個方面的時候會導致其他方面的問題，cursor_sharing=similar就是一個很典型的例子，當我們這樣的設定的時候，首先最佳化器的壓力會變大，因為CBO要做很多的重新最佳化。更嚴重的問題在於cursor_sharing=similar會導致同樣的SQL（除了繫結變數的值不一樣之外）在library cache裡面擁有很多不同的執行計劃，因為我們知道一個SQL下面的所有執行計劃都是被一個latch保護的，所以cursor_sharing=similar會導致更嚴重的latch 爭用。因此當我們使用cursor_sharing=similar的時候，除非必要，無需統計histograms資訊，因為我們要保證我們為了解決一個問題不會導致其他的更嚴重的問題。

八、    使用直方圖的注意事項：

（1）、Oracle不能保證在join中可以充分使用histograms，如果你有一個列col，Oracle只有你明確的指定了col operation(,=,in,between等等) 常量（這個常量當然也可以是透過bind variable peeking獲得的）的時候，才會使用histograms。如下所示：

select
t1.v1, t2.v1
from
t1,
t2
where
t1.n2 = 99
and t1.n1 = t2.n1;

如果我們在t1和t2上都有histograms，Oracle會在t1.n2=99這個條件上使用histograms，但Oracle不能在and t1.n1 = t2.n1這個條件上使用histograms，當然如果我們的條件改成：
t1.n2 = 99
and t1.n2 = t2.n1
這時候histograms就可以使用了，因為Oracle會自己把這個SQL改寫成：
t1.n2 = 99
and 
t2.n1 = 99

（2）、Oracle在分散式查詢中，即透過DBLINK查詢遠端資料庫表，此時不會使用遠端表的histograms資訊

（3）、在建立高度均衡直方圖時的例外情況：

即使在粒度最細的情況下，一個桶也只能大約表示某個值所對應行的1/1250（0.4%）。如果表中存在大於250個不同值時，那麼直方圖肯定會漏掉一些值。實際情況會更糟糕，如果某一行可能會跨接近兩個桶（行數目的0.8%），是無法被Oracle認定為高頻出現值的。更糟糕的是，如果存在某個出現頻率非常高的值所跨越的桶數超過了平均數，此時就會導致Oracle遺漏掉許多本來會被認定為高頻出現的資料行。因此確切估計桶數和選擇建立直方圖列對於高度均衡直方圖來說非常重要