analyze is slated to do this and this alone at some time in the future. analyze is destined to be the "validate" thing -- analyze validate structure, analyze list chained rows, etc.
dbms_stats will stick with stats and stats only. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:735625536552
因为Oracle Development 开发部门已经给予 Analyze 命令较为精确地功能定位了， 所以其最主要的2个功能validate structure和list chained rows目前和将来都不会被植入到DBMS_STATS包当中。 Analyze validate structure 的主要语法包括:@?/rdbms/admin/utlvalid.sql /* 创建存放验证信息的表 */ desc invalid_rows ANALYZE INDEX <index_name> [PARTITION <partition_name>] [SUBPARTITION <subpartition_name>] VALIDATE STRUCTURE CASCADE INTO <table_name> <OFFLINE | ONLINE> select * from index_stats; ANALYZE TABLE <table_name> [PARTITION <partition_name>] [SUBPARTITION <subpartition_name>] VALIDATE STRUCTURE CASCADE [INTO <table_name>] <OFFLINE | ONLINE> ANALYZE CLUSTER <cluster_name> VALIDATE STRUCTURE CASCADE INTO <table_name> <OFFLINE | ONLINE>补充：analyze index validate structure会填充index_stats视图，该视图包含了丰富的索引空间信息：
SQL> desc index_stats; Name Null? Type ----------------------------------------- -------- ---------------------------- HEIGHT NUMBER BLOCKS NUMBER NAME VARCHAR2(30) PARTITION_NAME VARCHAR2(30) LF_ROWS NUMBER LF_BLKS NUMBER LF_ROWS_LEN NUMBER LF_BLK_LEN NUMBER BR_ROWS NUMBER BR_BLKS NUMBER BR_ROWS_LEN NUMBER BR_BLK_LEN NUMBER DEL_LF_ROWS NUMBER DEL_LF_ROWS_LEN NUMBER DISTINCT_KEYS NUMBER MOST_REPEATED_KEY NUMBER BTREE_SPACE NUMBER USED_SPACE NUMBER PCT_USED NUMBER ROWS_PER_KEY NUMBER BLKS_GETS_PER_ACCESS NUMBER PRE_ROWS NUMBER PRE_ROWS_LEN NUMBER OPT_CMPR_COUNT NUMBER OPT_CMPR_PCTSAVE NUMBER但是请注意validate structure会锁住table/index/cluster等对象，对于在线业务可以考虑使用validate structure online在线验证方法，但是validate strucutre online也有它的缺点，那就是在线模式下结构验证命令将不填充索引的状态信息到index_stats视图。 Analyze list chained rows的主要语法包括：
@?/rdbms/admin/utlchain.sql desc chained_rows ANALYZE TABLE <table_name> LIST CHAINED ROWS INTO <table_name>; ANALYZE CLUSTER <cluster_name> LIST CHAINED ROWS INTO <table_name>;注意因为DBMS_STATS仅仅收集对CBO有用的统计信息，所以一些空间使用情况信息和使用FreeList管理的信息都不会被收集，这些信息包括: If statistics unrelated to the cost based optimizer are required, then these must still be collected using the the ANALYZE command. These statistics include: Space Usage information : EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT Information on freelist blocks AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS 因为以上信息对于CBO计算成本并没有帮助，所以DBMS_STATS也就无意也无法收集它们，但是Analyze命令还是可以做到收集以上这些信息。 此外因为CBO其实并不会参考Cluster类型对象的统计信息来计算Cost成本，而是使用cluster中各个表的统计信息(DBMS_STATS does not gather cluster statistics, but you can use DBMS_STATS to gather statistics on the individual tables instead of the whole cluster. ) 所以DBMS_STATS也不支持收集Cluster的统计信息。 这么总结一下，发现Analyze命令的独有功能还真不少，因为Oracle公司已经明确了Analyze作为"validate"验证命令的功能定位，且很多内部的工具和脚本仍在使用Analyze Table/Cluster/Index的特有功能，所以可以预期Analyze命令在未来的一段时间内也不会被废弃。 总结以上DBMS_STATS无法实现，而传统的Analyze命令可以做到的功能：
- Analyze validate structure 验证表、簇、索引的结构的完整性，使用cascade选项可以交叉验证表和索引的数据完整，online选项可以做到在线验证
- Analyze list chained rows 收集表、簇上的Migrated and Chained Rows链式或迁移行信息
- Analyze table compute statistics 收集表上的 empty_blocks、avg_space 空间使用信息
- Analyze Cluster 收集簇的信息，其实cluster上唯一可统计的信息是DBA_CLUSTERS.AVG_BLOCKS_PER_KEY(Number of blocks in the table divided by number of cluster keys) ， 所以收集cluster的statistics意义不大
Syntax: ANALYZE TABLE tablename COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options ANALYZE INDEX indexname COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options ANALYZE CLUSTER clustername COMPUTE|ESTIMATE|DELETE STATISTICS options ptnOption PARTITION (partion) SUBPARTITION (subpartition) options VALIDATE STRUCTURE [CASCADE] [INTO tablename] LIST CHAINED ROWS [INTO tablename] COMPUTE|ESTIMATE STATISTICS FOR TABLE COMPUTE|ESTIMATE STATISTICS FOR ALL COLUMNS COMPUTE|ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS COMPUTE|ESTIMATE STATISTICS FOR COLUMNS [SIZE int] column [SIZE int] When Estimating statistics you can optionally specify ... ESTIMATE STATISTICS SAMPLE n ROWS ... ESTIMATE STATISTICS SAMPLE n PERCENT