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