Something about Optimizer Statistics

1. DBMS_STATS gathers only statistics needed for optimization; it does not gather other statistics. For example, the table statistics gathered by DBMS_STATS include the number of rows, number of blocks currently containing data, and average row length, but not the number of chained rows, average free space, or number of unused data blocks.

2. Oracle Database automatically collects index statistics whenever an index is created or rebuilt. Exceptions are domain indexes, or indexes created while the base table is empty.

3. The optimizer uses the following criteria when determining which index to use:

• Number of rows in the index (cardinality)

• Number of distinct keys (defining the selectivity of the index)

• Level or height of the index (indicating how deeply the data probe must search to find the data)

• Number of leaf blocks in the index (number of I/Os needed to find the desired rows of data)

• Average leaf blocks for each key (ALFBKEY): This is the average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.

• Average number of data blocks in the table that are pointed to by a distinct value in the index, rounded to the nearest integer: This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.

4. Whenever optimizer statistics are modified using the DBMS_STATS package, old versions of the statistics are saved automatically for future restoration. Statistics can be restored using the RESTORE procedures of the DBMS_STATS package. However, old versions of statistics are not stored when the ANALYZE command has been used for collecting statistics. Retention is configurable using the ALTER_STATS_HISTORY_RETENTION procedure.