[總結] Oracle表的分析統計

討論一： 使用dbms_stats 還是analyze

自從Oracle8.1.5引入dbms_stats包， Oracle及專家們就推薦使用dbms_stats取代analyze。 理由如下：

1. dbms_stats可以並行分析

2. dbms_stats有自動分析的功能(alter table monitor )

3. analyze 分析統計資訊的有些時候不準確

[@more@]

第1,2比較好理解，且第2點實際上在VLDB(Very Large Database)中是最吸引人的；3以前比較模糊，看了metalink236935.1 解釋，analyze在分析Partition表的時候，有時候會計算出不準確的Global statistics 。 原因是dbms_stats會實在的去分析表全域性統計資訊（當指定引數）；而analyze是將表分割槽（區域性）的statistics 彙總計算成表全域性statistics ，可能導致誤差。 沒有分割槽表的情況下兩個都可以使用(看個人習慣，當然也可以分割槽表使用dbms_stats, 其他使用analyze )。

不過在一些論壇上也有看到dbms_stats 分析之後出現統計資料不準確的情況，而且確實有bug 在dbms_stats 上(可能和版本有關，有待查明)，應該是少數情況，需要我們注意。 還有，一般不建議analyze 和dbms_stats 混用。 實驗： 如果在分割槽表上用dbms_stats統計後，再使用 analyze table 來統計，就會出現表資訊不被更新的問題。 刪除統計資訊後再分析就更新了，或者直接用dbms_stats分析。 dbms_stats 目前有遇到的bug例子如下： http://www.itpub.net/viewthread.php?tid=959290&highlight=dbms%5C_stats

dbms_stats包可以分析table、Index或者整個使用者(schema)，資料庫，可以並行分析。

不同版本包有些不一樣， dbms_utility (8i以前的工具包)，dbms_stats (8i或以後提供的工具包) ，具體的dbms_stats 包的眾多功能介紹見後面。

對命令與工具包的一些總結:

1、對於分割槽表，建議使用DBMS_STATS，而不是使用Analyze語句。

a) 可以並行進行，對多個使用者，多個Table

b) 可以得到整個分割槽表的資料和單個分割槽的資料。

c) 可以在不同級別上Compute Statistics：單個分割槽，子分割槽，全表，所有分割槽

d) 可以匯出統計資訊

e) 可以使用者自動收集統計資訊(alter table monitor )

2、DBMS_STATS的缺點：

a) 不能Validate Structure (注意：validate structure 主要在於校驗物件的有效性. compute statistics在於統計相關的資訊) 。

b) 不能收集CHAINED ROWS(行連結), 不能收集CLUSTER TABLE(簇表)的資訊，這兩個仍舊需要使用Analyze語句。

c) DBMS_STATS 預設不對索引進行Analyze，因為預設Cascade是False，需要手工指定為True 。即GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊。

Analyze是同時更新表和索引的統計資訊，而dbms_stats會先更新表的統計資訊，然後再更新索引的統計資訊(預設Cascade是False)，這裡就有一個問題，就是當表的統計資訊更新後，而索引的統計資訊沒有被更新，這時候cbo就有可能選擇錯誤的plan 。

3、對於oracle 9裡面的External Table，Analyze不能使用，只能使用DBMS_STATS來收集資訊。

Analyze 命令語法如下 ：

ANALYZE

{ TABLE [ schema.]table

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| INDEX [ schema. ]index

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| CLUSTER [ schema. ]cluster

}

{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]

| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

dbms_stats所有的功能包如下：

GATHER_INDEX_STATS：分析索引資訊

GATHER_TABLE_STATS：分析表資訊，當cascade為true時，分析表、列（索引）資訊

GATHER_SCHEMA_STATS：分析方案資訊

GATHER_DATABASE_STATS：分析資料庫資訊

GATHER_SYSTEM_STATS：分析系統資訊

EXPORT_COLUMN_STATS：匯出列的分析資訊

EXPORT_INDEX_STATS：匯出索引分析資訊

EXPORT_SYSTEM_STATS：匯出系統分析資訊

EXPORT_TABLE_STATS：匯出表分析資訊

EXPORT_SCHEMA_STATS：匯出方案分析資訊

EXPORT_DATABASE_STATS：匯出資料庫分析資訊

IMPORT_COLUMN_STATS：匯入列分析資訊

