Oracle里收集與查看統計信息的方法

Oracle數據庫里的統計信息是這樣的一組數據：它存儲在數據字典里，且從多個維度描述了Oracle數據庫里對象的詳細信息。CBO會利用這些統計信息來計算目標SQL各種可能的、不同的執行路徑的成本，并從中選擇一條成本值最小的執行路徑來作為目標SQL的執行計劃。

Oracle數據庫里的統計信息可以分為如下6種類型：

• 表的統計信息

• 索引的統計信息

• 列的統計信息

• 系統統計信息

• 數據字典統計信息

• 內部對象統計信息

表的統計信息用于描述Oracle數據庫里表的詳細信息，它包含了一些典型的維度，如記錄數、表塊(表里的數據塊)數量、平均行長度等。

索引的統計信息于描述Oracle數據庫里索引的詳細信息，它包含了一些典型的維度，如索引的層級、葉子塊的數量、聚簇因子等。

列的統計信息于描述Oracle數據庫里列的詳細信息，它包含了一些典型的維度，如列的distinct值的數量、列的NULL值的數量、列的最小值、列的最大值以及直方圖等。

系統統計信息于描述Oracle數據庫所在的數據庫服務器的系統處理能力，它包含了CPU和I/O這兩個維度，借助于系統統計信息，Oracle可以更清楚地知道目標數據庫服務器的實際處理能力。

數據字典統計信息用于熱核Oracle數據庫里數據字典基表(如TAB$、IND$等)、數據字典基表上的索引，以及這些數據字典的列的詳細信息，描述上述數據字典基表的統計信息與描述普通表、索引、列的統計信息沒有本質區別。

內部對象統計信息用于描述Oracle數據庫里的一些內部表(如X$系列表)的詳細信息，它的維度和普通表的統計信息的維度類似，只不過其表塊的數量為0，因為X$系統表實際上只是Oracle自定義的內存結構，并不占用實際的物理存儲空間。

1、收集統計信息

在Oracle數據庫里，通常有兩種方法可以用來收集統計信息：一種是使用ANALYZE命令；另一種是使用DBMS_STATS包。表、索引、列的統計信息和數據字典統計信息用ANALYZE命令或者DBMS_STATS包收集均可，但系統統計信息和系統內部對象統計信息只能使用DBMS_STATS包來收集。

對系統內部表若使用ANALYZE命令來收集統計信息，會報錯ORA-02030

1.1 用ANALYZE命令收集統計信息

從Oracle7開始，ANALYZE命令就可以用來收集表、索引、列的統計信息，以及系統統計信息。

典型用法如下：

zx@ORCL>create table t2 as select * from dba_objects;

Table created.

zx@ORCL>create index idx_t2 on t2(object_id);

Index created.

zx@ORCL>analyze index idx_t2 delete statistics;

Index analyzed.

從Oracle 10g開始，創建索引后Oracle會怎么收集目標索引的統計信息，出現演示的目的，這里刪除索引IDX_T2的統計信息：

執行sosi腳本，從輸出內容可以看到表T2、表T2的列和索引IDX_T2均沒有相關的統計信息

zx@ORCL>select count(*) from t2;

  COUNT(*)
----------
     86852

只對表T2收集統計信息，并且以估算模式，采樣的比例為15%：

zx@ORCL>analyze table t2 estimate statistics sample 15 percent for table;

Table analyzed.

再次執行sosi腳本，可以看出現在只用表T2有統計信息，表T2的列和索引IDX_T2均沒有相關的統計信息。而且因為采用的是估算模式所以估算結果和實際結果并不一定會完全匹配，比如表T2的實際數量與估算出的數量不一致。

只對表T2收集統計信息，并且以計算模式：

zx@ORCL>analyze table t2 compute statistics for table;

Table analyzed.

再次執行sosi腳本，可以看出現在只用表T2有統計信息，表T2的列和索引IDX_T2均沒有相關的統計信息。而且因為采用的是計算模式，計算模式會掃描目標對象的所有數據，所以統計結果和實際結果是匹配的。

對表T2收集完統計信息后，現在對表T2的列OBJECT_NAME和OBJECT_ID以計算模式收集統計信息：

zx@ORCL>analyze table t2 compute statistics for columns object_name,object_id;

Table analyzed.

再次執行sosi腳本，可以看出，現在列OBJECT_NAME和OBJECT_ID確實已經有統計信息了

注：在崔華老師的《基于Oracle的SQL優化》一書中提到T2原有的統計信息已經被抹掉了，也就是說對同一個對象而言，新執行的ANALYZE命令會抹掉之前ANALYZE的結果。但是在我實際的執行結果是表T2原有的統計信息沒有被抹掉。我用到的環境是10.2.0.4和11.2.0.4，暫時沒有11.2.0.1的環境。

