Histogram Investigation（轉自kamus）

這段時間有客戶的10gR2資料庫經常遭遇執行計劃不穩定的現象，應該是直方圖資訊（Histogram）+繫結變數窺視（Bind Variable Peeking）造成的問題，藉此分享一下直方圖的使用經驗，但是我非常希望能聽到針對本文的不同聲音。測試環境Oracle 11.2.0.1 for Windows X64。

建立一個測試表。

這樣我們生成了一張表T，其中有一萬行記錄。
N1欄位可以認為是主鍵，有10000個distinct值（1-10000），這是一個資料分佈均勻的欄位；
N2欄位有101個distinct值，除了第一個值0和最後一個值101之外，其它的值都有100行記錄，我們可以認為這是一個資料分佈均勻的欄位；
N3欄位有10000個值都是xxxx，實際上這也仍然是一個分佈均勻的欄位。

對於這三種型別的欄位，我們都不應該去收集直方圖資訊，因為沒有意義。那麼直方圖資訊之所以需要存在的根本意義在哪裡？

一. 我們何時該做
直方圖究其根本實際上就是一個資料分佈的圖示，這個圖示是為了在生成SQL執行計劃的時候給Oracle的CBO更多的資訊，換句話說，就是當在where條件中的某些列可能由於列值的不同而希望CBO制定出不同的執行計劃時，我們需要直方圖。

反過來說，什麼情況下我們不需要直方圖呢？或者說直方圖的存在是沒有意義的呢？
1. 當此列不用於查詢時，也就是這個欄位永遠不會出現在where條件中（注意：即使是用於表連線那也算是出現在where條件中）。
2. 當此列無論給予什麼比較值，我們都希望永遠是一種執行計劃時。上面例子中的3個欄位都屬於這種情況。
對於N1或者N2出現在where條件中，我們希望永遠是用該列上的索引掃描（當然需要在該列上先建立索引），對於N3出現在where條件中，我們希望永遠是全表掃描。這樣的執行計劃的制定，只要有表級別的統計資訊就足夠了，直方圖資訊的出現不但不會對制定正確的執行計劃有幫助，甚至會出現奇怪的現象導致執行計劃不穩定。

二. 我們可以怎樣做
那麼我們繼續看一下收集直方圖資訊的方法，眾所周知，是需要使用DBMS_STATS.GATHER_TABLE_STATS儲存過程的，並且是由這個儲存過程中的method_opt引數決定的。可以由以下幾種選擇。

method_opt=>’for columns size skewonly [column_name]‘
什麼是SKEWONLY？先看一下Oracle官方文件中的定義。

Oracle determines the columns to collect histograms based on the data distribution of the columns.

也就是要看列上的資料分佈，那麼按照我們前面提到的，T表中的三列資料分佈都是均勻的，並不應該收集任何列上的直方圖資訊，實際呢？

可以看到在N2上收集了直方圖資訊，收集了101個bucket，而N3上則收集了1個bucket的直方圖資訊（實際上也只能收集一個，因為只有一個distinct值xxxx）。收集直方圖資訊是耗費資源的，因此SKEWONLY不推薦使用。實際上經歷了這麼多版本，SKEWONLY的演算法一直很奇怪，我沒有找到一份文件描述Oracle到底是如何定義SKEWONLY的收集憑據的。

method_opt=>’for columns size auto [column_name]‘
什麼是AUTO？再看一下Oracle官方文件中的定義。

Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

也就是在SKEWONLY的基礎上額外增加了workload限制，何謂workload？實際上就是我們前面提到的是否在where條件中出現。如果一個列從來沒有出現在where條件中，Oracle就認為是沒有workload的，那麼即使此列上的資料被認為是SKEW的，也不會收集直方圖資訊，只有出現過至少一次，Oracle才會收集。

因此AUTO也是有問題的，N2欄位上的直方圖資訊仍然會收集，這是我們不需要的。注意，在Oracle10g之後的自動統計資訊收集任務中，預設的是FOR ALL COLUMNS SIZE AUTO，這是很有問題的，因此即使是不禁用自動統計資訊收集，也應該透過DBMS_STATS.SET_PARAM儲存過程來修改該預設值。通常我們經歷的執行計劃莫名其妙改變，很多場合都是因為Oracle10g之後的這個統計資訊自動收集任務導致的。

method_opt=>’for columns size repeat [column_name]‘
只有在已有直方圖資訊的列上再次收集直方圖資訊。這是我們推薦設定為預設值的方式。具體的執行方法見後文。

method_opt=>’for columns size 1 [column_name]‘
size 1將刪除列上的直方圖資訊，同樣也意味著不收集。

method_opt=>’for all columns size 30′
size自定義大於1的數值，最大為254，如例表示要求收集30個bucket的直方圖資訊。

那麼現在到了最重要的部分，對於一個資料庫系統，我們到底應該如何收集直方圖資訊呢？