IMPORT_INDEX_STATS：匯入索引分析資訊

IMPORT_SYSTEM_STATS：匯入系統分析資訊

IMPORT_TABLE_STATS：匯入表分析資訊

IMPORT_SCHEMA_STATS：匯入方案分析資訊

IMPORT_DATABASE_STATS：匯入資料庫分析資訊

討論二： analyze 的使用方法 (分割槽表建議使用dbms_stats)

可以參考

Analyze 的三大功能：

蒐集和刪除索引、表和簇的統計資訊 驗證表、索引和簇的結構 鑑定表和簇的行遷移(migrated rows)和行連結(chained rows)

CBO是Oracle推薦使用的最佳化方式，要想使用好CBO，使SQL語句發揮最大效能，必須保證統計資料的及時性。統計資訊的生成可以有完全計演算法和抽樣估演算法。SQL例句如下：

完全計演算法： analyze table abc compute statistics;
抽樣估演算法(抽樣20%)： analyze table abc estimate statistics sample 20 percent;

對錶作完全計算所花的時間相當於做全表掃描，抽樣估演算法由於採用抽樣，比完全計演算法的生成統計速度要快，如果不是要求要有非常精確的資料的話，儘量採用抽樣分析法。建議對錶分析採用抽樣估算，對索引分析可以採用完全計算。

Analyze 分析table, index等需要的許可權： 必須在你自己的Schema(方案)中或者有ANALYZE ANY 系統許可權 。

比如： grant analyze any to tolywang ;

revoke analyze any from tolywang ;

Analyze 使用的侷限及改善：

Analyze 命令每次僅僅能影響到一個table(或index), 如果想透過analyze為整個schema或整個資料庫中的所有表生成統計數字。可以使用analyze的批處理方式(指令碼)。

Analyze 分析命令解析：

ANALYZE

{ TABLE [ schema.]table

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| INDEX [ schema. ]index

[ PARTITION ( partition ) | SUBPARTITION ( subpartition ) ]

| CLUSTER [ schema. ]cluster

}

{ COMPUTE [ SYSTEM ] STATISTICS [for_clause]

| ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

INDEX index： 對索引進行分析，分析的結果會放在USER_INDEXES, ALL_INDEXES,或 DBA_INDEXES中 。一般僅需要對索引進行統計時用到。

分析的內容：

Depth of the index from its root block to its leaf blocks (BLEVEL) 從索引的根塊到其葉塊的索引的深度(級數)。

Number of leaf blocks (LEAF_BLOCKS) 葉塊的數量，這些塊包括了指向表中及索引中行的指標。

Number of distinct index values (DISTINCT_KEYS) 不同索引值的數量 。

Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY) 包括每一個值的記錄的葉塊的平均數。

Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY) 被一個索引值指向的資料塊的平均數量。

Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR) 一個簇因子，表明了表中的行的順序和索引中的順序相匹配的緊密程度。

LAST_ANALYZED 為索引生成的統計數字的日期。

TABLE table：對錶進行分析，分析的結果會放在USER_TABLES, ALL_TABLES 和DBA_TABLES檢視中，當為表收集統計數字時，除非以別的方式指明，否則Oracle也為那個表中的索引收集統計數字。還有，在分析表的時候，oracle也會分析基於函式的index所引用的表示式。

分析table產生的內容 (在上面的幾個檢視列中可以找到)：

Number of rows (NUM_ROWS) * 表中行的數量 。

Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS) 高水位一下的資料塊數量(不管是否現在有資料還是空的) 。

* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS) 分配給表但未被資料使用的資料塊的數量。

Average available free space in each data block in bytes (AVG_SPACE) 在每一塊中自由空間數量的平均值(以位元組表示)。

Number of chained rows (CHAIN_CNT) 連結行的數量。

Average row length, including the row's overhead, in bytes (AVG_ROW_LEN) 在表中行的平均長度，以位元組表示。

LAST_ANALYZED : 為表生成統計資料的日期。

分析表的限制：

不可以分析資料字典表

不可以分析擴充套件表，但可以用DBMS_STATS來實現這個目的

不可以分析臨時表

不可以計算或估計下列欄位型別：REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types.

分析分割槽表最好使用DBMS_STATS來實現 。

PARTITION | SUBPARTITION：對分割槽表或索引進行分析

CLUSTER cluster: 對簇進行分析，分析的結果會放在ALL_CLUSTERS, USER_CLUSTERS and DBA_CLUSTERS.

