Optimizer statistics

posted Sep 8, 2010, 6:23 AM by Sachchida Ojha   [ updated May 8, 2012, 9:42 PM by Sachchida Ojha ]
Optimizer statistics are a 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. Optimizer statistics include the following:

Table statistics:  a) Number of rows b) Number of blocks c) Average row length
Column statistics: a) Number of distinct values (NDV) in column b) Number of nulls in column  c) Data distribution (histogram)
Index statistics:  a) Number of leaf blocks b) Levels c) Clustering factor
System statistics  a) I/O performance and utilization b) CPU performance and utilization

Also Read
Oracle 11g Enhanced Optimizer Statistics Maintenance!!


The DBMS_STATS package was introduced in Oracle 8i and is Oracles preferred method of gathering object statistics. Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods:
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);
This package also gives you the ability to delete statistics:
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('SCOTT');
EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.delete_index_stats('SCOTT', 'EMPLOYEES_PK');

Statistics must be regularly gathered on database objects as those database objects are modified over time. In order to determine whether or not a given database object
needs new database statistics,
Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL.
Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as
whether the table has been truncated, since the last time
statistics were gathered.

The information
about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay
while Oracle propagates the information to this view. Use the
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the
outstanding  monitored information
kept in the memory.

The GATHER_DATABASE_STATS or GATHER_SCHEMA_STATS procedures gather new statistics for tables with stale statistics when the OPTIONS parameter is
 set to GATHER STALE or GATHER AUTO. If a monitored table has been modified more
than 10%, then these statistics are considered stale and gathered again.