Gathering statistics
Any cost-based optimization is a reflection of the validity of the statistics on which it is based. To ensure that the statistics in your Oracle database are valid, you should periodically collect these statistics.
Prior to Oracle8i, the ANALYZE command was used to collect statistics. Oracle8i introduced the DBMS_STATS package, which offers a number of advantages over ANALYZE, such as collecting statistics in parallel and collecting statistics for global and partitioned objects. For more information on this package, see Chapter 10.
When you collect statistics, you can either collect statistics for the complete database or a data structure or use a sampling method to estimate the statistics for the complete data structure based on a smaller portion of data.
In general, you should gather statistics whenever the amount or composition of data in the database changes significantly, such as after creating a new index or doing a data load. Updating statistics should be a part of a general maintenance routine. You can also specify that statistics are automatically gathered for a particular table with procedures from the DBMS_STATS package.