[總結] Oracle優化 – 分析統計

 

編寫： 王琦

Itpub ID: tolywang

聯絡： tolywang@gmail.com

 

討論一： 使用dbms_stats 還是analyze 

 

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

1.     dbms_stats可以並行分析  

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

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

 

第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)  

 

可以參考   http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_46a.htm#SQLRF01105  

 

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

} ;

 

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