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

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

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

3、建議 analyze 和 dbms_stats 混用。實(shí)驗(yàn)：如果在分區(qū)表上用 dbms_stats 統(tǒng)計(jì)后，再使用analyzetable 來統(tǒng)計(jì)，就會(huì)出現(xiàn)表信息不被更新的問題。刪除統(tǒng)計(jì)信息后再分析就更新了，或者直接用 dbms_stats 分析。dbms_stats 目前有遇到的 bug 例子如下：http:/ 包可以分析 table、Index 或者整個(gè)用戶（schema）,數(shù)據(jù)庫(kù)，可以并行分析。不同版本包有些不一樣，dbms_utility（8i 以前的工具包），dbms_stats（8i 或以后提供的工具包），具體的 dbms_stats 包的眾多功能介紹見后面。對(duì)命令與工具包的一些總

4、結(jié)：1、對(duì)于分區(qū)表，建議使用 DBMS_STATS 而不是使用 Analyze 語(yǔ)句。a）可以并行進(jìn)行，對(duì)多個(gè)用戶，多個(gè) Tableb）可以得到整個(gè)分區(qū)表的數(shù)據(jù)和單個(gè)分區(qū)的數(shù)據(jù)。c）可以在不同級(jí)別上 ComputeStatistics:單個(gè)分區(qū)，子分區(qū)，全表，所有分區(qū)d）可以導(dǎo)出統(tǒng)計(jì)信息e）可以用戶自動(dòng)收集統(tǒng)計(jì)信息（altertablemonitor）2、DBMS_STATS 勺缺點(diǎn)：a）不能 ValidateStructure（注意：validatestructure 主要在于校驗(yàn)對(duì)象的有效性.computestatistics 在于統(tǒng)計(jì)相關(guān)的信息）。b）不能 U攵集CHAINEDROWST

5、 鏈接），不能 U攵集CLUSTERTABL 霞表）的信息，這兩個(gè)仍舊需要使用 Analyze 語(yǔ)句。c)DBMS_STATS 默認(rèn)不對(duì)索引進(jìn)行 Analyze,因?yàn)槟J(rèn) Cascade 是 False,需要手工指定為 True 即GATHER_TABLE_STATS:分析表信息，當(dāng)cascade為true時(shí)，分析表、列(索引)信息。Analyze 是同時(shí)更新表和索引的統(tǒng)計(jì)信息，而 dbms_stats 會(huì)先更新表的統(tǒng)計(jì)信息，然后再更新索引的統(tǒng)計(jì)信息(默認(rèn) Cascade 是 False),這里就有一個(gè)問題，就是當(dāng)表的統(tǒng)計(jì)信息更新后，而索引的統(tǒng)計(jì)信息沒有被更新，這時(shí)候 cbo 就有可能選擇錯(cuò)誤

6、的 plan。3、對(duì)于 oracle9 里面的 ExternalTable,Analyze 不能使用，只能使用 DBMS_STAT 既收集信息。Analyze 命令語(yǔ)法如下：ANALYZETABLEschema.tablePARTITION(partition)|SUBPARTITION(subpartition)|INDEXschema.indexPARTITION(partition)|SUBPARTITION(subpartition)|CLUSTERschema.clusterCOMPUTESYSTEMSTATISTICSfor_clause|ESTIMATESYSTEMSTATIST

7、ICSfor_clauseSAMPLEintegerROWS|PERCENT|validation_clauses|LISTCHAINEDROWSinto_clause|DELETESYSTEMSTATISTICS;dbms_stats 所有的功能包如下：GATHER_INDEX_STATS:分析索弓I信息GATHER_TABLE_STATS:分析表信息，當(dāng)cascade為true時(shí)，分析表、列(索引)信息GATHER_SCHEMA_STATS:分析方案信息GATHER_DATABASE_STATS:分析數(shù)據(jù)庫(kù)信息GATHER_SYSTEM_STATS:分析系統(tǒng)信息EXPORT_COLUMN_

8、STATS:導(dǎo)出列的分析信息EXPORT_INDEX_STATS:導(dǎo)出索引分析信息EXPORT_SYSTEM_STATS:導(dǎo)出系統(tǒng)分析信息EXPORT_TABLE_STATS:導(dǎo)出表分析信息EXPORT_SCHEMA_STATS:導(dǎo)出方案分析信息EXPORT_DATABASE_STATS:導(dǎo)出數(shù)據(jù)庫(kù)分析信息IMPORT_COLUMN_STATS:導(dǎo)入歹U分析信息IMPORT_INDEX_STATS:導(dǎo)入索引分析信息IMPORT_SYSTEM_STATS:導(dǎo)入系統(tǒng)分析信息IMPORT_TABLE_STATS:導(dǎo)入表分析信息IMPORT_SCHEMA_STATS:導(dǎo)入方案分析信息IMPORT_DA

