如何看懂oracle 直方圖

直方圖概述：

在Oracle中直方圖是一種對資料分布品質情況進行描述的工具。它會按照某一列不同值出現數量多少，以及出現的頻率高低來繪制資料的分布情況，以便能夠指導優化器根據資料的分布做出正确的選擇。

在某些情況下，表的列中的數值分布将會影響優化器使用索引還是執行全表掃描的決策。當where 子句的值具有不成比例數量的數值時，将出現這種情況，使得全表掃描比索引通路的成本更低。這種情況下如果where 子句的過濾謂詞列之上有一個合理的正确的直方圖，将會對優化器做出正确的選擇發揮巨大的作用，使得SQL語句執行成本最低進而提升性能。

在分析表或索引時，直方圖用于記錄資料的分布。通過獲得該資訊，基于成本的優化器就可以決定使用将傳回少量行的索引，而避免使用基于限制條件傳回許多行的索引。直方圖的使用不受索引的限制，可以在表的任何列上建構直方圖。

構造直方圖最主要的原因就是幫助優化器在表中資料嚴重偏斜時做出更好的規劃 。例如，如果一到兩個值構成了表中的大部分資料(資料偏斜)，相關的索引就可能無法幫助減少滿足查詢所需的I/O數量。建立直方圖可以讓基于成本的優化器知道何時使用索引才最合适，或何時應該根據WHERE子句中的值傳回表中80％的記錄。

通常情況下在以下場合中建議使用直方圖：

1、當Where子句引用了列值分布存在明顯偏差的列時：當這種偏差相當明顯時，以至于WHERE 子句中的值将會使優化器選擇不同的執行計劃。這時應該使用直方圖來幫助優化器來修正執行路徑。（注意：如果查詢不引用該列，則建立直方圖沒有意義。這種錯誤很常見，許多 DBA 會在偏差列上建立柱狀圖，即使沒有任何查詢引用該列。）

2、當列值導緻不正确的判斷時：這種情況通常會發生在多表連接配接時，例如，假設我們有一個五項的表聯接，其結果集隻有10行。Oracle 将會以一種使第一個聯接的結果集（集合基數）盡可能小的方式将表聯接起來。通過在中間結果集中攜帶更少的負載，查詢将會運作得更快。為了使中間結果最小化，優化器嘗試在SQL執行的分析階段評估每個結果集的集合基數。在偏差的列上擁有直方圖将會極大地幫助優化器作出正确的決策。如優化器對中間結果集的大小作出不正确的判斷，它可能會選擇一種未達到最優化的表聯接方法。是以向該列添加直方圖經常會向優化器提供使用最佳聯接方法所需的資訊。

等頻直方圖與等高直方圖

預設的，如果一個傾斜列上的唯一值超過了254個，那麼ORACLE會對此列建立等高直方圖，否則建立等頻直方圖。我們先來看下等頻直方圖。

所謂的等頻即按照列上的不同資料值進行劃分，由于每個數值的頻度相同，高度不同，故稱為等頻。下面是具體例子：

通過如下方式，建立表TAB，更新字段B，讓列B産生傾斜。并在B列上建立索引。

SQL> create table tab(a number,b number);

Table created.

SQL> insert into tab select rownum,rownum from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> update tab set b=5 where b between 6 and 9995;

9990 rows updated.

SQL> create index tab_b_idx on tab(b);

Index created.

然後分析表，強制使列B不産生直方圖。

SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');

PL/SQL procedure successfully completed.

檢視視圖USER_TAB_HISTOGRAMS 或者DBA_TAB_COL_STATISTICS 

SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where TABLE_NAME='TAB'

TABLE_NAME   COLUMN_NAME  ENDPOINT_NUMBER ENDPOINT_VALUE

------------ ------------ --------------- --------------

TAB          B                          0              1

TAB          B                          1          10000

列B上隻有最大值，最小值兩條記錄分别對應端點号(endpoint_number)0和1，這種顯示說明列B沒有直方圖資訊。 

在沒有直方圖的情況下，在B列上進行等值查詢的時候，都是索引範圍掃描。

SQL>  select * from tab where b=5

