ITPub博客

首页 > 数据库 > Oracle > 使用Analyze语句与Dbms_stats包的区别

使用Analyze语句与Dbms_stats包的区别

原创 Oracle 作者:royevictory 时间:2016-03-11 12:29:12 0 删除 编辑

使用Analyze语句与Dbms_stats包的区别

我们知道从oracle8i开始﹐analyze语句和dbms_stats包都可以收集相关对象(TablesIndexesClusters and Materialized Views)statistics。那哪些statistics的收集应该使用analyze语句﹐哪些statistics的收集应该使用dbms_stats包呢﹖


对于使用哪个去收集statistics﹐应该把一个原则﹐凡是与cost-based optimizer相关的statistics﹐都应通过dbms_stats包收集。与cost-based optimizer无关的statistics(empty blocksaverage space)都应通过analyze语句去收集。

之所以要用dbms_stats包去替代analyze收集优化器statistics﹐是因为dbms_stats包能收集并行statistics和分区对象的全局statistics

当然analyze语句在其它方面的statistics收集﹐是dbms_stats所无法取代的﹐如﹕

1. 收集在freelist上的blocks信息

2. 检验存储格式的合法性

analyze table bk_test_t validate structure cascade online;

3. 识别表或cluster的行迁移与行链接

为了能使用analyze….list chained rows语句识别 行迁移与行链接﹐必须先在执行analyze语句所在的schema内执行$ORACLE_HOME/rdbms/admin/utlchain.sql(utlchn1.sql)脚本建立chained_rows表。在chained_rows建立之后﹐就可以执行下面的语句﹕

analyze table bk_test list chained rows into chained_rows;




ANALYZE   TABLE   tablename   COMPUTE|ESTIMATE|DELETE   STATISTICS   
    
    
  请问COMPUTE;ESTIMATE;DELETE   分别有什么作用,有什么区别

analyze   table   tablename   compute   statistics   
    ------------------------------------------   
    对全表进行统计,然后生成统计信息,数据量大的时候     
    速度很慢,对temp   表要求也很高。   
    
    analyze   table   tablename   ESTIMATE   STATISTICS   
    SAMPLE     XXX     PERCENT   
    ---------------------   
    抽取表中的部分数据进行统计,最好>=20%,否则得不到   
    准确的统计信息   
    
    analyze   table   tablename   delete   statistics   
    -----------------------------------------   
      删除表上的统计信息   


自从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分析之后出现统计数据不准确的情况,而且确实有bugdbms_stats(可能和版本有关,有待查明),应该是少数情况,需要我们注意。还有,一般不建议analyzedbms_stats混用。 实验: 如果在分区表上用dbms_stats统计 后,再使用analyze table来统计,就会出现表信息不被更新的问题。删除统计信息后再分析就更新了,或者直接用dbms_stats分 析。dbms_stats目前有遇到的bug例子如下:http://www.itpub.net/viewthread.php?tid=959290&highlight=dbms%5C_stats 

 

dbms_stats包可以分析tableIndex或者整个用户(schema)数据库,可以并行分析。

不同版本包有些不一样,dbms_utility (8i以前的工具包)dbms_stats (8i以后提供的工具包),具体的dbms_stats包的众多功能介绍见后 面。

 

对命令与工具包的一些总结: 

1、对于分区表,建议使用DBMS_STATS,而 不是使用Analyze语句。

a)可以并行进行,对多个用户,多个Table

b)可以得到整个分区表的数据和单个分区的数据。

c)可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区

d)可以导出统计信息 

e)可以用户自动收集统计信息(alter table monitor )

2DBMS_STATS的缺点:

a)不能Validate Structure  (注意:validate structure主要在于校验对象的有效性. compute statistics在于统计相关的信息)

b)不能收集CHAINED ROWS(行 链接),不能收集CLUSTER TABLE(簇表)的信息,这两个仍旧需要使用Analyze语句。

c) DBMS_STATS默 认不对索引进行Analyze,因为默认CascadeFalse,需要手工指定为True。即GATHER_TABLE_STATS:分析表信息,当cascadetrue时,分析表、列(索引)信息

Analyze是同时更新表和索引的统计信息,而dbms_stats会 先更新表的统计信息,然后再更新索引的统计信息(默认CascadeFalse),这里就有一个问题,就是当表的统计信息更新后,而索引的统计信息没有被更新,这时候cbo就有可能选择错误的plan 

3、对于oracle 9里面的External TableAnalyze不能使用,只能使用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][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

 

dbms_stats所有的功能包如下:

GATHER_INDEX_STATS:分析索引信息

GATHER_TABLE_STATS:分析表信息,当cascadetrue时,分析表、列(索引)信息

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) 

 

可以参考  http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_46a.htm#SQLRF01105  

 

Analyze的三大功能:

搜集和删除索引、表和簇的统计信息
验证表、索引和簇的结构
鉴定表和簇的行迁移(migrated rows)和行链接(chained rows)

 

CBOOracle推荐使用的优化方式,要想使用好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

{ 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][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS



} ;

使用Analyze语句与Dbms_stats包的区别

