分类： Oracle
2010-04-18 19:24:32
我们知道从oracle8i开始﹐analyze语句和dbms_stats包都可以收集相关对象(Tables﹑Indexes﹑Clusters and Materialized Views)的statistics。那哪些statistics的收集应该使用analyze语句﹐哪些statistics的收集应该使用dbms_stats包呢﹖
对于使用哪个去收集statistics﹐应该把一个原则﹐凡是与cost-based optimizer相关的statistics﹐都应通过dbms_stats包收集。与cost-based optimizer无关的statistics(如empty blocks﹐average 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;
自从Oracle8.1.5引入dbms_stats包，及专家们就推荐使用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分析之后出现统计数据不准确的情况，而且确实有bug在dbms_stats上(可能和版本有关，有待查明)，应该是少数情况，需要我们注意。还有，一般不建议analyze和dbms_stats混用。 实验： 如果在分区表上用dbms_stats统计 后，再使用analyze table来统计，就会出现表信息不被更新的问题。删除统计信息后再分析就更新了，或者直接用dbms_stats分 析。dbms_stats目前有遇到的bug例子如下：http://www.itpub.net/viewthread.php?tid=959290&highlight=dbms%5C_stats
dbms_stats包可以分析table、Index或者整个用户(schema)，，可以并行分析。
不同版本包有些不一样，dbms_utility (8i以前的工具包)，dbms_stats (8i或以后提供的工具包)，具体的dbms_stats包的众多功能介绍见后
面。
对命令与工具包的一些总结:
1、对于分区表，建议使用DBMS_STATS，而 不是使用Analyze语句。
a)可以并行进行，对多个用户，多个Table
b)可以得到整个分区表的数据和单个分区的数据。
c)可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
d)可以导出统计信息
e)可以用户自动收集统计信息(alter table monitor )
2、DBMS_STATS的缺点：
a)不能Validate Structure (注意：validate structure主要在于校验对象的有效性. compute statistics在于统计相关的信息)。
b)不能收集CHAINED ROWS(行 链接),不能收集CLUSTER TABLE(簇表)的信息，这两个仍旧需要使用Analyze语句。
c) DBMS_STATS默
认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True。即GATHER_TABLE_STATS：分析表信息，当cascade为true时，分析表、列（索引）信息。
Analyze是同时更新表和索引的统计信息，而dbms_stats会 先更新表的统计信息，然后再更新索引的统计信息(默认Cascade是False)，这里就有一个问题，就是当表的统计信息更新后，而索引的统计信息没有被更新，这时候cbo就有可能选择错误的plan 。
3、对于oracle 9里面的External Table，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][SAMPLE integer { ROWS | PERCENT }]
| validation_clauses
| LIST CHAINED ROWS [ 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)
可以参考
Analyze的三大功能：
搜集和删除索引、表和簇的统计信息
验证表、索引和簇的结构
鉴定表和簇的行迁移(migrated rows)和行链接(chained rows)
CBO是Oracle推荐使用的优化方式，要想使用好CBO，使语句发挥最大效能，必须保证统计数据的及时性。统计信息的生成可以有完全计算法和抽样估算法。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
} ;
