直方圖使用技巧及analyze table操作對直方圖統計的影響

原文：http://www.cnblogs.com/yumiko/p/6060485.html

前言

• 針對索引列，尤其是存在嚴重數據傾斜的索引列，直方圖的統計信息，對于CBO優化器更準確地選擇執行計劃至關重要。
• 對于初心者，可以從這篇文章中，了解到直方圖對于索引列的價值、作用，以及使用技巧。
• 對于經驗者，同樣可以從文章中，了解到不同的analyze table操作，對于直方圖信息統計的影響。該部分也可以直接查閱本篇文章最后的總結篇。
• 先強調一句：analyze table table_name compute statistics這個操作要謹慎！
  

1、直方圖概述

• 直方圖作為一種計量數據分布的統計工具，并非ORACLE專有。
• 對于ORACLE而言，直方圖主要用于在分析表以及索引時，統計相關列上的數據，記錄該列整體的數據分布情況。

2、直方圖的分類

• ORACLE的直方圖主要有兩種，等頻直方圖以及等高直方圖
• 默認情況下，當列上的唯一值數量低于254個，ORACLE會建立等頻直方圖。
• 默認情況下，當列上的唯一值數量高于254個，ORACLE會建立等高直方圖。
• 可以在執行dbms_stats.gather_table_stats收集統計信息時，通過method_opt參數，設置SIZE低于目標列的唯一值數量，從而使用等高直方圖。

3、直方圖的優勢

對于ORACLE而言，CBO優化器可以根據直方圖收集的列值分布信息，讓選擇性高（返回數據行比例少）的列值使用索引，而選擇性低（返回數據行比例多）的列值不使用索引。尤其對于存在數據傾斜嚴重的列而言，直方圖很重要。

注：數據傾斜，主要指某列上的一個數值，相較于該列其他數值，出現比例高，如：“性別”列，“男性”占到該列整體數值（男性、女性）的80%，存在明顯的數據傾斜現象。

4、直方圖適用范圍 

一般而言，直方圖不受是否使用索引的限制，即可以用來統計索引列，也可以統計非索引列。但對于非索引列的統計，意義不大。

5、直方圖涉及的主要視圖

直方圖類型的視圖：DBA_TAB_COL_STATISTICS，USER_TAB_COL_STATISTICS，ALL_TAB_COL_STATISTICS

直方圖具體信息的視圖：DBA_TAB_HISTOGRAMS，USER_TAB_HISTOGRAMS，ALL_TAB_HISTOGRAMS

6、直方圖對于執行計劃選擇影響的示例說明

本示例中使用的數據庫版本為ORACLE 11.2.0.4。

從上圖中可以看到，該列的SYS值分布占到了整體的98%，表明存在嚴重的傾斜。

為OWNER列創建索引，并使用ANALYZE TABLE的方法收集統計信息。

這里先忽略，后面可以對比for all columns子句的情況再看下。

查看此時索引列執行計劃的選擇情況，這里以傾斜數據SYS為條件進行檢索。

從上面返回的結果看，529K行的SYS數據，僅僅返回107K行，顯然存在很大的誤差。

此外，對于數據傾斜達到98%的SYS而言，顯然全表掃描的效率應該更高，這里應該與錯誤的統計信息有關。

使用dbms_stats.gather_table_stats的方式再次收集表的統計信息。

從上圖可以看到，此時完成了對表的最新統計，同時收集了索引列的直方圖信息，且該直方圖為”等頻直方圖“。

再次查看此時索引列的執行計劃選擇情況，這里分別以選擇性差的傾斜數據SYS為條件，以及以選擇性好的SCOTT為條件分別進行檢索。

對于選擇性差的SYS值，CBO優化器采用了全表掃描的方式進行數據的訪問

對于選擇性好的SCOTT值，CBO優化器則采用了索引掃描的方式進行數據的訪問

如果采用索引的方式訪問SYS相關的數據行，真實的代價會是怎樣呢，這里，通過hint的方式進行一次索引掃描的訪問

從上圖中可以看到，在信息收集無誤的情況下，若采用索引掃描，其真實開銷是全表掃描的4倍。

通過上面這個執行計劃，也說明了，對于CBO優化器，準確無誤的統計信息對于執行計劃選擇的重要性。

通過DBA_TAB_HISTOGRAMS視圖，查看此時直方圖的詳細信息

可以看到，雖然ENDPOINT_VALUE收集到了唯OWNER列唯一值的hash值，但真實列ENDPOINT_ACTUAL_VALUE顯示為空。

后面對比analyze table for all columns操作后再看。

刪除SYS值相關的數據行，觀察直方圖統計的變化

可以看到，對表數據的DML操作，直方圖信息并未自動更改。

再次使用dbms_stats.gather_table_stats收集統計信息，此時直方圖得到了更新，如下圖：

上述說明了，對于直方圖的信息，需要定期進行收集工作。

7、ANALYZE TABLE操作對直方圖統計影響的示例說明

本示例承接上面示例內容，數據庫版本一致。

在上面內容中，已經演示了analyze table table_name compute statistics無法針對表進行直方圖的信息收集。

那么，對于已存在直方圖的表，該操作又會有何影響呢。

首先，承接上面內容，再次執行analyze table table_name compute statistics的操作，觀察直方圖信息的變化。

可以明顯的發現，OWNER列的直方圖信息消失了，說明該操作會刪除已存在的直方圖信息。

如果這是一個生產環境，對于這樣一張存在數據傾斜列的表，可能會帶來不可預估的影響。

對于analyze table table_name compute statistics for all indexes的操作，這里不再演示，會在下面的總結中，直接給出對直方圖影響的結論。

下面看一下analyze table table_name compute statistics for all indexes for all columns操作的影響。

從上面可以看到，當執行for all columns子句的時候，不但收集了索引列的直方圖信息，還收集了非索引列的直方圖信息。

再看下此時DBA_TAB_HISTOGRAMS視圖的詳細信息

可以看到，此時可以看見DBA_TAB_HISTOGRAMS視圖上，ENDPOINT_ACTUAL_VALUE列真實值的信息。

8、總結

• 直方圖可以為CBO優化器提供準確的數據分布參考，以便選擇正確的執行計劃。

• 默認情況下，使用dbms_stats.gather_table_stats得到的直方圖信息，只會收集索引列的直方圖信息。
• 默認情況下，使用dbms_stats.gather_table_stats得到的直方圖信息，無法在DBA_TAB_HISTOGRAMS視圖中看到真實值，但不影響CBO優化器的選擇。

• 使用analyze table table_name compute statistics進行統計分析，對于尚未存在直方圖信息的表，不會收集直方圖信息。
• 使用analyze table table_name compute statistics進行統計分析，對于已經存在直方圖信息的表，會刪除之前的直方圖信息。

• 使用analyze table table_name compute statistics for all indexes進行統計分析，對于尚未存在直方圖信息的表，不會收集直方圖信息。
• 使用analyze table table_name compute statistics for all indexes進行統計分析，對于已經存在直方圖信息的表，不會刪除之前的直方圖信息。

• 使用analyze table table_name compute statistics for all indexes for all columns進行統計分析，對于尚未存在直方圖信息的表，會收集直方圖信息，且包括索引列以及非索引列。
• 使用analyze table table_name compute statistics for all indexes for all columns進行統計分析，對于已經存在直方圖信息的表，會收集最新的直方圖信息。

最后一點，起碼從直方圖的收集情況看：analyze table table_name compute statistics并不等價于analyze table table_name compute statistics for all indexes for all columns

謹慎使用analyze table table_name compute statistics這個操作。

重要的事情說三遍?。?！