9、TABASE_STATS:導(dǎo)入數(shù)據(jù)庫(kù)分析信息討論二：analyze 的使用方法(分區(qū)表建議使用dbms_stats)可以參考http:/ 的三大功能：搜集和刪除索引、表和簇的統(tǒng)計(jì)信息驗(yàn)證表、索引和簇的結(jié)構(gòu)鑒定表和簇的行遷移(migratedrows)和行鏈接(chainedrows)CBO 是 Oracle 推薦使用的優(yōu)化方式，要想使用好 CBO,使 SQL 語(yǔ)句發(fā)揮最大效能，必須保證統(tǒng)計(jì)數(shù)據(jù)的及時(shí)性。統(tǒng)計(jì)信息的生成可以有完全計(jì)算法和抽樣估算法。SQL 例句如下：完全計(jì)算法：analyzetableabccomputestatistics;抽樣估算法(抽樣 20%)：analyzetablea

10、bcestimatestatisticssample20percent;對(duì)表作完全計(jì)算所花的時(shí)間相當(dāng)于做全表掃描，抽樣估算法由于采用抽樣，比完全計(jì)算法的生成統(tǒng)計(jì)速度要快，如果不是要求要有非常精確的數(shù)據(jù)的話，盡量采用抽樣分析法。建議對(duì)表分析采用抽樣估算，對(duì)索引分析可以采用完全計(jì)算。Analyze 分析 table,index 等需要的權(quán)限：必須在你自己的 Schema(方案)中或者有 ANALYZEANY 系統(tǒng)權(quán)限。比如：grantanalyzeanytotolywang;revokeanalyzeanyfromtolywang;Analyze 使用的局限及改善：Analyze命令每次僅僅能影響

11、到一個(gè)table(或index),如果想通過analyze為整個(gè)schema或整個(gè)數(shù)據(jù)庫(kù)中的所有表生成統(tǒng)計(jì)數(shù)字?？梢允褂?analyze 的批處理方式(腳本)。Analyze 分析命令解析：ANALYZETABLEschema.tablePARTITION(partition)|SUBPARTITION(subpartition)|INDEXschema.indexPARTITION(partition)|SUBPARTITION(subpartition)|CLUSTERschema.clusterCOMPUTESYSTEMSTATISTICSfor_clause|ESTIMATESYSTE

12、MSTATISTICSfor_clauseSAMPLEintegerROWS|PERCENT|validation_clauses|LISTCHAINEDROWSinto_clause|DELETESYSTEMSTATISTICS;INDEXindex:對(duì)索引進(jìn)行分析，分析的結(jié)果會(huì)放在 USER_INDEXES,ALL_INDEXES,或DBA_INDEXES 中。一般僅需要對(duì)索引進(jìn)行統(tǒng)計(jì)時(shí)用到。分析的內(nèi)容：Depthoftheindexfromitsrootblocktoitsleafblocks(BLEVEL)從索引的根塊到其葉塊的索引的深度(級(jí)數(shù))。Numberofleafblocks(

13、LEAF_BLOCKS)葉塊的數(shù)量，這些塊包括了指向表中及索引中行的指針。Numberofdistinctindexvalues(DISTINCT_KEYS)不同索引值的數(shù)量。Averagenumberofleafblocksforeachindexvalue(AVG_LEAF_BLOCKS_PER_KEY)包括每一個(gè)值的記錄的葉塊的平均數(shù)。Averagenumberofdatablocksforeachindexvalue(foranindexonatable)(AVG_DATA_BLOCKS_PER_KEY)被一個(gè)索弓 I 值指向的數(shù)據(jù)塊的平土勻數(shù)量。Clusteringfactor(ho

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

15、圖列中可以找到)：Numberofrows(NUM_ROWS)*表中行的數(shù)量。Numberofdatablocksbelowthehighwatermark(thatis,thenumberofdatablocksthathavebeenformattedtoreceivedata,regardlesswhethertheycurrentlycontaindataorareempty)(BLOCKS)高水位一下的數(shù)據(jù)塊數(shù)量(不管是否現(xiàn)在有數(shù)據(jù)還是空的)。*Numberofdatablocksallocatedtothetablethathaveneverbeenused(EMPTY_BLOCK

16、S)分配給表但未被數(shù)據(jù)使用的數(shù)據(jù)塊的數(shù)量。Averageavailablefreespaceineachdatablockinbytes(AVG_SPACE)在每一塊中自由空間數(shù)量的平均值(以字節(jié)表示)。Numberofchainedrows(CHAIN_CNT)鏈接行的數(shù)量。Averagerowlength,includingtherowsoverhead,inbytes(AVG_ROW_LEN)在表中行的平均長(zhǎng)度，以字節(jié)表示。LAST_ANALYZED:為表生成統(tǒng)計(jì)數(shù)據(jù)的日期。分析表的限制：不可以分析數(shù)據(jù)字典表不可以分析擴(kuò)展表，但可以用 DBMS_STATS 來實(shí)現(xiàn)這個(gè)目的不可以分析臨時(shí)表

