oracle直方圖筆記-轉
murkey發表於2013-12-18
1. 直方圖有兩種類別，等頻直方圖與等高直方圖。
預設的，如果一個傾斜列上的唯一值超過了254個，那麼ORACLE會對此列建立等高直方圖，否則建立等頻直方圖。
何謂傾斜列(或者說是列值偏差較大)：例如一個表TAB有10000行記錄，列A前10行的值分別1-10，
剩下的9900行值全部都為999，則該列稱為傾斜列，意思就是列的值分佈不均勻。
2. 使用場景：通常情況下，如果TAB的A列上建有普通索引，在A列上進行等值查詢的時候，都是索引範圍掃描。
這時候，如果等值查詢條件為A = '999'，索引範圍掃描花費的成本要遠遠大於全表掃描的成本，
那麼我們想要的情況是：當等值查詢條件為A = '999'時候走全表掃描，其他條件如A='1'時候走索引，
直方圖正是為了解決這個問題的。我們可以在TAB的A列上建立直方圖，那麼最佳化器就會選擇我們期望的最低成本
的路徑執行。
3. 語法，
begin
dbms_stats.gather_ table _stats(
ownname => '',
tabname =>''，
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade =>true，
degree => 7
);
end;
其中degree指定了並行度視主機的CPU個數而定，estimate_percent指定了取樣比率，此處使用了auto目的是讓oracle來決定取樣收集的比率，繪製直方圖時會根據取樣的資料分析結果來繪製，當然也可以人為指定取樣比率。如：estimate_percent=>20指定取樣比率為20%，cascade=>true指定收集相關表的索引的統計資訊，該引數預設為false，因此使用dbms_stats收集統計資訊時抹人事部收集表的索引資訊的。
method_opt: size 1不蒐集，size 2~255會蒐集，size auto自動判斷，size skewonly只要資料傾斜則蒐集
示例如下(轉自itpub)
4. 問題是：如果使用點陣圖索引，是否可以解決同樣的問題呢？
ORACLE的直方圖的一些試驗
直方圖有兩種類別，等頻直方圖與等高直方圖。
預設的，如果一個傾斜列上的唯一值超過了254個，那麼ORACLE會對此列建立等高直方圖，否則建立等頻直方圖。
透過如下方式，建立表TAB，更新欄位B，讓列B產生傾斜。並在B列上建立索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);
表已建立。
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into tab values (i, i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> update tab set b=5 where b between 6 and 9995;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index ix_tab_b on tab(b);
索引已建立。
然後分析表，強制使列B不產生直方圖。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');
END;
檢視檢視USER_TAB_HISTOGRAMS，列B上只有最大值，最小值兩條記錄分別對應端點號（endpoint_number）0和1，這種顯示說明列B沒有直方圖資訊。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ；
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
TAB B 1 10000
在沒有直方圖的情況下，在B列上進行等值查詢的時候，都是索引範圍掃描。
SQL> select * from tab where b=1;
執行計劃
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| 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 | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已選擇9991行。
執行計劃
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| 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 | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
收集直方圖資訊。看看是什麼效果。由於列B唯一值的個數沒有超過254因此產生的是等頻直方圖。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');
END;
在B=1時候採用索引掃描，而B=5時候，已經採用全表掃描了，說明直方圖起了作用。
SQL> select * from tab where b=1;
執行計劃
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 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 | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已選擇9991行。
執行計劃
----------------------------------------------------------
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 |
--------------------------------------------------------------------------
檢視此時的直方圖資訊：
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB'；
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
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
其中EDNPOINT_NUMBER是累計值。EDNPOINT_VALUE是列的值。可以看出這種等頻直方圖統計的 列的資訊是非常精確的。它為每一個列值分配了一個桶。從執行計劃的ROWS部分也可以看出ORACLE計算出來的cardinality是9991，和實 際的情況完全吻合。
如果想知道每一個列值對應的數量是多少，需要做一下簡單的減法運算：
假如想知道列值等於5的個數，那麼可以透過：
9995-4=9991得到。這就是ENDPOINT_NUMBER累計值的含義。
在看看等高直方圖的情況。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 8 ');
END;
由於列B有10個唯一值，透過上面的size 8可以強制ORACLE使用等高直方圖。
檢視直方圖資訊.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB'；
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
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;
已選擇9991行。
執行計劃
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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的。只能使用等高直方圖了。
預設的，如果一個傾斜列上的唯一值超過了254個，那麼ORACLE會對此列建立等高直方圖，否則建立等頻直方圖。
何謂傾斜列(或者說是列值偏差較大)：例如一個表TAB有10000行記錄，列A前10行的值分別1-10，
剩下的9900行值全部都為999，則該列稱為傾斜列，意思就是列的值分佈不均勻。
2. 使用場景：通常情況下，如果TAB的A列上建有普通索引，在A列上進行等值查詢的時候，都是索引範圍掃描。
這時候，如果等值查詢條件為A = '999'，索引範圍掃描花費的成本要遠遠大於全表掃描的成本，
那麼我們想要的情況是：當等值查詢條件為A = '999'時候走全表掃描，其他條件如A='1'時候走索引，
直方圖正是為了解決這個問題的。我們可以在TAB的A列上建立直方圖，那麼最佳化器就會選擇我們期望的最低成本
的路徑執行。
3. 語法，
begin
dbms_stats.gather_ table _stats(
ownname => '',
tabname =>''，
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade =>true，
degree => 7
);
end;
其中degree指定了並行度視主機的CPU個數而定，estimate_percent指定了取樣比率，此處使用了auto目的是讓oracle來決定取樣收集的比率，繪製直方圖時會根據取樣的資料分析結果來繪製，當然也可以人為指定取樣比率。如：estimate_percent=>20指定取樣比率為20%，cascade=>true指定收集相關表的索引的統計資訊，該引數預設為false，因此使用dbms_stats收集統計資訊時抹人事部收集表的索引資訊的。
method_opt: size 1不蒐集，size 2~255會蒐集，size auto自動判斷，size skewonly只要資料傾斜則蒐集
示例如下(轉自itpub)
4. 問題是：如果使用點陣圖索引，是否可以解決同樣的問題呢？
ORACLE的直方圖的一些試驗
直方圖有兩種類別，等頻直方圖與等高直方圖。
預設的，如果一個傾斜列上的唯一值超過了254個，那麼ORACLE會對此列建立等高直方圖，否則建立等頻直方圖。
透過如下方式，建立表TAB，更新欄位B，讓列B產生傾斜。並在B列上建立索引。
SQL> spool d:\hist.txt
SQL> create table tab (a number, b number);
表已建立。
SQL>
SQL> begin
2 for i in 1..10000 loop
3 insert into tab values (i, i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 過程已成功完成。
SQL> update tab set b=5 where b between 6 and 9995;
已更新9990行。
SQL> commit;
提交完成。
SQL> create index ix_tab_b on tab(b);
索引已建立。
然後分析表，強制使列B不產生直方圖。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 1 ');
END;
檢視檢視USER_TAB_HISTOGRAMS，列B上只有最大值，最小值兩條記錄分別對應端點號（endpoint_number）0和1，這種顯示說明列B沒有直方圖資訊。
SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' ；
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
TAB B 1 10000
在沒有直方圖的情況下，在B列上進行等值查詢的時候，都是索引範圍掃描。
SQL> select * from tab where b=1;
執行計劃
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| 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 | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已選擇9991行。
執行計劃
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| 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 | IX_TAB_B | 1000 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
收集直方圖資訊。看看是什麼效果。由於列B唯一值的個數沒有超過254因此產生的是等頻直方圖。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO ');
END;
在B=1時候採用索引掃描，而B=5時候，已經採用全表掃描了，說明直方圖起了作用。
SQL> select * from tab where b=1;
執行計劃
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 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 | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
SQL> select * from tab where b=5;
已選擇9991行。
執行計劃
----------------------------------------------------------
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 |
--------------------------------------------------------------------------
檢視此時的直方圖資訊：
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB'；
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
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
其中EDNPOINT_NUMBER是累計值。EDNPOINT_VALUE是列的值。可以看出這種等頻直方圖統計的 列的資訊是非常精確的。它為每一個列值分配了一個桶。從執行計劃的ROWS部分也可以看出ORACLE計算出來的cardinality是9991，和實 際的情況完全吻合。
如果想知道每一個列值對應的數量是多少，需要做一下簡單的減法運算：
假如想知道列值等於5的個數，那麼可以透過：
9995-4=9991得到。這就是ENDPOINT_NUMBER累計值的含義。
在看看等高直方圖的情況。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
TABNAME => 'TAB',
CASCADE => TRUE,
METHOD_OPT => 'FOR COLUMNS B SIZE 8 ');
END;
由於列B有10個唯一值，透過上面的size 8可以強制ORACLE使用等高直方圖。
檢視直方圖資訊.
SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'TAB'；
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ ---------------------------------------- --------------- --------------
TAB B 0 1
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;
已選擇9991行。
執行計劃
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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的。只能使用等高直方圖了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/500314/viewspace-1063634/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- （轉）Oracle直方圖詳解2009-04-24Oracle直方圖
- oracle 直方圖2009-11-18Oracle直方圖
- oracle直方圖histogram小記(一)2012-11-24Oracle直方圖Histogram
- 轉貼oracle直方圖histogram(二)2012-11-24Oracle直方圖Histogram
- oracle直方圖使用2016-12-16Oracle直方圖
- Oracle直方圖解析2013-12-18Oracle直方圖圖解
- matplotlib的直方圖繪製（筆記）2020-12-28直方圖筆記
- Oracle直方圖詳解2010-11-20Oracle直方圖
- [轉] oracle統計資訊(statistics)和直方圖(histogram)2009-07-04Oracle直方圖Histogram
- Oracle直方圖詳解(ZT)2009-06-01Oracle直方圖
- 直方圖2017-12-20直方圖
- 【效能優化】Oracle直方圖解析2010-08-23優化Oracle直方圖圖解
- oracle統計資訊和直方圖2009-06-01Oracle直方圖
- 收集直方圖及檢視直方圖資訊2015-12-05直方圖
- Oracle直方圖 (柱狀圖 histograms) 詳解2009-09-10Oracle直方圖Histogram
- [數字影像學筆記] 4.直方圖變換22020-12-07筆記直方圖
- 數字影像處理讀書筆記（三）直方圖匹配2020-10-12筆記直方圖
- oracle統計資訊--直方圖的收集：2016-12-06Oracle直方圖
- 直方圖(histograms)2017-02-28直方圖Histogram
- 7.3 直方圖2015-11-27直方圖
- webrtc QOS筆記一 Neteq直方圖演算法淺讀2023-02-16Web筆記直方圖演算法
- Oracle直方圖統計資訊的應用2017-11-06Oracle直方圖
- Oracle直方圖 32位元組限制2014-01-26Oracle直方圖
- 直方圖均衡化2023-01-08直方圖
- 04:垂直直方圖2017-03-06直方圖
- 直方圖學習2018-04-25直方圖
- [zt] Histograms - 直方圖2008-09-09Histogram直方圖
- dba_histograms等高直方圖和等頻直方圖的理解2016-05-20Histogram直方圖
- ORACLE的直方圖的一些試驗2010-05-08Oracle直方圖
- [20170615]直方圖-高度直方圖(11g).txt2017-06-15直方圖
- 直方圖中最大矩形2020-10-15直方圖
- 【效能優化】直方圖2010-09-03優化直方圖
- 【opencv學習筆記】027之直方圖反向投影 - calcBackProject函式詳解2018-05-16OpenCV筆記直方圖Project函式
- [Python影象處理] 十一.灰度直方圖概念及OpenCV繪製直方圖2018-11-06Python直方圖OpenCV
- OpenCV計算機視覺學習（9）——影像直方圖 & 直方圖均衡化2020-11-10OpenCV計算機視覺直方圖
- halcon-直方圖均衡2019-09-28直方圖
- python如何畫直方圖2021-09-11Python直方圖
- elasticsearch 之 histogram 直方圖聚合2022-11-25ElasticsearchHistogram直方圖