三. 我們應該怎樣做
首先我們要明確直方圖資訊是有存在的必要的，但是隻應該存在在那些應該要存在的列上，Oracle知道哪些列上應該存在嗎？Oracle一直在致力於想知道，可惜的是現在做的仍然不夠好，全部交給Oracle去做的話（SIZE SKEWONLY或者SIZE AUTO），可能得到的結果就是該收集的沒收集，不該收集的收集了一堆。只有我們的DBA才最知道哪些列上應該收集直方圖，這實際上已經遠遠不僅僅是技術問題了，而是一個業務問題，因此DBA應該去熟悉業務，DBA應該知道自己的應用的資料分佈特點，應該知道哪些列會常被用在where條件中。

直方圖資訊收集是一個漫長而持續的過程，沒有一蹴而就的方法，也不可能一勞永逸。

我們推薦的方法是：
1. 第一次收集統計資訊時，設定method_opt=>FOR ALL COLUMNS SIZE 1，這意味刪除所有列上的直方圖。
2. 在測試階段或者在真實生產環境中，在調優SQL的過程中，DBA將會逐漸得知每個需要直方圖資訊的欄位，在這些欄位上人工收集統計資訊，method_opt=>FOR COLUMNS SIZE AUTO [COLUMN_NAME]，如果你能夠明確知道應該收集多少個bucket而手工指定SIZE值那更好。保留收集所有這些欄位的指令碼，以備資料庫系統升級或者遷移時候使用。
3. 在每次資料分佈有所變化的時候，更新統計資訊，使用method_opt=>FOR ALL COLUMNS SIZE REPEAT，這樣只會收集已經存在了直方圖資訊的欄位。

重複2，3步驟，直到系統穩定。這是一個可控的步驟，只有可控，才可能避免不可預知的錯誤。

[備註1] dba_tab_col_statistics.histogram欄位值的含義。
該欄位可能包含三個值：NONE，FREQUENCY或者HEIGHT BALANCED。

NONE：就是沒有直方圖

FREQUENCY：當該列的distinct值數量<=bucket數量時，為此型別。對於此型別而言，在dba_tab_histograms檢視中的會存在相當於distinct值數量的記錄，該檢視的ENDPOINT_VALUE欄位記錄了這些distinct值，而ENDPOINT_NUMBER欄位則記錄了到此distinct值為止總共有多少條記錄，注意這是一個累加值，實際上我們可以用一條記錄的ENDPOINT_NUMBER減去上一條記錄的ENDPOINT_NUMBER來獲知對應於本記錄的ENDPOINT_VALUE值有多少條記錄。我知道這聽上去很拗口，可能也有些難於理解，但是請儘量去理解吧。

HEIGHT BALANCED：當該列的distinct值數量>bucket數量時，為此型別。比如我們上面的例子收集了SIZE 30的直方圖資訊，對於N1，N2欄位來說都超過了30個distinct值，因此為HEIGHT BALANCED型別。此時dba_tab_histograms檢視中的ENDPOINT_NUMBER欄位就不再是表示有多少條記錄了，而僅僅表示bucket編號，SIZE 30的話，就是簡單的0-30（需要31個bucket才可以表示SIZE 30）。主要是在於ENDPOINT_VALUE欄位，實際上是這樣分的，分了30個bucket，現在T表總共是10000條記錄，那麼每個bucket裡面大概會是333條記錄。

第一個ENDPOINT_VALUE的值相當於如下SQL的返回值。

第二個ENDPOINT_VALUE的值相當於如下SQL的返回值。

第三個ENDPOINT_VALUE的值相當於如下SQL的返回值。

依次類推。正是這樣的資訊告訴了Oracle資料的分佈情況，試想一下，如果連續3個bucket的ENDPOINT_VALUE值都10，那麼也就是說至少有2個bucket中的記錄N2欄位都是10，也就是說N2=10的記錄至少有666條，越多的bucket有越多相同的ENDPOINT_VALUE值，就表明資料分佈越不均勻。
有一點需要額外注意的是：如果某幾個bucket的ENDPOINT_VALUE值相同，那麼在檢視中只會記錄最後一個bucket的資訊。

[備註2] dba_tab_col_statistics.density欄位值的含義。
官方文件中的解釋“Density of the column”就跟沒說一樣。Density的含義是“密度”。

還是看上面的例子，當HISTOGRAM=NONE的時候，N1欄位的密度是.0001，一萬分之一，1萬條記錄在某欄位上有1萬個distinct值，那麼該欄位的密度就是一萬分之一；N3欄位的密度是1，1萬條記錄在某欄位上都是相同的，只有1個distinct值，那麼該欄位的密度就是1。到這裡應該可以比較形象的理解Density了。那麼對於N2欄位來說呢，密度是.00990099，很簡單了，1萬條記錄裡面有101個distinct值，10000/101/10000=.00990099。

DENSITY值是會影響CBO判斷執行計劃的，而回到前文的例子，我們比較一下有直方圖和沒有直方圖時候的同一列的DENSITY值，就會發現很要命的事情，直方圖很大地影響到了密度值，目前還沒有更科學的方法去研究直方圖是如何影響密度的，從而又會對CBO的判斷產生多大影響（至少我還沒有研究到），但是至少我們可以知道直方圖只應該存在在必須存在的列上，因為除了不必要的收集會消耗不必要的資源，它有更多不可預知的影響會導致效能問題。