Oracle表的分析统计

[总结]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在分析Partition表的时候，有时候会计算出不准确的Globalstatistics。原因是dbms_stats会实在的去分析表全局统计信息（当指定参数）；而analyze是将表分区（局部）的statistics汇总计算成表全局statistics，可能导致误差。没有分区表的情况下两个都可以使用(看个人习惯，当然也可以分区表使用dbms_stats,其他使用analyze)。不过在一些论坛上也有看到dbms_stats分析之后出现统计数据不准确的情况，而且确实有bug在dbms_stats上(可能和版本有关，有待查明)，应该是少数情况，需要我们注意。还有，一般不建议analyze和dbms_stats混用。实验：如果在分区表上用dbms_stats统计后，再使用analyzetable来统计，就会出现表信息不被更新的问题。删除统计信息后再分析就更新了，或者直接用dbms_stats分析。dbms_stats目前有遇到的bug例子如下：=959290&highlight=dbms%5C_statsdbms_stats包可以分析table、Index或者整个用户(schema)，数据库，可以并行分析。不同版本包有些不一样，dbms_utility(8i以前的工具包)，dbms_stats(8i或以后提供的工具包)，具体的dbms_stats包的众多功能介绍见后面。对命令与工具包的一些总结:1、对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。a)可以并行进行，对多个用户，多个Tableb)可以得到整个分区表的数据和单个分区的数据。c)可以在不同级别上ComputeStatistics：单个分区，子分区，全表，所有分区d)可以导出统计信息e)可以用户自动收集统计信息(altertablemonitor)2、DBMS_STATS的缺点：a)不能ValidateStructure(注意：validatestructure主要在于校验对象的有效性.computestatistics在于统计相关的信息)。b)不能收集CHAINEDROWS(行链接),不能收集CLUSTERTABLE(簇表)的信息，这两个仍旧需要使用Analyze语句。c)DBMS_STATS默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True。即GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息。Analyze是同时更新表和索引的统计信息，而dbms_stats会先更新表的统计信息，然后再更新索引的统计信息(默认Cascade是False)，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan。3、对于oracle9里面的ExternalTable，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][SAMPLEinteger{ROWS|PERCENT}]|validation_clauses|LISTCHAINEDROWS[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)可以参考的三大功能：搜集和删除索引、表和簇的统计信息验证表、索引和簇的结构鉴定表和簇的行迁移(migratedrows)和行链接(chainedrows)CBO是Oracle推荐使用的优化方式，要想使用好CBO，使SQL语句发挥最大效能，必须保证统计数据的及时性。统计信息的生成可以有完全计算法和抽样估算法。SQL例句如下：完全计算法：analyzetableabccomputestatistics;抽样估算法(抽样20%)：analyzetableabcestimatestatisticssample20percent;对表作完全计算所花的时间相当于做全表扫描，抽样估算法由于采用抽样，比完全计算法的生成统计速度要快，如果不是要求要有非常精确的数据的话，尽量采用抽样分析法。建议对表分析采用抽样估算，对索引分析可以采用完全计算。Analyze分析table,index等需要的权限：必须在你自己的Schema(方案)中或者有ANALYZEANY系统权限。比如：grantanalyzeanytotolywang;revokeanalyzeanyfromtolywang;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][SAMPLEinteger{ROWS|PERCENT}]|validation_clauses|LISTCHAINEDROWS[into_clause]|DELETE[SYSTEM]STATISTICS};INDEXindex：对索引进行分析，分析的结果会放在USER_INDEXES,ALL_INDEXES,或DBA_INDEXES中。一般仅需要对索引进行统计时用到。分析的内容：Depthoftheindexfromitsrootblocktoitsleafblocks(BLEVEL)从索引的根块到其叶块的索引的深度(级数)。Numberofleafblocks(LEAF_BLOCKS)叶块的数量，这些块包括了指向表中及索引中行的指针。Numberofdistinctindexvalues(DISTINCT_KEYS)不同索引值的数量。Averagenumberofleafblocksforeachindexvalue(AVG_LEAF_BLOCKS_PER_KEY)包括每一个值的记录的叶块的平均数。Averagenumberofdatablocksforeachindexvalue(foranindexonatable)(AVG_DATA_BLOCKS_PER_KEY)被一个索引值指向的数据块的平均数量。Clusteringfactor(howwellorderedtherowsareabouttheindexedvalues)(CLUSTERING_FACTOR)一个簇因子，表明了表中的行的顺序和索引中的顺序相匹配的紧密程度。LAST_ANALYZED为索引生成的统计数字的日期。TABLEtable：对表进行分析，分析的结果会放在USER_TABLES,ALL_TABLES和DBA_TABLES视图中，当为表收集统计数字时，除非以别的方式指明，否则Oracle也为那个表中的索引收集统计数字。还有，在分析表的时候，oracle也会分析基于函数的index所引用的表达式。分析table产生的内容(在上面的几个视图列中可以找到)：Numberofrows(NUM_ROWS)*表中行的数量。Numberofdatablocksbelowthehighwatermark(thatis,thenumberofdatablocksthathavebeenformattedtoreceivedata,regardlesswhethertheycurrentlycontaindataorareempty)(BLOCKS)高水位一下的数据块数量(不管是否现在有数据还是空的)。*Numberofdatablocksallocatedtothetablethathaveneverbeenused(EMPTY_BLOCKS)分配给表但未被数据使用的数据块的数量。Averageavailablefreespaceineachdatablockinbytes(AVG_SPACE)在每一块中自由空间数量的平均值(以字节表示)。Numberofchainedrows(CHAIN_CNT)链接行的数量。Averagerowlength,includingtherow'soverhead,inbytes(AVG_ROW_LEN)在表中行的平均长度，以字节表示。LAST_ANALYZED:为表生成统计数据的日期。分析表的限制：不可以分析数据字典表不可以分析扩展表，但可以用DBMS_STATS来实现这个目的不可以分析临时表不可以计算或估计下列字段类型：REFs,varrays,nestedtables,LOBs(LOBsarenotanalyzed,theyareskipped),LONGs,orobjecttypes.分析分区表最好使