Where to get index statistics

• DBA_INDEXES

          Statistics columns populated by:

           – DBMS_STATS package (preferred)
           – ANALYZE command
          
           BLEVEL: Height of index between root block and leaf pages (0 means there is only a root block)
           LEAF_BLOCKS: Number of leaf blocks in index
           DISTINCT_KEYS: Number of distinct index values
           AVG_LEAF_BLOCKS_PER_KEY: Average number of leaf blocks required to store an
           indexed value.
           AVG_DATA_BLOCKS_PER_KEY: Average number of table blocks that contain rows
           referenced  by indexed key value
           NUM_ROWS: Number of leaf row entries
           CLUSTERING_FACTOR: Indicates how well ordered the rows in the table are in relation to
           the index

• INDEX_STATS

          Populated by ANALYZE ... VALIDATE STRUCTURE command
          Only stores details of last index analyzed
          HEIGHT: Height of index, beginning at 1 for root only index
          BLOCKS: Number of blocks allocated to the index, not necessarily used
          LF_ROWS: Number of leaf row entries, including deleted row entries
          LF_BLKS: Number of leaf blocks, including empty leaf blocks
          LF_ROWS_LEN: Total size of all leaf row entries, including overhead and deleted entries
          LF_BLK_LEN: Total usable space in all leaf blocks
          BR_ROWS: Number of branch row entries
          BR_BLKS: Number of branch blocks
          BR_ROWS_LEN: Total size of all branch row entries, including overhead
          BR_BLK_LEN: Total usable space in all branch blocks
          DEL_LF_ROWS: Number of deleted leaf row entries not yet cleaned out
          DEL_LF_ROWS_LEN: Total size of all deleted leaf row entries not yet cleaned out
          DISTINCT_KEYS: Number of distinct index entries, including deleted entries
          MOST_REPEATED_KEY: The number of key entries for the most repeated index value
          BTREE_SPACE: Total size of the entire index, including deleted entries
          USED_SPACE: Total space currently used (not free) within the index, including deleted entries
          PCT_USED: Percentage of space currently used (not free) within the index, including deleted entries
          ROWS_PER_KEY: Average number of leaf row entries per distinct key value
          BLKS_GETS_PER_ACCESS: Average number of block reads required to access specific
          index   entry (the fewer rows_per_key and the lower the CF, the lower this value). EG:
          For a unique index with a HEIGHT of 3, this value would be 4 (3 for the index block reads and
          one  for the table block read).

•  INDEX_HISTOGRAMS

•  V$SEGMENT_STATISTICS