Verifying Optimizer Statistics

Verifying Optimizer Statistics
==============================

The query optimizer uses statistics gathered on tables and indexes when determining
the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan.

Things to check:
----------------
■ If you gather statistics for some tables in your database, then it is probably best togather statistics for all tables. This is especially true if your application includes SQL statements that perform joins.

■ If the optimizer statistics in the data dictionary are no longer representative of thedata in the tables and indexes, then gather new statistics. One way to check
whether the dictionary statistics are stale is to compare the real cardinality (row
count) of a table to the value of DBA_TABLES.NUM_ROWS. Additionally, if there is
significant data skew on predicate columns, then consider using histograms.