Keeping your database statistics up to date
By ACShorten on Nov 23, 2009
One of the most important pieces of advice I like to give customers is the fact that their database statistics should be kept to date. The Cost Based Optimizer (CBO) uses statistics in the database to decide the most efficient path to get the data for individual SQL queries. If the statistics are stale or incorrect then the CBO may pick a less optimal path. It may make the wrong decision with the wrong statistics.
There are lots of articles about how statistics work and how to efficiently update them. Here is a short list from My Oracle Support that may assist in your understanding:
Note 236935.1 - Global statistics - An Explanation
Note 114671.1 - Gathering Statistics for the Cost Based Optimizer
Note 237537.1 - How to Move from ANALYZE to DBMS_STATS on Non-Partitioned Tables - Some Examples
Note 122009.1 - How to Retrieve Statistics Generated by ANALYZE SQL
Note 130688.1 - Script: Report Statistics for a Table, its Columns and Indexes with DBMS_STATS
Note 130911.1 - How to Determine if Dictionary Statistics are RDBMS- Generated or User-Defined
Note 1074354.6 - DBMS_STATS.CREATE_STAT_TABLE: What Do Table Columns Mean?
Note 117203.1 - How to Use DBMS_STATS to Move Statistics to a Different Database
Note 130899.1 - How to Set User-Defined Statistics Instead of RDBMS
Note 149560.1 - Collect and Display System Statistics (CPU and IO) for CBO usage
Note 153761.1 - Scaling the System to Improve CBO optimizer
Note 102334.1 - How to Automate Change Based Statistic Gathering - Monitoring Tables
Search for "Managing Optimizer Statistics" in your favorite search engine to get the product documentation.
The question I get from sites, is how often and how can I minimze the impact of the collection of statistics. Again there are guidelines available in the database documentation that suggest that you should update when a certain percentage of change in individual changes in table and how to minimze the impact of collacting the statistics.
The product itself does not have specific guidelines (except that it needs to be done on a regular basis) as we like to fit into the regime that you employ at your site for updating statistics and the growth factors experienced on individual objects due to the way you use the product.