ry views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES in the columns in parentheses.（带*为准确值）

oracle收集如下的索引统计信息。对于常规索引，可以在视图USER_INDEXES, ALL_INDEXES, and DBA_INDEXES中查看到如下的统计信息。

* Depth of the index from its root block to its leaf blocks (BLEVEL)（从0开始）

Number of leaf blocks (LEAF_BLOCKS) （叶子块的数量）

Number of distinct index values (DISTINCT_KEYS)

Average number of leaf blocks per index value (AVG_LEAF_BLOCKS_PER_KEY) （每个索引值存在于几个叶子块，通常为1）

Average number of data blocks per index value (for an index on a table) (AVG_DATA_BLOCKS_PER_KEY) （每个索引值对应的记录存在于几个数据块，通常为1）

Clustering factor (how well ordered the rows are about the indexed values) (CLUSTERING_FACTOR)（聚簇因子）

Where are the statistics stored

Statistics are stored into the Oracle Data Dictionary, in tables owned by SYS. Views are created on these tables to retrieve data more easily.

These views are prefixed with DBA_ or ALL_ or USER_. For ease of reading, we will use DBA_% views, but ALL_% views or USER_% views could be used as well.

统计信息存储在数据字典中，在sys用户下的表内。通过视图我们可以非常方便的从这些表中获取信息。视图通常以DBA_ USER_ ALL_开始。为了简便，我们以DBA_开头的视图为例。

Conventions Used

- Statistics available only since 8.0.X rdbms release         : (*)
- Statistics available only since 8.1.X rdbms release         : (**)
- Statistics not available at partition or subpartition level : (G)
- Statistics not available at subpartition level              : (GP)

Table level statistics can be retrieved from:

DBA_ALL_TABLES - (8.X onwards)DBA_OBJECT_TABLES - (8.X onwardsDBA_TABLES - (all versions)DBA_TAB_PARTITIONS - (8.X onwards)DBA_TAB_SUBPARTITIONS - (8.1 onwards)

Columns to look at are:

  NUM_ROWS                         : Number of rows (always exact even when computed 
                   			 with ESTIMATE method) 
  BLOCKS                           : Number of blocks which have been used even  
                                     if they are empty due to delete statements 
  EMPTY_BLOCKS                     : Number of empty blocks (these blocks have  
                                     never been used) 
  AVG_SPACE                        : Average amount of FREE space in bytes in blocks  
                                     allocated to the table : Blocks + Empty Blocks 
  CHAIN_CNT                        : Number of chained or migrated rows     
  AVG_ROW_LEN                      : Average length of rows in bytes 
  AVG_SPACE_FREELIST_BLOCKS (*)(G) : Average free space of blocks in the freelist 
  NUM_FREELIST_BLOCKS       (*)(G) : Number of blocks in the freelist 
  SAMPLE_SIZE                      : Sample defined in ESTIMATE method (0 if COMPUTE) 
  LAST_ANALYZED                    : Timestamp of last analysis 
  GLOBAL_STATS             (**)    : For partitioned tables, YES means statistics  
                                     are collected for the TABLE as a whole 
                                     NO means statistics are estimated from statistics  
                                     on underlying table partitions or subpartitions 
  USER_STATS               (**)    : YES if statistics entered directly by the user

Index level statistics can be retrieved from:

DBA_INDEXES - (all versions )DBA_IND_PARTITIONS - (8.X onwards)DBA_IND_SUBPARTITIONS - (8.1 onwards )

Columns to look at are:

  BLEVEL                       : B*Tree level : depth of the index from its root  
                                 block to its leaf blocks (从0开始)
  LEAF_BLOCKS                  : Number of leaf blocks 
  DISTINCT_KEYS                : Number of distinct keys 
  AVG_LEAF_BLOCKS_PER_KEY      : Average number of leaf blocks in which each 
                                 distinct key appears (1 for a UNIQUE index) 
  AVG_DATA_BLOCKS_PER_KEY      : Average number of data blocks in the table that  
                                 are pointed to by a distinct key 
  CLUSTERING_FACTOR            : - if near the number of blocks, then the table is  
                                   ordered : index entries in a single leaf block  
                                   tend to point to rows in same data block 
                                 - if near the number of rows, the table is  
                                   randomly ordered : index entries in a single  
                                   leaf block are unlikely to point to rows in  
                                   same data b

使用DBMS_STATS来收集统计信息(四)

Where are the statistics stored