oracle直方圖

直方圖概述：

直方圖是一種統計學上的工具，并非Oracle專有，通常情況下它會表現為一種幾何圖形表，這個圖形表是根據從實際環境中所收集來的被管理對象某個方面的質量分布情況的數據所繪制成的，通常會畫成以數量為底邊，以頻度為高度的一系列連接起來的矩形圖，因此直方圖在統計學上也稱為質量分布圖。
在Oracle中直方圖是一種對數據分布質量情況進行描述的工具。它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪制數據的分布情況，以便能夠指導優化器根據數據的分布做出正確的選擇。在某些情況下，表的列中的數值分布將會影響優化器使用索引還是執行全表掃描的決策。當where 子句的值具有不成比例數量的數值時，將出現這種情況，使得全表掃描比索引訪問的成本更低。這種情況下如果where 子句的過濾謂詞列之上有一個合理的正確的直方圖，將會對優化器做出正確的選擇發揮巨大的作用，使得SQL語句執行成本最低從而提升性能。
在分析表或索引時，直方圖用于記錄數據的分布。通過獲得該信息，基于成本的優化器就可以決定使用將返回少量行的索引，而避免使用基于限制條件返回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上構建直方圖。
構造直方圖最主要的原因就是幫助優化器在表中數據嚴重偏斜時做出更好的規劃 。例如，如果一到兩個值構成了表中的大部分數據(數據偏斜)，相關的索引就可能無法幫助減少滿足查詢所需的I/O數量。創建直方圖可以讓基于成本的優化器知道何時使用索引才最合適，或何時應該根據WHERE子句中的值返回表中80％的記錄。
通常情況下在以下場合中建議使用直方圖：
1、當Where子句引用了列值分布存在明顯偏差的列時：當這種偏差相當明顯時，以至于WHERE 子句中的值將會使優化器選擇不同的執行計劃。這時應該使用直方圖來幫助優化器來修正執行路徑。（注意：如果查詢不引用該列，則創建直方圖沒有意義。這種錯誤很常見，許多 DBA 會在偏差列上創建柱狀圖，即使沒有任何查詢引用該列。）
2、當列值導致不正確的判斷時：這種情況通常會發生在多表連接時，例如，假設我們有一個五項的表聯接，其結果集只有10行。Oracle 將會以一種使第一個聯接的結果集（集合基數）盡可能小的方式將表聯接起來。通過在中間結果集中攜帶更少的負載，查詢將會運行得更快。為了使中間結果最小化，優化器嘗試在SQL執行的分析階段評估每個結果集的集合基數。在偏差的列上擁有直方圖將會極大地幫助優化器作出正確的決策。如優化器對中間結果集的大小作出不正確的判斷，它可能會選擇一種未達到最優化的表聯接方法。因此向該列添加直方圖經常會向優化器提供使用最佳聯接方法所需的信息。

如何使用直方圖：

為表的所有索引字段建立直方圖：

exec dbms_stats.gather_table_stats('HDC_DAG','EB_JBXX',method_opt => 'for all indexed columns size 254',cascade => true);

為指定字段建立直方圖：

exec dbms_stats.gather_table_stats('HDC_DAG','EB_JBXX',method_opt => 'for columns size 100 xb',cascade => true);

exec dbms_stats.gather_table_stats('HDC_DAG','DA_GRJBXX', estimate_percent => dbms_stats.auto_sample_size,method_opt => 'for columns xm size auto ',cascade => true);

刪除直方圖：

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

exec dbms_stats.delete_column_stats('HDC_DAG','EB_JBXX','JLLYXTBH',col_stat_type => 'HISTOGRAM');

查看列的直方圖存儲桶(bucket)信息：

select * from user_histograms  t where t.TABLE_NAME  ='EB_JBXX' and t.COLUMN_NAME ='XM';

直方圖的分析是建立在索引的分析上

創建直方圖的考慮因素：

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