Execution Plan

----------------------------------------------------------

Plan hash value: 157166354

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT            |           |  1000 |  6000 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB       |  1000 |  6000 |     4   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TAB_B_IDX |  1000 |       |     2   (0)| 00:00:01 |

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("B"=5)

收集直方圖資訊。看看是什麼效果。由于列B唯一值的個數沒有超過254是以産生的是等頻直方圖。

SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true);

預設是對所有列分析直方圖

在B=1時候采用索引掃描，而B=5時候，已經采用全表掃描了，說明直方圖起了作用。

SQL> select * from tab where b=1;

|   0 | SELECT STATEMENT            |           |     1 |     6 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TAB       |     1 |     6 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | TAB_B_IDX |     1 |       |     1   (0)| 00:00:01 |

   2 - access("B"=1) 

SQL>

select * from tab where  b=5;

Plan hash value: 1995730731

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT  |      |  9991 | 59946 |     6   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TAB  |  9991 | 59946 |     6   (0)| 00:00:01 |

   1 - filter("B"=5)

檢視此時的直方圖資訊：

SQL> select TABLE_NAME,COLUMN_NAME,ENDPOINT_NUMBER,ENDPOINT_VALUE from user_histograms where table_name='TAB';

TAB          B                          1              1

TAB          B                          2              2

TAB          B                          3              3

TAB          B                          4              4

TAB          B                       9995              5

TAB          B                       9996           9996

TAB          B                       9997           9997

TAB          B                       9998           9998

TAB          B                       9999           9999

TAB          B                      10000          10000

TAB          A                          0              1

------------ ------------ --------------- --------------

TAB          A                          1          10000

12 rows selected.

其中EDNPOINT_NUMBER是累計值。EDNPOINT_VALUE是列的值。可以看出這種等頻直方圖統計的列的資訊是非常精确的。它為每一個列值配置設定了一個bucket。從執行計劃的ROWS部分也可以看出ORACLE計算出來的cardinality是9991，和實際的情況完全吻合。

如果想知道每一個列值對應的數量是多少，需要做一下簡單的減法運算：假如想知道列值等于5的個數，那麼可以通過：9995-4=9991得到。這就是ENDPOINT_NUMBER累計值的含義。

以上部分，就是如何看懂直方圖的辦法，如果能夠了解這些，直方圖就能看懂了。

等高直方圖，當列上的資料不同值超過254時，Oracle将會預設将列上的資料劃分為高度一緻但頻度不一緻的等高直方圖。

SQL> exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 8);

由于列B有10個唯一值，通過上面的size 8可以強制ORACLE使用等高直方圖。

檢視直方圖資訊.

TAB          B                          7              5

TAB          B                          8          10000

從查詢結果驚奇的發現隻有三個桶0 7 8，原來ORACLE會自動省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相鄰的桶的值。省去了桶（EDNPOINT_NUMBER）為1 2 3 4 5 6 ，EDNPOINT_VALUE為5的六條内容。

說明：在等高直方圖中，EDNPOINT_NUMBER代表桶号,這一點與等頻直方圖不同。再看等高直方圖下的執行計劃： 

SQL>  select * from tab where b=5

|   0 | SELECT STATEMENT  |      |  9982 | 59892 |     6   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| TAB  |  9982 | 59892 |     6   (0)| 00:00:01 |

有執行計劃的ROWS部分，ORACLE計算出來的cardinality不是特别精确的。9991才是精确值。而等頻直方圖可以精确到9991，是以可以說等頻直方圖比等高直方圖穩定，精确。

可是現實很多時候，列的唯一值是超過254的。隻能使用等高直方圖了。

一個注意點（删除直方圖）

如果需要删除直方圖資訊，10g中可以通過上面提到的

exec dbms_stats.gather_table_stats('HR','TAB',cascade=>true,method_opt=>'FOR COLUMNS B SIZE 1');

但這卻得再次收集表的統計資訊，十分不合理，11g有如下方法可以直接删除直方圖資訊

dbms_stats.delete_column_stats(ownname => user,

tabname => 'T',

colname => 'VAL',

col_stat_type => 'HISTOGRAM')