Oracle表的分析統(tǒng)計

1、總結(jié)Oracle表的分析統(tǒng)計討論一： 使用dbms_stats還是analyze自從Oracle8.1.5引入dbms_stats包，Oracle及專家們就推薦使用dbms_stats取代analyze。 理由如下：1. dbms_stats可以并行分析2. dbms_stats有自動分析的功能（alter table monitor ）3. analyze分析統(tǒng)計信息的有些時候不準確第1,2比較好理解，且第2點實際上在VLDB（Very Large Database）中是最吸引人的；3以前比較模糊，看了metalink236935.1解釋，analyze在分析Partition表的時候，有時

2、候會計算 出不準確的Globalstatistics。原因是dbms_stats會實在的去分析表全局統(tǒng)計信息（當指定參數(shù)）；而analyze是將表分區(qū)（局部）的statistics匯總計算成表全局statistics,可能導(dǎo)致誤差。沒有分區(qū)表的情況下兩個都可以使用（看個人習(xí)慣，當然也可以分區(qū)表使用dbms_stats,其他使用analyze ）。不過在一些論壇上也有看到dbms_stats分析之后出現(xiàn)統(tǒng)計數(shù)據(jù)不準確的情況，而且確實有bug在dbms_stats上（可能和版本有關(guān)，有待查明），應(yīng)該是少數(shù)情況，需要我們注意。還有，一般不建議analyze和dbms_stats混用。 實驗：如果在分

3、區(qū)表上用dbms_stats統(tǒng)計后，再使用analyze table來統(tǒng)計，就會出現(xiàn)表信息不被更新的問題。刪除統(tǒng)計信息后再分析就更新了，或者直接用dbms_stats分析。dbms_stats目前有遇到的bug例子如下：http:/ （8i以前的工具包），dbms_stats （8i或以后提供的工具包），具體的dbms_stats包的眾多功能介紹見后面。對命令與工具包的一些總結(jié)：1、對于分區(qū)表，建議使用DBMS_STATS而不是使用Analyze語句。a）可以并行進行，對多個用戶，多個Tableb）可以得到整個分區(qū)表的數(shù)據(jù)和單個分區(qū)的數(shù)據(jù)。c）可以在不同級別上Compute Statistic

4、s :單個分區(qū)，子分區(qū)，全表，所有分區(qū)d）可以導(dǎo)出統(tǒng)計信息e）可以用戶自動收集統(tǒng)計信息（alter table monitor ） 2、DBMS_STATS勺缺點：a）不能Validate Structure（注意：validate structure主要在于校驗對象的有效性.computestatistics在于統(tǒng)計相關(guān)的信息）。b）不能收集CHAINED ROWS（亍鏈接），不能收集CLUSTER TABL表）的信息，這兩個仍舊需要 使用Analyze語句。c) DBMS_STATS默認不對索引進行Analyze，因為默認Cascade是False,需要手工指定為True即GATHER_T

5、ABLE_STATS:分析表信息，當cascade為true時，分析表、列(索引)信息。Analyze是同時更新表和索引的統(tǒng)計信息，而dbms_stats會先更新表的統(tǒng)計信息，然后再更新索 引的統(tǒng)計信息(默認Cascade是False),這里就有一個問題，就是當表的統(tǒng)計信息更新后，而索引 的統(tǒng)計信息沒有被更新，這時候cbo就有可能選擇錯誤的plan。3、對于oracle 9里面的External Table , Analyze不能使用，只能使用DBMS_STATS收集信息。Analyze命令語法如下：ANALYZE TABLE schema.tablePARTITION ( partition

6、 ) | SUBPARTITION ( subpartition )| INDEX schema. indexPARTITION ( partition ) | SUBPARTITION ( subpartition )| CLUSTER schema. cluster COMPUTE SYSTEM STATISTICS for_clause| ESTIMATE SYSTEM STATISTICS for_clauseSAMPLE integer ROWS | PERCENT | validation_clauses| LIST CHAINED ROWS into_clause | DELET

7、E SYSTEM STATISTICS;dbms_stats所有的功能包如下：GATHER_INDEX_STATS:分析索弓I信息GATHER_TABLE_STATS:分析表信息，當cascade為true時，分析表、列(索引)信息GATHER_SCHEMA_STATS:分析方案信息GATHER_DATABASE_STATS:分析數(shù)據(jù)庫信息GATHER_SYSTEM_STATS:分析系統(tǒng)信息EXPORT_COLUMN_STATS:導(dǎo)出列的分析信息EXPORT_INDEX_STATS:導(dǎo)出索引分析信息EXPORT_SYSTEM_STATS:導(dǎo)出系統(tǒng)分析信息EXPORT_TABLE_STATS:導(dǎo)