可以使用如下的命令同時以計算模式對表T2和列OBJECT_NAME、OBJECT_ID收集統計信息：

zx@ORCL>analyze table t2 compute statistics for table for columns object_name,object_id;

Table analyzed.

再次執行sosi腳本，可以看到表T2和列OBJECT_NAME、OBJECT_ID上都有統計信息了。

使用如下命令可以以計算模式收集索引IDX_T2的統計信息

zx@ORCL>analyze index idx_t2 compute statistics;

Index analyzed.

再次執行sosi腳本，從輸出可以看到，現在索引IDX_T2已經有了統計信息，并且之前收集的表T2和列OBJECT_NAME、OBJECT_ID上的統計信息并沒有被抹掉，這是因為我們剛才執行的ANALYZE命令和之前執行的ANALYZE命令針對的不是同一個對象。

使用如下命令可以刪除表T2、表T2的所有列及表T2的所有索引的統計信息：

zx@ORCL>analyze table t2 delete statistics;

Table analyzed.

再次執行sosi腳本，從輸出可以看到，剛才收集的表T2、表T2的列OBJECT_NAME、OBJECT_ID以及索引IDX_T2的統計信息已經全部被刪除了。

如果想一次性以計算模式收集表T2、表T2的所有列和表T2上的所有索引的統計信息，執行如下的語句就可以了：

zx@ORCL>analyze table t2 compute statistics;

Table analyzed.

再次執行sosi腳本，從輸出可以看到，現在表T2、表T2的所有列和索引IDX_T2的統計信息都有了。

1.2 用DBMS_STATS包收集統計信息

從Oracle 8.1.5開始，DBMS_STATS包被廣泛用于統計信息的收集，用DMBS_STATS包收集統計信息也是Oracle官方推薦的方式。在收集CBO所需要的統計信息方面，可以簡單的將DBMS_STATS包理解成是ANALYZE命令的增加版。

DBMS_STATS包里最常用的就是如下4個存儲過程：

• GATHER_TABLE_STATS：用于收集目標表、目標表的列和目標表上的索引的統計信息。

• GATHER_INDEX_STATS：用于收集指定索引的統計信息。

• GATHER_SCHEMA_STATS：用于收集指定schema下所有對象的統計信息。

• GATHER_DATABASE_STATS：用于收集全庫所有對象的統計信息。

現在介紹DBMS_STATS包在收集統計信息時的常見用法，還是針對上面的測試表T2，這里使用DBMS_STATS包實現了和ANALYZE命令一模一樣的效果。

先刪除表T2上的所有統計信息

analyze table t2 delete statistics;

只對表T2收集統計信息，并且以估算模式，采用的比例同樣為15%：

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>15,method_opt=>'FOR TABLE',cascade=>false);

PL/SQL procedure successfully completed.

執行sosi腳本，從輸出內容可以看出，現在只有表T2有統計信息，表T2的列和索引IDX_T2均沒有相關的統計信息。而且因為采用的估算模式，所以估算結果和實際結果并不一定會完全匹配。

需要注意的是，這里Oracle數據庫的版本是11.2.0.4，我們在調用DMBS_STATS.GATHER_TABLE_STATS時指定參數METHOD_OPT的值為'FOR TABLE',這表示只收集表T2的統計信息。這種收集表統計信息的方法并不適用于Oracle數據庫所有的版本。例如這種方法就不適用于Oracle10.2.0.4和Oracle10.2.0.5，在這兩個版本里，即使指定了'FOR TABLE'，Oracle除了收集表統計信息之外還會對所有的列收集統計信息。

如果公對表T2收集統計信息，并且是以計算模式收集，用DBMS_STATS包實現的方法就是將估算模式的采樣比例(即參數ESTIMATE_PERCENT)設置為100%或NULL；

exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);

exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>NULL,method_opt=>'FOR TABLE',cascade=>false);

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'FOR TABLE',cascade=>false);

PL/SQL procedure successfully completed.

執行sosi腳本，從輸出內容可以看出，現在只有表T2的統計信息，表T2的列和索引IDX_T2均沒有相關的統計信息。而且因為采用的是計算模式，計算模式會掃描目標對象的所有數據，所以統計結果和實際結果是匹配的。

對表T2收集完統計信息后，現在我們來對表T2的列OBJECT_NAME、OBJECT_ID以計算模式收集統計信息(不收集直方圖)：

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,method_opt=>'for columns size 1 object_name,object_id',cascade=>false);

PL/SQL procedure successfully completed.

執行sosi腳本，從輸出內容可以看出，現在表T2的列OBJECT_NAME、OBJECT_ID上都有統計信息了，并且Oracle還會同時收集表T2上的統計信息(注意，這和ANALYZE命令有所區別)。