我们知道从oracle8i开始﹐analyze语句和dbms_stats包都可以收集相关对象(TablesIndexesClusters and Materialized Views)statistics。那哪些statistics的收集应该使用analyze语句﹐哪些statistics的收集应该使用dbms_stats包呢﹖



对于使用哪个去收集statistics﹐应该把一个原则﹐凡是与cost-based optimizer相关的statistics﹐都应通过dbms_stats包收集。与cost-based optimizer无关的statistics(empty blocksaverage space)都应通过analyze语句去收集。

之所以要用dbms_stats包去替代analyze收集优化器statistics﹐是因为dbms_stats包能收集并行statistics和分区对象的全局statistics

当然analyze语句在其它方面的statistics收集﹐是dbms_stats所无法取代的﹐如﹕

1. 收集在freelist上的blocks信息

2. 检验存储格式的合法性

analyze table bk_test_t validate structure cascade online;

3. 识别表或cluster的行迁移与行链接

为了能使用analyze.list chained rows语句识别 行迁移与行链接﹐必须先在执行analyze语句所在的schema内执行$ORACLE_HOME/rdbms/admin/utlchain.sql(utlchn1.sql)脚本建立chained_rows表。在chained_rows建立之后﹐就可以执行下面的语句﹕

analyze table bk_test list chained rows into chained_rows;




ANALYZE   TABLE   tablename   COMPUTE|ESTIMATE|DELETE   STATISTICS   
    
    
 
请问COMPUTEESTIMATEDELETE   分别有什么作用,有什么区别

analyze   table   tablename   compute   statistics   
    ------------------------------------------   
   
对全表进行统计,然后生成统计信息,数据量大的时候     
   
速度很慢,对temp   表要求也很高。   
    
    analyze   table   tablename   ESTIMATE   STATISTICS   
    SAMPLE     XXX     PERCENT   
    ---------------------   
   
抽取表中的部分数据进行统计,最好>=20%,否则得不到   
   
准确的统计信息   
    
    analyze   table   tablename   delete   statistics   
    -----------------------------------------   
     
删除表上的统计信息   



自从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分析之后出现统计数据不准确的情况,而且确实有bugdbms_stats(可能和版本有关,有待查明),应该是少数情况,需要我们注意。还有,一般不建议analyzedbms_stats混用。 实验: 如果在分区表上用dbms_stats统计 后,再使用analyze table来统计,就会出现表信息不被更新的问题。删除统计信息后再分析就更新了,或者直接用dbms_stats分 析。dbms_stats目前有遇到的bug例子如下:http://www.itpub.net/viewthread.php?tid=959290&highlight=dbms%5C_stats 

 

dbms_stats包可以分析tableIndex或者整个用户(schema)数据库,可以并行分析。

不同版本包有些不一样,dbms_utility (8i以前的工具包)dbms_stats (8i或以后提供的工具包),具体的dbms_stats包的众多功能介绍见后 面。

 

对命令与工具包的一些总结

1、对于分区表,建议使用DBMS_STATS,而 不是使用Analyze语句。

a)可以并行进行,对多个用户,多个Table

b)可以得到整个分区表的数据和单个分区的数据。

c)可以在不同级别上Compute Statistics:单个分区,子分区,全表,所有分区

d)可以导出统计信息 

e)可以用户自动收集统计信息(alter table monitor )

2DBMS_STATS的缺点:

a)不能Validate Structure  (注意:validate structure主要在于校验对象的有效性. compute statistics在于统计相关的信息)

b)不能收集CHAINED ROWS(行 链接),不能收集CLUSTER TABLE(簇表)的信息,这两个仍旧需要使用Analyze语句。

c) DBMS_STATS默 认不对索引进行Analyze,因为默认CascadeFalse,需要手工指定为True。即GATHER_TABLE_STATS:分析表信息,当cascadetrue时,分析表、列(索引)信息。

Analyze是同时更新表和索引的统计信息,而dbms_stats会 先更新表的统计信息,然后再更新索引的统计信息(默认CascadeFalse),这里就有一个问题,就是当表的统计信息更新后,而索引的统计信息没有被更新,这时候cbo就有可能选择错误的plan 

3、对于oracle 9里面的External TableAnalyze不能使用,只能使用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][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

 

dbms_stats所有的功能包如下:

GATHER_INDEX_STATS:分析索引信息

GATHER_TABLE_STATS:分析表信息,当cascadetrue时,分析表、列(索引)信息

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) 

 

可以参考  http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_46a.htm#SQLRF01105  

 

Analyze的三大功能:

搜集和删除索引、表和簇的统计信息
验证表、索引和簇的结构
鉴定表和簇的行迁移(migrated rows)和行链接(chained rows)

 

CBOOracle推荐使用的优化方式,要想使用好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

{ 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][SAMPLE integer { ROWS | PERCENT }]

| validation_clauses

| LIST CHAINED ROWS [ into_clause ]

| DELETE [ SYSTEM ] STATISTICS

} ;

 



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29302187/viewspace-2055511/,如需转载,请注明出处,否则将追究法律责任。

上一篇: analyze和statistics
请登录后发表评论 登录
全部评论

注册时间:2014-08-06

  • 博文量
    195
  • 访问量
    527789