【效能優化】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 (
                         wnname => '',
                         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('user', 'table',cascade=>false, method_opt=>'for columns  size 1');
五,Oracle直方圖的種類：
        Oracle利用直方圖來提高非均勻資料分佈的選擇率和技術的計算精度。但是實際上Oracle會採用另種不同的策略來生成直方圖：其中一種是針對包含很少不同值的資料集；另一種是針對包含很多不同的資料集。Oracle會針對第一種情況生成頻率直方圖，針對第二種情況生成高度均衡直方圖。通常情況下當BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方圖，而當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)數。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-671681/，如需轉載，請註明出處，否則將追究法律責任。