深入理解Oracle長條圖____Oracle

長條圖是一種按資料出現的頻率來進行分類儲存的方法.在oracle中長條圖是用來描述表中列資料的分布情況.每一個sql在被執行前都要經過最佳化這一步驟那麼在最佳化器給出一個最優執行計畫之最佳化器應該要知道sql語句中所引用的底層對象的詳細資料.

長條圖描述的對象包括列中不同值的數量和它們出現的頻率.現在儲存每一個不同值和它出現的頻率是不可行的,特別是對於大表來說列中有上萬個不同值,oracle使用長條圖來儲存關於列中資料分布的有用資訊而且oracle的CBO使用長條圖資訊來計算出一個最優的執行計畫.

CBO與長條圖histograms
從一個行源中評估返回行數所佔的比例這就是選擇率,選擇率在CBO的查詢最佳化中起著重要作用.選擇率的取值範圍是0到1之間.粗略的講,如果滿足謂詞條件的只有少量的行記錄那麼CBO將更喜歡使用索引掃描,如果謂詞條件要從表中擷取大量資料那麼CBO將更喜歡使用全表掃描.比如下面的查詢擷取deptno等於10的所有僱員資訊如果返回少量的記錄查詢將會更傾向於使用索引掃描:
select * from emp where deptno=10;

為了評估選擇率(或者換句話說計算出最優執行計畫),CBO會使用各種形式的統計資訊,配置參數等.以表中列的角度來說,CBO會收集以下統計資訊:
列中不同值的數量也就是NDV
列中的最小值/最大值
列中null值的數量
資料分布或長條圖資訊

在沒有長條圖時最佳化器使用基表中記錄的列中不同值的數量,列中最小值/最大值和列中null值的數量來計算統計資訊.使用這些資訊最佳化器假設資料在列中的最小值和最大值之間是均勻分布的或者說列中每一個不同值的出現次數是相同的.
下面舉列說明.建立一個測試表t1它有10000行記錄,有兩個列,列all_distinct包含不同值的範圍從1到10000.列skew對於前10行記錄的值從1到10,餘下的9990行記錄都是10000.

[oracle@jingyong ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 4 06:05:14 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create table t1 as select rownum all_distinct,10000 skew from dual connect by level < =10000; Table created. SQL> update t1 set skew=all_distinct where rownum< =10; 10 rows updated. SQL> commit;Commit complete.SQL> select skew,count(*) from t1 group by skew order by skew;      SKEW   COUNT(*)---------- ----------         1          1         2          1         3          1         4          1         5          1         6          1         7          1         8          1         9          1        10          1     10000       999011 rows selected.

使用dbms_stata.gather_table_stats來收集統計資訊是產生長條圖是由參數method_opt來控制的method_opt參數的文法是由多個部分組成的.前兩個部分是強制性的:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column...]

method_opt文法中的主要部分控制哪此列將收集列的統計資訊(min,max,ndv,nulls).預設是for all columns,它將會對錶中所有的列(包括隱藏列)收集基本的列統計資訊.

for all indexed columns將只對哪些包含索引的列進收集列統計資訊.

for all hidden columns將只會對哪些虛擬列收集列統計資訊.這意味著在對錶收集統計時真實列是不會產生列統計資訊的.這個值不能用於通常的統計資訊收集.它只能用在當基表列的統計資訊精確收集後在表中建立新的虛擬列.然後對新的虛擬列收集列統計資訊時才使用它.

注意如果列不在統計資訊收集列表中那麼只會收集列的平均長度.

size用來指定長條圖的桶數SIZE {integer | REPEAT | AUTO | SKEWONLY}
auto:基於列的使用資訊(sys.col_usage$)和是否存在資料扭曲來收集長條圖
integer:人為的指定建立長條圖的桶數範圍是1到254,如果size 1意味著不建立長條圖
repeat:只會對已經存在長條圖的列重建長條圖.如果是一個分區表,repeat會確保對在全域層級存在長條圖的列重建長條圖.這是不被推薦的設定的.當前長條圖的桶數將會作為重建長條圖所使用的桶數的最大值.比如,當前長條圖的桶數是5,那麼產生的長條圖最大桶數就是5,說的直白點就是重新整理現有長條圖的列上的統計資訊.
skewonly:對任何資料分布出現傾斜列的自動建立長條圖

