1.对索引结构进行统计
1.1analyze index indexname validate structure
联机文档的原文:Specify VALIDATE STRUCTURE to validate the structure of the analyzed object.
The statistics collected by this clause are not used by the Oracle Database optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses.
For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause.
这个命令主要是用来分析索引的数据块是否有坏块，以及根据分析得到的数据（存放在index_stats）來判断索引是否需要重新建立。
1.2 validate structure有二中模式：online, offline， 默认是offline模式。
联机文档原文:
Specify ONLINE to enable Oracle Database to run the validation while DML operations are ongoing within the object. The database reduces the amount of validation performed to allow for concurrency.
Specify OFFLINE, to maximize the amount of validation performed. This setting prevents INSERT, UPDATE, and DELETE statements from concurrently accessing the object during validation but allows queries. This is the default.
Restriction on ONLINE
You cannot specify ONLINE when analyzing a cluster or index.
Note:
When you validate the structure of an object ONLINE, Oracle Database does not collect any statistics, as it does when you validate the structure of the object OFFLINE.
以offline模式分析时，会对表加一个4级別的锁（表共享），对run系統可能造成一定的影响。
而online模式则没有表lock的影响，但当以online模式分析时， 在视图index_stats没有统计信息。
2.对索引状态进行统计
analyze index indexname compute statistics
联机文档原文:
COMPUTE STATISTICS instructs Oracle Database to compute exact statistics about the analyzed object and store them in the data dictionary. When you analyze a table, both table and column statistics are collected.
Both computed and estimated statistics are used by the Oracle Database optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements.
Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary views USER_TAB_HISTOGRAMS, DBA_TAB_HISTOGRAMS, and ALL_TAB_HISTOGRAMS; USER_PART_HISTOGRAMS, DBA_PART_HISTOGRAMS, and ALL_PART_HISTOGRAMS; and USER_SUBPART_HISTOGRAMS, DBA_SUBPART_HISTOGRAMS, and ALL_SUBPART_HISTOGRAMS.
总的来说，compute statistics是用来统计index的分析信息，来为CBO服务的。9i之后推荐使用dbms_stats。
ps:
for table的统计信息存在于视图：user_tables 、all_tables、dba_tables
for all indexes的统计信息存在于视图: user_indexes 、all_indexes、dba_indexes
for all columns的统计信息存在于试图：user_tab_columns、all_tab_columns、dba_tab_columns
3.一些sample
3.1使用validate structure分析一个索引是否需要重建
(1)analyze index index_name validate structure;
(2)select t.del_lf_rows_len /t.lf_blk_len from index_stats t where t.name = &index_name;
(3)如果结果大于20%,index就需要被rebuild了。
4.对于分区表，建议使用DBMS_STATS，而不是使用Analyze语句。
(1)可以并行进行，对多个用户，多个Table
(2)可以得到整个分区表的数据和单个分区的数据。
(3)可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区
(4)可以导出统计信息
(5)可以用户自动收集统计信息
5.DBMS_STATS的缺点
(1)不能Validate Structure
(2)不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。
(3)DBMS_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True
6.对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS_STATS来收集信息。