12-5. Optimizer Statistics

Optimizer statistics include table, column, index and system statistics. They provide the optimizer a statistically correct snapshot of data storage and distribution, which the optimizer uses to make decisions on how to access data.

The statistics that are collected include:

• Size of the table or index (in database blocks)
  SELECT blocks FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
• Number of rows
  SELECT num_rows FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
• Average row size and chain count (tables only)
  SELECT avg_row_len, chain_cnt FROM dba_tables WHERE owner='hr' AND table_name='EMPLOYEES'
• Height and number of deleted leaf rows (indexes only)

Optimizer statistics are collected automatically by the preconfigured GATHER_STATS_JOB, which runs during predefined maintenance windows, once per day.

A large table that experiences 10 percent growth (or reduction) within a 24-hour period is usually considered too volatile for statistics collection once per day to be sufficient. For tables that experience this level of change, Oracle recommends collecting statistics more frequently so it won't exceed 10 percent growth between collection periods.

Statistics can be manually collected by using Enterprise Manager or through the use of the DBMS_STATS package as shown here:

SQL> EXEC dbms_stats.gather_table_stats('HR', 'EMPLOYEES');