[總結] oracle表的分析統計
作者: tolywang(
發表於:2008.09.03 15:19
分類: oracle資料庫管理
出處：討論一： 使用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例子如下：
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
]| 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
]| 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) 不同索引值的數量 。
oracle系統表總結
oracle系統表處理 1.取得指定使用者的所有表名 範例 1.selectowneras 物件所有者 object nameas 表名 object idas 物件編號 fromdba objectswhereowner raxnyb andobject type table orderbyown...
工作表資料的統計分析
步英雷教學目標 知識技能 1 掌握對資料的篩選操作 2 掌握對資料的分類彙總操作 教學重 難點 教學重點 難點 1 掌握對資料的篩選操作 2 掌握對資料的分類彙總操作 教學難點 資料的高階篩選 教學方法 1 案例展示，教師引導 2 任務驅動下的學生自主 交流學習 教學課時 2課時教學過程 本節課內容...
Oracle分割槽表總結
create table emp empno number 4 ename varchar2 30 location varchar2 30 partition by list location partition p1 values 北京 partition p2 values 上海 天津 重慶 ...