現在來對錶t1收集統計資訊但不建立長條圖

SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1');PL/SQL procedure successfully completed.SQL> select column_name,num_distinct,density from user_tab_col_statistics where  2  table_name='T1';COLUMN_NAME                    NUM_DISTINCT    DENSITY------------------------------ ------------ ----------ALL_DISTINCT                          10000      .0001SKEW                                     11 .090909091

如果沒有長條圖,列的density統計資訊代表了它的選擇率它是通過去時1/num_distinct=1/11=0.09090901來計算出來的.在有長條圖的情況下,density的計算依賴於長條圖的類型和oracle的版本.density值的範圍是0到1之間.當查詢使用這個列作謂詞條件時最佳化器將會使用這個列的density統計資訊來評估將要返回的行數.所以 cardinality(基數)=selectivity(選擇率)* number of rows(表的行數)

下面來檢查一下在謂詞條件中列的資料分布存在傾斜而沒有長條圖的情況下其基數評估的情況:

SQL> explain plan for select * from t1 where skew=1;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |   909 |  6363 |     7  (15)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T1   |   909 |  6363 |     7  (15)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("SKEW"=1)SQL>  explain plan for select * from t1 where skew=10000;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |   909 |  6363 |     7  (15)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T1   |   909 |  6363 |     7  (15)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - filter("SKEW"=10000)

因為oracle假設列skew中的資料是均勻分布的所以基數評估cardinality=density*num_rows=0.09090901*10000=909.09,四捨五入就是909行.但是我們知道skew=1的記錄只有1行而skew=10000的記錄有9990行.這種假設必然導致錯誤的執行計畫.例如,如果我們在列skew上建立一個B樹索引,oracle將使用對謂詞skew=10000行使用索引掃描並返回909行記錄.

SQL> create index skew_idx on t1(skew);Index created.SQL> exec dbms_stats.gather_index_stats(user,'skew_idx');PL/SQL procedure successfully completed.SQL> explain plan for select * from t1 where skew=10000;Explained.SQL> select  * from table(dbms_xplan.display);Plan hash value: 3994350891----------------------------------------------------------------------------------------| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |          |   909 |  6363 |     4   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1       |   909 |  6363 |     4   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | SKEW_IDX |   909 |       |     2   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("SKEW"=10000)

因為我們知道沒有給出關於資料分布的額外資訊,CBO假設列中的資料在最小值和最大值之間是均勻分布的所以選擇了錯誤的執行計畫.

oracle長條圖
一旦對列建立長條圖後,它將告訴CBO列中資料出現的頻率.所以在上面的例子中如果對列skew建立長條圖它將告訴最佳化順skew=1的值只出現一次,skew=10000的值出現了9990次.因此它能讓最佳化器選擇最優的執行計畫.

在oracle中有兩種類型的長條圖.第一種是oracle會選擇儲存列中每一個不同值以及其出現的頻率,稱這種為寬度平衡長條圖或頻率長條圖.這對於列有少量的不同值來說是有效和可能的方式.然而當列有大量不同值時要儲存每一個不同值以及其出現的頻率是不可能的.當然在無限資源(儲存空間和計算能力和解析時間)的情況下,可以在任何情況下對每一個不同值儲存其出現的頻率來對最佳化器提供最終的資訊,但是在真實的環境中這是不可能的.所以oracle使用高度平衡長條圖來儲存這樣的資料.oracle會根據列中不同值的數量來自動判斷所要建立長條圖的類型,不同類型的長條圖所描述的資訊是不同的.

頻率長條圖(frequence histograms)
頻率長條圖列中的不同值被划到相同數量的桶中.每一個桶中儲存的都是相同的值,也就是說頻率長條圖的桶數等於列的不同值的個數.buckets=ndv

