What is Optimizer Statistics?

•Optimizer statistics are the collection of data that describe more details about the database and the objects in the database.

•These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.

•These statistics include tables, column, system, index level statistics. The following is listed with attributes that query optimizer use.

A)Table statistics
-------------------------
Number of rows
Number of blocks
Average row length

B)Column statistics
------------------------------
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)

C)Index statistics
--------------------------------
Number of leaf blocks
Levels
Clustering factor

D)System statistics
----------------------------------
I/O performance and utilization
CPU performance and utilization

•These optimizer statitics are are stored in the data dictionary like DBA_TABLES, DBA_TAB_STATISTICS, DBA_INDEXES, DBA_IND_STATISTICS etc.

•Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.