使用如下命令可以以計算模式收集索引IDX_T2的統計信息

zx@ORCL>exec dbms_stats.gather_index_stats(ownname=>'ZX',indname=>'IDX_T2',estimate_percent=>100);

PL/SQL procedure successfully completed.

執行sosi腳本，從輸出內容可以看出，現在索引IDX_T2已經有了統計信息。

使用如下命令可以刪除表T2、表T2的所有列及表T2的所有索引的統計信息：

zx@ORCL>exec dbms_stats.delete_table_stats(ownname=>'ZX',tabname=>'T2');

PL/SQL procedure successfully completed.

執行sosi腳本，從輸出內容可以看出，表T2、表T2的所有列及表T2的所有索引的統計信息已經全部被刪除了。

如果想一次性以計算模式收集表T2、表T2的所有列及表T2的所有索引的統計信息，執行如下語句就可以了

zx@ORCL>exec dbms_stats.gather_table_stats(ownname=>'ZX',tabname=>'T2',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

1.3 ANALYZE和DBMS_STATS的區別

從上面的演示中可以看出ANALYZE命令和DBMS_STATS包都可以用來收集表、索引和列的統計信息，看起來它們在收集統計信息方面的效果是一模一樣的，為什么Oracle會推薦使用DBMS_STATS包來收集統計信息呢？

因為ANALYZE命令和DMBS_STATS包相比，存在如下缺陷：

ANALYZE命令不能正確地收集分區表的統計信息，而DBMS_STATS包卻可以。ANALYZE命令只會收集最低層次對象的統計信息，然后推導和匯總出高一級的統計信息，比如對于有子分區的分區表而言，它只會先收集子分區統計信息，然后再匯總，推導出分區或表級的統計信息。有的統計信息是可以從當前對象的下一級對象進行匯總后得到的，比如表的總行數，可以由各分區的行數相加得到。但有的統計信息則不能從下一級對象得到，比如列上的distinct值數量NUM_DISTINCT以及DESNSITY等。

ANALYZE命令不能并行收集統計信息，而DBMS_STATS包卻可以。并行收集統計信息對數據量很大的表表而言，是非常有用的特性。對于數據量很大的表，如果不能并行收集統計信息，則意味著如果想精確地收集目標對象的統計信息，那么耗費的時間可能會非常長，這有可能是不能接受的。在Oracle數據庫里，DBMS_STATS包收集統計信息可以并行執行，這在一定程度上緩解了對大表的統計信息收集過長所帶來的一系列問題。

DBMS_STATS包的并行收集是通過手工指定輸入參數DEGREE來實現的，比如對表T1進行收集統計信息，同時指定并行度為4：

exec dbms_stats.gahter_table_stats(ownname=>'SCOTT',tabname=>'T1',cascade=>true,estimate_percent=>100,degree=>4);

當然，DBMS_STATS包也不是完美的，它與ANALYZE命令相比，其缺陷在于DBMS_STATS包只能收集與CBO相關的統計信息，而與CBO無關的一些額外信息，比如行遷移/行鏈接的數量(CHAIN_CNT)、校驗表和索引的結構信息等，DBMS_STATS包就無能為力了。而ANALYZE命令可以用來分析和收集上述額外的信息，比如analyze table xxx list chained rows intoyyy 可以用來分析和收集行遷移/行鏈接的數量，analyzeindex xxx validate structure可以用來分析索引的結構。

2、查看統計信息

前面介紹了如何收集統計信息，那如何查看這些統計信息呢？Oracle數據庫的統計信息會存儲在數據字典里，我們只需要去查詢相關的數據字典就好了。如果有充裕的時間，現寫SQL去查詢數據字典里的統計信息也沒有什么，但當我們真正碰到有性能問題的SQL時，通常會希望能在第一時間就收集到與目標SQL相關的各種統計信息，以便于在第一時間定位問題所在，這時候寫SQL去查詢數據字典就已經來不及了，所以我們需要事先準備好通用的查詢統計信息的腳本，出問題的時候只需要運行一下腳本，就能在第一時間獲取目標對象的所有統計信息了。

sosi腳本(Show Optimizer Statistics Information)就是這樣一種腳本，國內的Oracle數據庫專家也一直在用這個腳本，它源于MOS上的文章：SCRIPT - Select to show OptimizerStatistics for CBO (文檔 ID 31412.1)，用法很簡單，只需要運行一下sosi腳本，并指定要查看統計信息的表名就可以了。它支持分區表，顯示分為三部分，分別是表級別的統計信息，分區級別的統計信息和子分區級別的統計信息。前面做實驗用到的也是這個腳本。

 附件是sosi腳本可以下載使用。

參考《基于Oracle的SQL優化》