下面的圖表代表了列skew的資料分布情況.從圖表中可以看出以下資訊:
在x軸有11個桶,每一個桶代表了一個不同的值
Y軸顯示了每一個不同值出現的頻率.skew的1到10的頻率是1,值10000的頻率是9990
通過查看這樣的資訊可以很容易的說出一個特定值出現的頻率

下面來對列skew建立一個頻率長條圖並查看資料是怎樣儲存在資料字典視圖中的.現在對參數method_opt使用’for column column_name size n’來建立指定桶數的長條圖.

SQL> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns skew size 11');PL/SQL procedure successfully completed.SQL> select column_name,endpoint_number,endpoint_value from user_tab_histograms where  2    table_name='T1' and column_name='SKEW';COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE------------ --------------- --------------SKEW                       1              1SKEW                       2              2SKEW                       3              3SKEW                       4              4SKEW                       5              5SKEW                       6              6SKEW                       7              7SKEW                       8              8SKEW                       9              9SKEW                      10             10SKEW                   10000          10000

第一個語句對列skew建立了有11個桶的長條圖,因為我們知道列skew有11個不同的值.第二個語句顯示了儲存在資料字典視圖中的長條圖資料.長條圖中儲存的資訊依賴於長條圖的桶數小於列不同值的個數或者相等會有不同的解釋,也就是說長條圖中儲存的資訊依賴於長條圖的類型會有不同的解釋.下面解釋頻率長條圖所代表的資訊.

Endpoint_value顯示的是真實的列值,endpoint_number顯示的是累積的行數或者是累積的頻率.為了計算一個特定列值的頻率需使用與它相關的endpoint_number值減去它之前的累積值.
例如,對於endpoint_value為5的值,它的endpoint_number為5,之前的endpoint_number為4,因上skew=5的記錄只有5-4=1行.類似的對於endpoint_value為10000的值它的endpoint_number為10000它之前的endpoint_number為10,所以skew=10000的記錄有10000=10=9990行.

使用下面的sql來解釋說明儲存在資料字典中的長條圖資訊:

SQL> select endpoint_value as column_value,  2  endpoint_number as cummulative_frequency,  3  endpoint_number - lag(endpoint_number,1,0) over (order by endpoint_number) as frequency  4  from user_tab_histograms  5  where table_name ='T1' and column_name='SKEW';COLUMN_VALUE CUMMULATIVE_FREQUENCY  FREQUENCY------------ --------------------- ----------           1                     1          1           2                     2          1           3                     3          1           4                     4          1           5                     5          1           6                     6          1           7                     7          1           8                     8          1           9                     9          1          10                    10          1       10000                 10000       9990

儲存總的或累積頻率來代替單個頻率在範圍掃描時是特別有用的對於象where skew< =10這樣的謂詞基數就現成的.
現在因為我們對更skew建立了長條圖再來查看之前的查詢有什麼不同:

