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!!
DBMS_STATS
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.