17、不可以計(jì)算或估計(jì)下列字段類型：REFs,varrays,nestedtables,LOBs(LOBsarenotanalyzed,theyareskipped),LONGs,orobjecttypes.分析分區(qū)表最好使用 DBMS_STATS 來實(shí)現(xiàn)。PARTITION|SUBPARTITION:對(duì)分區(qū)表或索引進(jìn)行分析CLUSTERcluster:對(duì)簇進(jìn)行分析，分析的結(jié)果會(huì)放在 ALL_CLUSTERS,USER_CLUSTERSandDBA_CLUSTERS.compute_statistics_clause語(yǔ)法：COMPUTESYSTEMSTATISTICSfor_clause對(duì)分析對(duì)像進(jìn)

18、行精確的統(tǒng)計(jì)，然后把信息存儲(chǔ)的數(shù)據(jù)字典中?？梢赃x擇對(duì)表或?qū)ψ侄芜M(jìn)行分析。computed 和 estimated 這兩種方式的統(tǒng)計(jì)數(shù)據(jù)都被優(yōu)化器用來影響 sql 的執(zhí)行計(jì)劃如果指定 system 選項(xiàng)就只統(tǒng)計(jì)系統(tǒng)產(chǎn)生的信息。for_clause:FORTABLE:只統(tǒng)計(jì)表FORCOLUMNS:只統(tǒng)計(jì)某個(gè)字段FORALLCOLUMNS:統(tǒng)計(jì)所有字段FORALLINDEXEDCOLUMNS:統(tǒng)計(jì)索引的所有字段FORALLINDEXES:只分析索弓 IFORALLLOCALINDEXES 針對(duì)分區(qū)表中的本地索引estimate_statistics_clauseESTIMATESYSTEMSTATI

19、STICSfor_clauseSAMPLEintegerROWS|PERCENT只是對(duì)部分行做一個(gè)大概的統(tǒng)計(jì)。適用于大表SAMPLE:指定具體統(tǒng)計(jì)多少行，如果忽略這個(gè)參數(shù)的話，oracle 會(huì)默認(rèn)為 1064 行ROWScauses:行數(shù)Oracletosampleintegerrowsofthetableorclusterorintegerentriesfromtheindex.Theintegermustbeatleast1.PERCENTcauses:百分?jǐn)?shù)。一般情況下，建議在可以得到足夠精確的統(tǒng)計(jì)的前提下使用最小的百分比。validation_clauses分析 REF 或是對(duì)像的結(jié)構(gòu)

20、比如：ANALYZETABLEemployeesVALIDATESTRUCTURECASCADE;ANALYZETABLEcustomersVALIDATEREFUPDATE;validatestructure:ANALYZEINDEXXXXXXVALIDATESTRUCTURE；檢查表里的行數(shù)據(jù)的完全性，并檢查表或者是索引的結(jié)構(gòu)，并把分析過的結(jié)果寫入INDEX_STATS數(shù)據(jù)字典中。對(duì)于cascade,有如下解釋：SpecifyCASCADEifyouwantOracletovalidatethestructureoftheindexesassociatedwiththetableorclu

21、ster.Ifyouusethisclausewhenvalidatingatable,thenOraclealsovalidatesthetablesindexes.Ifyouusethisclausewhenvalidatingacluster,thenOraclealsovalidatesalltheclusteredtablesindexes,includingtheclusterindex.備注：需要注意一下各種統(tǒng)計(jì)方式的等價(jià)性以及執(zhí)行的先后順序。比如：analyzetabletcomputestatistics=analyzetabletcomputestatisticsforta

22、bleforallindexes還有，任何時(shí)候生成表統(tǒng)計(jì)的數(shù)字時(shí)，都擦掉了任意列的統(tǒng)計(jì)數(shù)字。所以需要順序的執(zhí)行命令,以免出現(xiàn)問題。先 analyzetabletcomputestatistics,然后 analyzetabletcomputestatisticsforallindexedcolumns.如果順序錯(cuò)誤，那么列相關(guān)統(tǒng)計(jì)信息就會(huì)被覆蓋。另外，forallcolumns 是對(duì)數(shù)據(jù)列進(jìn)行直方圖統(tǒng)計(jì)。統(tǒng)計(jì)分析后的信息存儲(chǔ)視圖：一般我們需要養(yǎng)成一種習(xí)慣，在分析之前，需要建立備份表，用于備份之前最近的一次統(tǒng)計(jì)分析數(shù)據(jù)，dbms_stats 包提供了專用的導(dǎo)入導(dǎo)出功能。而 Analyze 分析之后的統(tǒng)計(jì)信息存放在以下幾個(gè)視圖中：fortable 的統(tǒng)計(jì)信息存在于視圖：user_tables、all_tables、dba_tablesforallindexes 的統(tǒng)計(jì)信息存在于視圖:user_indexes、all_inde