Oracle表的分析统计

1、总结Oracle 表的分析统计讨论一：使用 dbms_stats 还是 analyze自从 Oracle8.1.5 引入 dbms_stats 包，Oracle 及专家们就推荐使用 dbms_stats 取代analyze。理由如下：1. dbms_stats 可以并行分析2. dbms_stats 有自动分析的功能（altertablemonitor）3. analyze 分析统计信息的有些时候不准确第 1,2 比较好理解，且第 2 点实际上在 VLDB（VeryLargeDatabase）中是最吸引人的；3 以前比较模糊，看了 metalink236935.1 解释，analyze 在分析

2、 Partition 表的时候，有时候会计算出不准确的 Globalstatistics。原因是 dbms_stats 会实在的去分析表全局统计信息（当指定参数）；而 analyze 是将表分区（局部）的 statistics 汇总计算成表全局 statistics,可能导致误差。没有分区表的情况下两个都可以使用（看个人习惯，当然也可以分区表使用 dbms_stats,其他使用 analyze）。不过在一些论坛上也有看到 dbms_stats 分析之后出现统计数据不准确的情况，而且确实有 bug在 dbms_stats 上（可能和版本有关，有待查明），应该是少数情况，需要我们注意。还有，一般不

3、建议 analyze 和 dbms_stats 混用。实验：如果在分区表上用 dbms_stats 统计后，再使用analyzetable 来统计，就会出现表信息不被更新的问题。删除统计信息后再分析就更新了，或者直接用 dbms_stats 分析。dbms_stats 目前有遇到的 bug 例子如下：http:/ 包可以分析 table、Index 或者整个用户（schema）,数据库，可以并行分析。不同版本包有些不一样，dbms_utility（8i 以前的工具包），dbms_stats（8i 或以后提供的工具包），具体的 dbms_stats 包的众多功能介绍见后面。对命令与工具包的一些总

4、结：1、对于分区表，建议使用 DBMS_STATS 而不是使用 Analyze 语句。a）可以并行进行，对多个用户，多个 Tableb）可以得到整个分区表的数据和单个分区的数据。c）可以在不同级别上 ComputeStatistics:单个分区，子分区，全表，所有分区d）可以导出统计信息e）可以用户自动收集统计信息（altertablemonitor）2、DBMS_STATS 勺缺点：a）不能 ValidateStructure（注意：validatestructure 主要在于校验对象的有效性.computestatistics 在于统计相关的信息）。b）不能 U攵集CHAINEDROWST

5、 链接），不能 U攵集CLUSTERTABL 霞表）的信息，这两个仍旧需要使用 Analyze 语句。c)DBMS_STATS 默认不对索引进行 Analyze,因为默认 Cascade 是 False,需要手工指定为 True 即GATHER_TABLE_STATS:分析表信息，当cascade为true时，分析表、列(索引)信息。Analyze 是同时更新表和索引的统计信息，而 dbms_stats 会先更新表的统计信息，然后再更新索引的统计信息(默认 Cascade 是 False),这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候 cbo 就有可能选择错误

6、的 plan。3、对于 oracle9 里面的 ExternalTable,Analyze 不能使用，只能使用 DBMS_STAT 既收集信息。Analyze 命令语法如下：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:分析表信息，当cascade为true时，分析表、列(索引)信息GATHER_SCHEMA_STATS:分析方案信息GATHER_DATABASE_STATS:分析数据库信息GATHER_SYSTEM_STATS:分析系统信息EXPORT_COLUMN_

8、STATS:导出列的分析信息EXPORT_INDEX_STATS:导出索引分析信息EXPORT_SYSTEM_STATS:导出系统分析信息EXPORT_TABLE_STATS:导出表分析信息EXPORT_SCHEMA_STATS:导出方案分析信息EXPORT_DATABASE_STATS:导出数据库分析信息IMPORT_COLUMN_STATS:导入歹U分析信息IMPORT_INDEX_STATS:导入索引分析信息IMPORT_SYSTEM_STATS:导入系统分析信息IMPORT_TABLE_STATS:导入表分析信息IMPORT_SCHEMA_STATS:导入方案分析信息IMPORT_DA

9、TABASE_STATS:导入数据库分析信息讨论二：analyze 的使用方法(分区表建议使用dbms_stats)可以参考http:/ 的三大功能：搜集和删除索引、表和簇的统计信息验证表、索引和簇的结构鉴定表和簇的行迁移(migratedrows)和行链接(chainedrows)CBO 是 Oracle 推荐使用的优化方式，要想使用好 CBO,使 SQL 语句发挥最大效能，必须保证统计数据的及时性。统计信息的生成可以有完全计算法和抽样估算法。SQL 例句如下：完全计算法：analyzetableabccomputestatistics;抽样估算法(抽样 20%)：analyzetablea

10、bcestimatestatisticssample20percent;对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有非常精确的数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。Analyze 分析 table,index 等需要的权限：必须在你自己的 Schema(方案)中或者有 ANALYZEANY 系统权限。比如：grantanalyzeanytotolywang;revokeanalyzeanyfromtolywang;Analyze 使用的局限及改善：Analyze命令每次仅仅能影响

