直方圖(histograms)

13811135929發表於2017-02-28
直方圖Histogram
        直方圖(histogram)本來是統計學概念，為什麼資料庫引入此概念，究其原因是因為透過直方圖最佳化器可以生成較好的執行計劃。
       一旦對列建立了直方圖後，透過直方圖可以看到列值各個資料出現的頻率（注意這裡說的是頻率，並不一定是某數值出現的具體次數）。直方圖分為兩種型別：頻度直方圖以及高度平衡直方圖。頻度直方圖針對列有少量的不同值比較有效，當列中不同值的數目較大，建議使用高度平衡直方圖。Oracle會根據列中不同值的數量來自動判斷所要建立的直方圖型別。
      依然拿上篇部落格的表為例子(http://blog.itpub.net/29827284/viewspace-2134398/)
  1. SQL> exec dbms_stats.gather_table_stats(USER,'T',method_opt=>'for columns skew size 11');
  2. SQL> select * from user_tab_histograms t where t.table_name='T' and COLUMN_NAME='SKEW';
  3. TABLE_NAME    COLUMN_NAME   ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_ACTUAL_VALUE
    ------------- ------------- --------------- -------------- ------------------------------
    T             SKEW                        1              1
    T             SKEW                        2              2
    T             SKEW                        3              3
    T             SKEW                        4              4
    T             SKEW                        5              5
    T             SKEW                        6              6
    T             SKEW                        7              7
    T             SKEW                        8              8
    T             SKEW                        9              9
    T             SKEW                       10             10
    T             SKEW                    10000          10000
      ENDPONIT_VALUE顯示的是列值
      ENDPOINT_NUMBER顯示的是累積的行數更嚴格的說是累積的頻率。
     為了計算一個特定列值的頻率要使用與它相關的endpoint_number值減去它之前的累計值。對於列SKEW中ENDPOINT_VALUE為10000，它對應的ENDPOINT_NUMBER=10000，上一個ENDPOINT_VALUE為10的ENDPOINT_NUMBER=10，那麼說明ENDPOINT_VALUE為10000有10000-10=9990條記錄。有了直方圖，可以讓最佳化器做出最優的執行計劃。廢話少說，透過實驗說明：

點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from t where skew=1;

  3. Explained.

  5. SQL> SELECT * FROM table(dbms_xplan.display);

  7. PLAN_TABLE_OUTPUT
  8. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9. Plan hash value: 1601196873

  11. --------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. --------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 1 | 7 | 7 (0)| 00:00:01 |
  15. |* 1 | TABLE ACCESS FULL| T | 1 | 7 | 7 (0)| 00:00:01 |
  16. --------------------------------------------------------------------------

  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------

  21.    1 - filter("SKEW"=1)


點選(此處)摺疊或開啟

  1. SQL> explain plan for select * from t where skew=10000;
  2. SQL> SELECT * FROM table(dbms_xplan.display);

  4. PLAN_TABLE_OUTPUT
  5. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  6. Plan hash value: 1601196873

  8. --------------------------------------------------------------------------
  9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  10. --------------------------------------------------------------------------
  11. | 0 | SELECT STATEMENT | | 9990 | 69930 | 7 (0)| 00:00:01 |
  12. |* 1 | TABLE ACCESS FULL| T | 9990 | 69930 | 7 (0)| 00:00:01 |
  13. --------------------------------------------------------------------------

  15. Predicate Information (identified by operation id):
  16. ---------------------------------------------------

  18.    1 - filter("SKEW"=10000)
可以看出收集資訊建立了直方圖後，評估出的基數相對準確了很多，從而避免生成錯誤的執行計劃。為了更好的說明情況，可以在SKEW列建立索引

點選(此處)摺疊或開啟

  1. SQL> create index ind_skew on t(skew);

  3. Index created.

  5. SQL> explain plan for select * from t where skew=1;

  7. Explained.

  9. SQL> SELECT * FROM table(dbms_xplan.display);

  11. PLAN_TABLE_OUTPUT
  12. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  13. Plan hash value: 3288809695

  15. ----------------------------------------------------------------------------------------
  16. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  17. ----------------------------------------------------------------------------------------
  18. | 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 |
  19. | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 7 | 2 (0)| 00:00:01 |
  20. |* 2 | INDEX RANGE SCAN | IND_SKEW | 1 | | 1 (0)| 00:00:01 |
  21. ----------------------------------------------------------------------------------------

  23. Predicate Information (identified by operation id):
  24. ---------------------------------------------------

  26.    2 - access("SKEW"=1)

  28. 14 rows selected.

  30. SQL> explain plan for select * from t where skew=10000;

  32. Explained.

  34. SQL> SELECT * FROM table(dbms_xplan.display);

  36. PLAN_TABLE_OUTPUT
  37. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  38. Plan hash value: 1601196873

  40. --------------------------------------------------------------------------
  41. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  42. --------------------------------------------------------------------------
  43. | 0 | SELECT STATEMENT | | 9990 | 69930 | 7 (0)| 00:00:01 |
  44. |* 1 | TABLE ACCESS FULL| T | 9990 | 69930 | 7 (0)| 00:00:01 |
  45. --------------------------------------------------------------------------

  47. Predicate Information (identified by operation id):
  48. ---------------------------------------------------

  50.    1 - filter("SKEW"=10000)
WHERE條件SKEW=1選擇走索引，WHERE條件SKEW=10000因為返回條目佔據了表的絕多部分記錄，Oracle選擇全表掃描，可以看出上面的兩條語句根據實際情況生成了非常完美的執行計劃。

  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29827284/viewspace-2134417/，如需轉載，請註明出處，否則將追究法律責任。

相關文章