SQL> select column_name,num_distinct,density,histogram from user_tab_col_statistics where table_name='T1';COLUMN_NAME                    NUM_DISTINCT    DENSITY HISTOGRAM------------------------------ ------------ ---------- ---------------ALL_DISTINCT                          10000      .0001 NONESKEW                                     11     .00005 FREQUENCYSQL> explain plan for select * from t1 where skew=10000;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT---------------------------------------------------------------------------Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |  9990 | 69930 |     7  (15)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T1   |  9990 | 69930 |     7  (15)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("SKEW"=10000)13 rows selected.SQL> explain plan for select * from t1 where skew=1;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------Plan hash value: 3994350891----------------------------------------------------------------------------------------| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |          |     1 |     7 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |     7 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | SKEW_IDX |     1 |       |     1   (0)| 00:00:01 |----------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("SKEW"=1)14 rows selected.現在最佳化器對於謂詞skew=10000選擇了全表掃描且能精確計算出它的基數9990.注意現在skew列的density是變成了0.00005也就是1/(2*num_rows)或者0.5/num_rows.高度平衡長條圖(height-balanced histograms)在頻率長條圖中oracle給每一個不同值分配一個桶,然而桶的最大個數是254,因此如果表中的列有大量的不同值(超過254),將會建立一個高度平衡的長條圖.在高度平衡長條圖中,因為我們的不同值超過了桶的個數,因此oracle首先分對列資料進行排序然後將資料集按桶數進行分類且除了最後一桶可能包含的資料比其它的桶少以外,所有其它的桶包含相同數量的值(這就是為什麼叫高度平等長條圖的原因).這是有一個單獨的語句用來建立高度平衡長條圖.當請求的桶數少於列中不同值的個數時,oracle就會建立一個高度平衡長條圖且這意味著endpoint_value和endpoint_number是不相同的.為瞭解釋這種類型長條圖的資訊先看一個列有23個值且有9個不同值的例子.假設我們指定長條圖的桶數是5,下面的圖表顯示了這些資料是如何儲存在長條圖中的:  基於上面的圖表可以得出以下資訊:長條圖的桶數比列中的不同值的個數小因為我們指定了長條圖的桶數是5,所以整個資料集除了最後一個桶(在這裡只有3個值)其它按相同的大小分配到每一個桶中.每一個桶中的endpoints和第一個桶中的first point被標記因為它們有特殊意義.資料3被標記為紅色,它是一種特殊情況它的endpoint出現在多個桶中.下面的圖表是長條圖的另一種顯示方式:  使用5個桶且列有23個值這意味著除了最後一個桶只有3個值以外其它每一個桶都有5個值.實際上這是oracle在資料字典視圖中儲存高度平衡長條圖資訊的方式.因為bucket 1和2都使用3作為一個endpoint,oracle為了節省空間的將不會儲存bucket 1.所以當桶被合并時只會儲存單個條目.  下面我們來對列skew建立一個高度平衡長條圖,這一次讓桶數小於列的不同值的個數11:

SQL> select  column_name,endpoint_number,endpoint_value from  2    user_tab_histograms where table_name='T1' and column_name='SKEW';COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE--------------- --------------  --------------SKEW                         0               1SKEW                         5           10000

這裡buckets 1到5都是用10000作為它的endpoint所以bucket 1到4為了節省空間的沒有被儲存.下面的查詢能用來顯示桶數和它的endpoinit值

SQL> SELECT bucket_number, max(skew) AS endpoint_value  2   FROM (  3   SELECT skew, ntile(5) OVER (ORDER BY skew) AS bucket_number  4   FROM t1)  5   GROUP BY bucket_number  6   ORDER BY bucket_number;BUCKET_NUMBER ENDPOINT_VALUE------------- --------------            1          10000            2          10000            3          10000            4          10000            5          10000

這裡ntile(5)是一個分析函數,它將一個有序的資料集劃分到5個桶中.

所以簡而言之,在高度平衡長條圖中,資料被劃分到不同的桶中除了最後一個桶每一個桶包含相同的資料.每一個桶中的最大值被記錄為endpoint_value而第一個桶中的最小值也被記錄(bucket 0).endpoint_number代表桶數.一旦資料被記錄到桶中將會識別為2種類型的資料:
Non popular values和popular values.

Popular values是哪些作為endpoint value出現多次的值.例如在前面的例子中3是一個popular值,在上面的例子中skew 10000是一個popular value.non popular value是哪些沒有作為endpoint values出現或者只作為endpoint values出現一次的值.popular value和non popular value不是固定的它依賴於長條圖桶的大小,改變桶的大小會出現不同的popular值.

小結:
列中不同值的個數小於長條圖的桶數:當不同值的個數小於桶數時,endpoint_value列包含的是不同值本身,endpoint_number列包含是小於列值的累積行數.(頻率長條圖)

列中不同值的個數大於長條圖的桶數:當不同值的個數大於桶數時,endpoint_number包含的是bucekt id且endpoint_value是顯示的每一個桶中的最大值.bucket 0是一個特殊它顯示的是列中的最小值(高度平衡長條圖).

本篇文章轉自：http://blog.itpub.net/26015009/viewspace-1072732/