【DB筆試面試636】在Oracle中，如何收集直方圖資訊？在收集直方圖時有哪些注意事項？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，如何收集直方圖資訊？在收集直方圖時有哪些注意事項？

♣

答案部分

默認情況下，資料庫會為列收集基本統計資訊，但不會收集直方圖資訊。Oracle通過指定DBMS_STATS的METHOD_OPT參數來創建直方圖。METHOD_OPT參數可以接受如下的輸入值：

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]  FOR COLUMNS [size_clause] column|attribute [size_clause] [,column|attribute [size_clause]...]  

其中的size_clause必須符合如下的格式：

SIZE {integer | REPEAT | AUTO | SKEWONLY}  

含義如下所示：

l SKEWONLY：只對數據分布不均衡的列收集直方圖統計資訊。

l REPEAT：只對己經有直方圖統計資訊的列收集直方圖統計資訊。

l AUTO：讓Oracle自行決定是否對目標列收集直方圖統計資訊，以及使用哪種類型的直方圖。

l integer：直方圖的Bucket的數量，必須是在1~254的範圍內，1表示刪除該目標列上的直方圖統計資訊。

METHOD_OPT參數的默認值為「FOR ALL COLUMNS SIZE AUTO」，「FOR ALL COLUMNS SIZE 1」表示刪除所有列直方圖統計資訊。下面是一些常用的收集方法：

l 對T表上所有有索引的列以自動收集的方式收集直方圖：FOR ALL INDEXED COLUMNS SIZE AUTO

l 對T表上的列A和列B以自動收集的方式收集直方圖：FOR COLUMNS SIZE AUTO A B

l 對T表上的列A和列B收集直方圖統計資訊，同時指定BUCKET數量均為10：FOR COLUMNS SIZE 10 A B

l 對T表上的列A和列B收集直方圖統計資訊，同時指定列A的BUCKET數量為10，列B的BUCKET數量為5：FOR COLUMNS A SIZE 10 B SIZE 5

l 只刪除表T上列A的直方圖統計資訊：FOR COLUMNS A SIZE 1

l 刪除表T上所有列的直方圖統計資訊：FOR ALL COLUMNS SIZE 1

如果需要刪除某個列SAL的直方圖資訊，在Oracle 10g中可以通過設置「METHOD_OPT=>'FOR COLUMNS SAL SIZE 1'」，但這卻得再次收集表的統計資訊，十分不合理，所以，在Oracle 11g中，有如下方法可以直接刪除直方圖資訊：

EXEC DBMS_STATS.DELETE_COLUMN_STATS(USER,'T_ST_20170604_LHR','SAL',COL_STAT_TYPE => 'HISTOGRAM');  

其中，COL_STAT_TYPE默認為ALL，表示刪除列的基本統計資訊和直方圖資訊。

SKEWONLY的示例如下：

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收集統計資訊時默認是不收集表的索引的統計資訊的。

關於直方圖的一些注意事項：

①　對於超過32個字元的字元型列，超出的那一部分無法在直方圖中體現，這種先天性的缺陷會直接影響CBO對相關文本型欄位的可選擇率及返回結果集的Cardinality的評估。

②　數字和日期在直方圖上被精確表示。

③　如果目標列的數據是均勻分布的（例如，主鍵列、唯一索引列），那麼就不需要對這些列收集直方圖統計資訊。

④　對於那些從來沒有在WHERE條件中出現過的列，無論其數據分布是否均勻，都無須對這些列收集直方圖統計資訊。

⑤　如果某個列從未在WHERE條件中出現過，那麼SYS.COL_USAGE$中就不會有這個列的使用記錄，那麼Oracle在以默認方式自動收集統計資訊時就不會收集這個列的直方圖統計資訊。

⑥　如果目標列的DISTINCT值的數量和目標表的記錄數據量相同，即使該目標列在SYS.COL_USAGE$中有使用記錄，那麼Oracle在自動收集直方圖統計資訊的時候也不會對該列收集直方圖統計資訊。

⑦　Oracle是怎麼來判斷某列的數據分布是否是傾斜的呢？Oracle採用了一種很簡單的方法來判斷某列是否傾斜，就是判斷目標列的DISTINCT值的數量是否和目標表的記錄數量相同，如果相同，那麼Oracle就認為該列的數據分布不是傾斜的，否則就是傾斜的。

⑧　如果目標列的DISTINCT值的數量和目標表的數據量相同（即數據分布不傾斜），即使該目標列在SYS.COL_USAGE$中有使用記錄，那麼Oracle在自動收集直方圖統計資訊的時候也不會對該列收集直方圖統計資訊。

⑨　「在手工收集直方圖統計資訊的時候，如果手工指定的Bucket的數量等於目標列的DISTINCT值的數量，且這個值是小於等於254的話，那麼Oracle此時收集的直方圖統計資訊的類型應該是FREQUENCY」——這個結論成立的前提條件是該列的數據分布是傾斜的。

本文選自《Oracle程式設計師面試筆試寶典》，作者：小麥苗