8、出表分析信息EXPORT_SCHEMA_STATS:導(dǎo)出方案分析信息EXPORT_DATABASE_STATS:導(dǎo)出數(shù)據(jù)庫分析信息IMPORT_COLUMN_STATS:導(dǎo)入列分析信息IMPORT_INDEX_STATS:導(dǎo)入索引分析信息IMPORT_SYSTEM_STATS:導(dǎo)入系統(tǒng)分析信息IMPORT_TABLE_STATS:導(dǎo)入表分析信息IMPORT_SCHEMA_STATS:導(dǎo)入方案分析信息IMPORT_DATABASE_STATS:導(dǎo)入數(shù)據(jù)庫分析信息討論二：analyze的使用方法(分區(qū)表建議使用dbms_stats)可以參考http:/ rows)CBO是Oracle推薦使用的優(yōu)化

9、方式，要想使用好CBO ,使SQL語句發(fā)揮最大效能，必須保證統(tǒng)計數(shù)據(jù)的及時性。統(tǒng)計信息的生成可以有完全計算法和抽樣估算法。SQL例句如下：完全計算法：analyze table abc compute statistics;抽樣估算法(抽樣20%) : analyze table abc estimate statistics sample 20 percent;對表作完全計算所花的時間相當于做全表掃描，抽樣估算法由于采用抽樣， 比完全計算法的生成統(tǒng)計速度要快，如果不是要求要有非常精確的數(shù)據(jù)的話，盡量采用抽樣分析法。 建議對表分析采用抽樣估算，對索引分析可以采用完全計算。Analyze分析ta

10、ble, index等需要的權(quán)限：必須在你自己的Schema(方案)中或者有ANALYZEANY系統(tǒng)權(quán)限。比如：grant analyze any to tolywang ;revoke analyze any from tolywang ;Analyze使用的局限及改善：Analyze命令每次僅僅能影響到一個table(或index),如果想通過analyze為整個schema或整個數(shù)據(jù)庫中的所有表生成統(tǒng)計數(shù)字?？梢允褂胊nalyze的批處理方式(腳本)。Analyze分析命令解析：ANALYZE TABLE schema.tablePARTITION ( partition ) | SUB

11、PARTITION ( subpartition )| INDEX schema. indexPARTITION ( partition ) | SUBPARTITION ( subpartition )| CLUSTER schema. cluster COMPUTE SYSTEM STATISTICS for_clause| ESTIMATE SYSTEM STATISTICS for_clauseSAMPLE integer ( ROWS | PERCENT )| validation_clauses| LIST CHAINED ROWS into_clause | DELETE SYS

12、TEM STATISTICS);INDEX index :對索引進行分析，分析的結(jié)果會放在USER_INDEXES, ALL_INDEXES,或DBA_INDEXES中。一般僅需要對索引進行統(tǒng)計時用到。分析的內(nèi)容：Depth of the index from its root block to its leaf blocks (BLEVEL)從索引的根塊到其葉塊的索引的深度(級數(shù))。Number of leaf blocks (LEAF_BLOCKS)葉塊的數(shù)量，這些塊包括了指向表中及索引中行的指針。Number of distinct index values (DISTINCT_KEYS

13、)不同索引值的數(shù)量 。Average number of leaf blocks for each index value (AVG_LEAF_BLOCKS_PER_KEY)包括每一個值的記錄的葉塊的平均數(shù)。Average number of data blocks for each index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY)被一個索引值指向的數(shù)據(jù)塊的平均數(shù)量。Clustering factor (how well ordered the rows are about the indexed values)(C

14、LUSTERING_FACTOR)一個簇因子，表明了表中的行的順序和索引中的順序相匹配的緊密程度。LAST_ANALYZED為索引生成的統(tǒng)計數(shù)字的日期。TABLE table:對表進行分析，分析的結(jié)果會放在USER_TABLES, ALL_TABLES和DBA_TABLES視圖中，當為表收集統(tǒng)計數(shù)字時，除非以別的方式指明，否則Oracle也為那個 表中的索引收集統(tǒng)計數(shù)字。還有，在分析表的時候，oracle也會分析基于函數(shù)的index所引用的表達式。分析table產(chǎn)生的內(nèi)容(在上面的幾個視圖列中可以找到)：Number of rows (NUM_ROWS) *表中行的數(shù)量 。Number of

15、data blocks below the high water mark (that is, the number of data blocks that have been formatted to receivedata, regardless whether they currently contain data or are empty) (BLOCKS)高水位一下的數(shù)據(jù)塊數(shù)量(不管是否現(xiàn)在有數(shù)據(jù)還是空的)。* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS)分配

