233
技術社區[雲棲]
關於直方圖統計信息的兩個有趣的知識點
本文講的是關於直方圖統計信息的兩個有趣的知識點，有朋友問了我如下這樣一個問題，最後的解決過程挺有意思的，讓我發現了直方圖統計信息裏我之前沒有注意到的兩個知識點，這裏跟大家分享一下。
問題
數據庫的版本是11.2.0.3：
創建一個測試表T1：
SQL> create table t1 as select *
from dba_users;
Table created
從如下查詢結果中我們可以看到，表T1的OBJECT_ID是104192：
SQL> select object_id from dba_objects
where owner=’SCOTT’
and object_name=’T1′;
OBJECT_ID
——————-
104192
表T1的列user_id所對應的INTCOL#是2：
SQL> select name,intcol# from sys.col$
where obj#=104192
and name=’USER_ID’;
NAME INTCOL#
—————— ———-
USER_ID 2
從如下結果裏可以看到，SYS.COL_USAGE$現在還沒有列USER_ID的使用記錄：
SQL> select obj#,intcol#,
equality_preds
from sys.col_usage$
where obj#=104192;
OBJ# INTCOL# EQUALITY_PREDS
我們現在來使用一下列USER_ID：
SQL> select count(*) from t1
where user_id=5;
COUNT(*)
—————–
1
使用完後，我們發現SYS.COL_USAGE$還是沒有列USER_ID的使用記錄：
SQL> select obj#,intcol#,
equality_preds
from sys.col_usage$
where obj#=104192;
OBJ# INTCOL# EQUALITY_PREDS
———- ———- ————–
這個是正常的，這裏不是沒有列USER_ID的使用記錄，是已經有了但隻是還沒有被持久化到SYS.COL_USAGE$中，這裏需要我們手工執行一下dbms_stats.gather_table_stats，這樣就能將USER_ID的使用記錄flush到SYS.COL_USAGE$中了，然後我們就能看到了：
但現在的問題是無論我們怎麼執行dbms_stats.gather_table_stats，列user_id上的直方圖統計信息就是沒有（這也是那位朋友問的問題）：
這裏除非我們手工指定user_id列所用的bucket的數量：
手工指定了直方圖統計信息的bucket的數量為39後，明明列user_id的distinct值的數量也是39，為什麼這裏直方圖的類型居然是HEIGHT BALANCED？按道理講應該是FREQUENCY啊！
當看到上述測試結果的時候，我意識到一定是什麼地方出了問題。
因為上述現象的出現已經顛覆了我之前對直方圖統計信息的如下兩個認識：
1、我原先一直以為如果METHOD_OPT的值是默認的“FOR ALL COLUMNS SIZE AUTO”的話，那麼隻要SYS.COL_USAGE$中有目標列的使用記錄，則Oracle在自動收集直方圖統計信息的時候就會去收集該列的直方圖統計信息；
2、在手工收集直方圖統計信息的時候，如果我手工指定的bucket的數量等於目標列的distinct值的數量，且這個值是小於等於254的話，那麼Oracle此時收集的直方圖統計信息的類型應該是FREQUENCY。
到底是什麼地方出了問題？
我們來複習一下Oracle關於自動收集直方圖統計信息的定義：
Oracle在“SIZE Clause in METHOD_OPT Parameter of DBMS_STATS Package (Doc ID 338926.1)”中明確指出，METHOD_OPT的值中的AUTO的含義為如下所示：
AUTO: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
這裏的“workload of the columns”指的應該就是目標列是否在SYS.COL_USAGE$中有使用記錄。
注意到Oracle這裏還提到了另外一個條件——“based on data distribution”（這也是我之前沒有注意到的條件），但這裏的具體含義是什麼？
“based on data distribution”直譯過來就是目標列數據的分布。說白了就是目標列的數據分布確實得是傾斜的，隻有滿足這個前提條件，再加上該目標列在SYS.COL_USAGE$中有使用記錄，Oracle在自動收集直方圖統計信息的時候才會對該列收集直方圖統計信息。
Oracle是怎麼來判斷某列的數據分布是否是傾斜的呢？
Oracle采用了一種很簡單的方法
就是判斷目標列的distinct值的數量是否和目標表的數據量相同，如果相同，Oracle就認為該列的數據分布不是傾斜的，否則就是傾斜的。
如果目標列的distinct值的數量和目標表的數據量相同，即使該目標列在SYS.COL_USAGE$中有使用記錄，Oracle在自動收集直方圖統計信息的時候也不會對該列收集直方圖統計信息。
搞清楚了上述知識點，那位朋友問的問題自然就有答案了——對於表T1的列user_id而言，其distinct值的數量和表T1的數據量相同，所以這裏即使user_id在SYS.COL_USAGE$中有使用記錄，Oracle在自動收集直方圖統計信息的時候也不會對user_id收集直方圖統計信息：
現在我們來驗證一下上述理論，往表T1中插入一條記錄，使得user_id的distinct值的數量小於表T1的數據量，這樣當我們再次對表T1收集統計信息的時候，user_id列的直方圖統計信息應該就有了。
先把之前對user_id列手工指定bucket數量收集的直方圖統計信息刪掉：
對表T1插入一條user_id列的值和現有值重複的記錄：
SQL> insert into t1 select *
from t1 where user_id=5;
1 row inserted
SQL> commit;
Commit complete
現在user_id列的distinct值的數量已經小於表T1的數據量了：
此時對表T1再次收集統計信息：
SQL> exec dbms_stats.
gather_table_stats
(ownname=>’SCOTT’,
tabname=>’T1′,estimate_percent=>100);
PL/SQL procedure successfully completed
從如下查詢結果裏我們可以看到，現在user_id列上終於有了直方圖統計信息，且其類型就是FREQUENCY，這就和我們以前的認知匹配上了，同時也驗證了我們剛才的分析結論：
再次刪除user_id列上的直方圖統計信息：
我們再次以手工指定bucket數量的方式收集user_id列上的直方圖統計信息：
SQL> exec dbms_stats.gather_table_stats
(ownname=>’SCOTT’,tabname=>’T1′,
method_opt=>’for columns size 39 USER_ID‘,estimate_percent=>100);
PL/SQL procedure successfully completed
從如下查詢結果我們可以看到，現在user_id列上的直方圖統計信息的類型已經不是之前的HEIGHT BALANCED了，而是變成了FREQUENCY：
這說明我們之前的認識（在手工收集直方圖統計信息的時候，如果我手工指定的bucket的數量等於目標列的distinct值的數量，且這個值是小於等於254的話，那麼Oracle此時收集的直方圖統計信息的類型應該是FREQUENCY）成立的前提條件是該列的數據分布是傾斜的。
總結
通過這篇文章，我們介紹了如下兩個關於直方圖統計信息的有趣知識點：
1、如果目標列的distinct值的數量和目標表的數據量相同，即使該目標列在SYS.COL_USAGE$中有使用記錄，Oracle在自動收集直方圖統計信息的時候也不會對該列收集直方圖統計信息；
2、在手工收集直方圖統計信息的時候，如果我手工指定的bucket的數量等於目標列的distinct值的數量，且這個值是小於等於254的話，那麼Oracle此時收集的直方圖統計信息的類型應該是FREQUENCY——這個結論成立的前提條件是該列的數據分布是傾斜的。
原文發布時間為：2017-09-27
作者： 崔華
本文來自雲棲社區合作夥伴“數據和雲”，了解相關信息可以關注“數據和雲”微信公眾號
最後更新：2017-09-27 15:34:31