compute_statistics_clause

語法：COMPUTE [ SYSTEM ] STATISTICS [for_clause]

對分析對像進行精確的統計，然後把資訊儲存的資料字典中。可以選擇對錶或對欄位進行分析。computed和estimated這兩種方式的統計資料都被最佳化器用來影響sql的執行計劃

如果指定system選項就只統計系統產生的資訊 。

for_clause ：

FOR TABLE：只統計表

FOR COLUMNS：只統計某個欄位

FOR ALL COLUMNS：統計所有欄位

FOR ALL INDEXED COLUMNS：統計索引的所有欄位

FOR ALL INDEXES： 只分析索引

FOR ALL LOCAL INDEXES 針對分割槽表中的本地索引

estimate_statistics_clause

ESTIMATE [ SYSTEM ] STATISTICS [for_clause][SAMPLE integer { ROWS | PERCENT }]

只是對部分行做一個大概的統計。適用於大表

SAMPLE：指定具體統計多少行，如果忽略這個引數的話，oracle會預設為1064行

ROWS causes：行數 Oracle to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1.

PERCENT causes：百分數 。 一般情況下，建議在可以得到足夠精確的統計的前提下使用最小的百分比 。

validation_clauses

分析REF或是對像的結構

比如 ：ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE customers VALIDATE REF UPDATE;

validate structure ： ANALYZE INDEX XXXXX VALIDATE STRUCTURE; 檢查表裡的行資料的完全性，並檢查表或者是索引的結構，並把分析過的結果寫入INDEX_STATS 資料字典中。

對於cascade , 有如下解釋： Specify CASCADE if you want Oracle to validate the structure of the indexes associated with the table or cluster. If you use this clause when validating a table, then Oracle also validates the table's indexes. If you use this clause when validating a cluster, then Oracle also validates all the clustered tables' indexes, including the cluster index.

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

備註： 需要注意一下各種統計方式的等價性以及執行的先後順序。 比如：

analyze table t compute statistics = analyze table t compute statistics for table for all indexes

還有，任何時候生成表統計的數字時，都擦掉了任意列的統計數字。 所以需要順序的執行命令，以免出現問題。

先 analyze table t compute statistics ， 然後 analyze table t compute statistics for all indexed columns . 如果順序錯誤，那麼列相關統計資訊就會被覆蓋。

另外， for all columns 是對資料列進行直方圖統計 。

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

統計分析後的資訊儲存檢視：

一般我們需要養成一種習慣，在分析之前，需要建立備份表，用於備份之前最近的一次統計分析資料，dbms_stats 包提供了專用的匯入匯出功能 。而Analyze分析之後的統計資訊存放在以下幾個檢視中 ：

for table的統計資訊存在於檢視：user_tables 、all_tables、dba_tables

for all indexes的統計資訊存在於檢視: user_indexes 、all_indexes、dba_indexes

for all columns的統計資訊存在於試圖：user_tab_columns、all_tab_columns、dba_tab_columns

統計分析資訊的刪除 ：

當analyze table table_name delete statistics 會從資料字典中刪除這個table的所有的statistics以及它的所有index統計資訊 。

檢視錶統計數字： 可以透過在dba_tables, all_tables和user_tables資料字典檢視中的列來訪問這些統計數字 。當為表收集統計數字時，除非以別的方式指明，否則Oracle也為那個表中的index收集統計資訊。

NUM_ROWS 表中行的數量

BLOCKS 高水位一下的資料塊數量(不管是否現在有資料還是空的)

EMPTY_BLOCKS 分配給表但未被資料使用的資料塊的數量

AVG_SPACE 在每一塊中自由空間數量的平均值(以位元組表示)

CHAIN_CNT 連結行的數量

AVG_ROW_LEN 在表中行的平均長度，以位元組表示

LAST_ANALYZED 為表生成統計數字的日期 。

檢視索引統計數字： 可以透過在dba_indexes, all_indexes和user_indexes資料字典檢視中的列來訪問這些統計數字 。

BLEVEL 從索引的根塊到其葉塊的索引的深度(級數)。

LEAF_BLOCKS 葉塊的數量，這些塊包括了指向表中及索引中行的指標。

DISTINCT_KEYS 不同索引值的數量 。

AVG_LEAF_BLOCKS_PER_KEY

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