11、到一个table(或index),如果想通过analyze为整个schema或整个数据库中的所有表生成统计数字。可以使用 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:对索引进行分析，分析的结果会放在 USER_INDEXES,ALL_INDEXES,或DBA_INDEXES 中。一般仅需要对索引进行统计时用到。分析的内容：Depthoftheindexfromitsrootblocktoitsleafblocks(BLEVEL)从索引的根块到其叶块的索引的深度(级数)。Numberofleafblocks(

13、LEAF_BLOCKS)叶块的数量，这些块包括了指向表中及索引中行的指针。Numberofdistinctindexvalues(DISTINCT_KEYS)不同索引值的数量。Averagenumberofleafblocksforeachindexvalue(AVG_LEAF_BLOCKS_PER_KEY)包括每一个值的记录的叶块的平均数。Averagenumberofdatablocksforeachindexvalue(foranindexonatable)(AVG_DATA_BLOCKS_PER_KEY)被一个索弓 I 值指向的数据块的平土匀数量。Clusteringfactor(ho

14、wwellorderedtherowsareabouttheindexedvalues)(CLUSTERING_FACTOR)一个簇因子，表明了表中的行的顺序和索引中的顺序相匹配的紧密程度。LAST_ANALYZED 为索引生成的统计数字的日期。TABLEtable:对表进行分析，分析的结果会放在 USER_TABLES,ALL_TABLES 和DBA_TABLES 视图中，当为表收集统计数字时，除非以别的方式指明，否则 Oracle 也为那个表中的索引收集统计数字。还有，在分析表的时候，oracle 也会分析基于函数的 index 所引用的表达式。分析 table 产生的内容(在上面的几个视

15、图列中可以找到)：Numberofrows(NUM_ROWS)*表中行的数量。Numberofdatablocksbelowthehighwatermark(thatis,thenumberofdatablocksthathavebeenformattedtoreceivedata,regardlesswhethertheycurrentlycontaindataorareempty)(BLOCKS)高水位一下的数据块数量(不管是否现在有数据还是空的)。*Numberofdatablocksallocatedtothetablethathaveneverbeenused(EMPTY_BLOCK

16、S)分配给表但未被数据使用的数据块的数量。Averageavailablefreespaceineachdatablockinbytes(AVG_SPACE)在每一块中自由空间数量的平均值(以字节表示)。Numberofchainedrows(CHAIN_CNT)链接行的数量。Averagerowlength,includingtherowsoverhead,inbytes(AVG_ROW_LEN)在表中行的平均长度，以字节表示。LAST_ANALYZED:为表生成统计数据的日期。分析表的限制：不可以分析数据字典表不可以分析扩展表，但可以用 DBMS_STATS 来实现这个目的不可以分析临时表

17、不可以计算或估计下列字段类型：REFs,varrays,nestedtables,LOBs(LOBsarenotanalyzed,theyareskipped),LONGs,orobjecttypes.分析分区表最好使用 DBMS_STATS 来实现。PARTITION|SUBPARTITION:对分区表或索引进行分析CLUSTERcluster:对簇进行分析，分析的结果会放在 ALL_CLUSTERS,USER_CLUSTERSandDBA_CLUSTERS.compute_statistics_clause语法：COMPUTESYSTEMSTATISTICSfor_clause对分析对像进

18、行精确的统计，然后把信息存储的数据字典中。可以选择对表或对字段进行分析。computed 和 estimated 这两种方式的统计数据都被优化器用来影响 sql 的执行计划如果指定 system 选项就只统计系统产生的信息。for_clause:FORTABLE:只统计表FORCOLUMNS:只统计某个字段FORALLCOLUMNS:统计所有字段FORALLINDEXEDCOLUMNS:统计索引的所有字段FORALLINDEXES:只分析索弓 IFORALLLOCALINDEXES 针对分区表中的本地索引estimate_statistics_clauseESTIMATESYSTEMSTATI

19、STICSfor_clauseSAMPLEintegerROWS|PERCENT只是对部分行做一个大概的统计。适用于大表SAMPLE:指定具体统计多少行，如果忽略这个参数的话，oracle 会默认为 1064 行ROWScauses:行数Oracletosampleintegerrowsofthetableorclusterorintegerentriesfromtheindex.Theintegermustbeatleast1.PERCENTcauses:百分数。一般情况下，建议在可以得到足够精确的统计的前提下使用最小的百分比。validation_clauses分析 REF 或是对像的结构

20、比如：ANALYZETABLEemployeesVALIDATESTRUCTURECASCADE;ANALYZETABLEcustomersVALIDATEREFUPDATE;validatestructure:ANALYZEINDEXXXXXXVALIDATESTRUCTURE；检查表里的行数据的完全性，并检查表或者是索引的结构，并把分析过的结果写入INDEX_STATS数据字典中。对于cascade,有如下解释：SpecifyCASCADEifyouwantOracletovalidatethestructureoftheindexesassociatedwiththetableorclu

21、ster.Ifyouusethisclausewhenvalidatingatable,thenOraclealsovalidatesthetablesindexes.Ifyouusethisclausewhenvalidatingacluster,thenOraclealsovalidatesalltheclusteredtablesindexes,includingtheclusterindex.备注：需要注意一下各种统计方式的等价性以及执行的先后顺序。比如：analyzetabletcomputestatistics=analyzetabletcomputestatisticsforta

22、bleforallindexes还有，任何时候生成表统计的数字时，都擦掉了任意列的统计数字。所以需要顺序的执行命令,以免出现问题。先 analyzetabletcomputestatistics,然后 analyzetabletcomputestatisticsforallindexedcolumns.如果顺序错误，那么列相关统计信息就会被覆盖。另外，forallcolumns 是对数据列进行直方图统计。统计分析后的信息存储视图：一般我们需要养成一种习惯，在分析之前，需要建立备份表，用于备份之前最近的一次统计分析数据，dbms_stats 包提供了专用的导入导出功能。而 Analyze 分析之后的统计信息存放在以下几个视图中：fortable 的统计信息存在于视图：user_tables、all_tables、dba_tablesforallindexes 的统计信息存在于视图:user_indexes、all_inde