16、給表但未被數(shù)據(jù)使用的數(shù)據(jù)塊的數(shù)量。Average available free space in each data block in bytes (AVG_SPACE)在每一塊中自由空間數(shù)量的平均值(以字節(jié)表示)。Number of chained rows (CHAIN_CNT)鏈接行的數(shù)量。Average row length, including the rows overhead, in bytes (AVG_ROW_LEN)在表中行的平均長度，以字節(jié)表示。LAST_ANALYZED :為表生成統(tǒng)計數(shù)據(jù)的日期。分析表的限制：不可以分析數(shù)據(jù)字典表不可以分析擴展表，但可以用DBMS_ST

17、ATS來實現(xiàn)這個目的不可以分析臨時表不可以計算或估計下列字段類型：REFs, varrays, nested tables, LOBs (LOBs are not analyzed,they are skipped), LONGs, or object types.分析分區(qū)表最好使用DBMS_STATS來實現(xiàn)。PARTITION | SUBPARTITION:對分區(qū)表或索引進行分析CLUSTER cluster:對簇進行分析，分析的結(jié)果會放在ALL_CLUSTERS, USER_CLUSTERSand DBA_CLUSTERS.compute_statistics_clause語法：COMPU

18、TE SYSTEM STATISTICS for_clause對分析對像進行精確的統(tǒng)計，然后把信息存儲的數(shù)據(jù)字典中。可以選擇對表或?qū)ψ侄芜M行分析。computed和estimated這兩種方式的統(tǒng)計數(shù)據(jù)都被優(yōu)化器用來影響sql的執(zhí)行計劃如果指定system選項就只統(tǒng)計系統(tǒng)產(chǎn)生的信息。for_clause :FOR TABLE:只統(tǒng)計表FOR COLUMNS:只統(tǒng)計某個字段FOR ALL COLUMNS:統(tǒng)計所有字段FOR ALL INDEXED COLUMNS:統(tǒng)計索引的所有字段FOR ALL INDEXES :只分析索弓IFOR ALL LOCAL INDEXES針對分區(qū)表中的本地索引est

19、imate_statistics_clauseESTIMATE SYSTEM STATISTICS for_clauseSAMPLE integer ROWS | PERCENT 只是對部分行做一個大概的統(tǒng)計。適用于大表SAMPLE:指定具體統(tǒng)計多少行，如果忽略這個參數(shù)的話，oracle會默認為1064行ROWS causes:行數(shù)Oracle to sample integer rows of the table or cluster or integer entries from the index. Theinteger must be at least 1.PERCENT causes

20、:百分數(shù)。一般情況下，建議在可以得到足夠精確的統(tǒng)計的前提下使用最 小的百分比。validation_clauses分析REF或是對像的結(jié)構(gòu)比如：ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;ANALYZE TABLE customers VALIDATE REF UPDATE;validate structure :ANALYZE INDEX XXXXX VALIDATE STRUCTURE；檢查表里的行數(shù)據(jù)的完全性，并檢查表或者是索引的結(jié)構(gòu)，并把分析過的結(jié)果寫入INDEX_STATS數(shù)據(jù)字典中。對于cascade ,有如下解釋：Spec

21、ifyCASCADEif you want Oracle to validate thestructure of the indexes associated with the table or cluster. If you use this clause when validating a table,then Oracle also validates the tables indexes. If you use this clause when validating a cluster, then Oraclealso validates all the clustered table

22、s indexes, including the cluster index.備注：需要注意一下各種統(tǒng)計方式的等價性以及執(zhí)行的先后順序。比如：analyze table t compute statistics = analyze table t compute statistics for table for all indexes還有，任何時候生成表統(tǒng)計的數(shù)字時，都擦掉了任意列的統(tǒng)計數(shù)字。所以需要順序的執(zhí)行命令, 以免出現(xiàn)問題。先analyze table t compute statistics, 然后analyze table t compute statistics for all indexed columns .如果順序錯誤，那么列相關(guān)統(tǒng)計信息就會被覆蓋。另外，for all columns是對數(shù)據(jù)列進行直方圖統(tǒng)計。統(tǒng)計分析后的信息存儲視圖：一般我們需要養(yǎng)成一種習(xí)慣，在分析之前，需要建立備份表，用于備份之前最近的一次統(tǒng)計分析數(shù)據(jù)，dbms_stats包提供了專用的導(dǎo)入導(dǎo)出功能。而Analyze分析之后的統(tǒng)計信息存放在以下幾個視圖中：for table的統(tǒng)計信息存在于視圖：user_tables、all_tables、dba_tablesfor all indexes