Table Stats gathering for Oracle

Question

When and how should table stats gathering be performed for Oracle, version 9 and up? How would you go about gathering stats for a large database, where stats gathering would collide with "business hours".

Answer 1

Gathering stats should be done whenever there has been large changes to the data content, for example a large number of deletes or inserts. If the table structure has changed you should gather stats also. It is advisable to use the 'ESTIMATE' option.

Do this as an automated process out of business hours if possible, or if you have to do it during business hours then choose a time when there is minimum access to the tables you wish to gather stats for.

Answer 2

Make sure when using the estimate (sample_percent) that you gather at least 10 percent. Below that can yield very questionable results.

Answer 3

Consider backing up current stats when gathering -- that way you can compare them (if you're interested) and possibly restore them if your new stats cause problems. Keep in mind that stats are used to determine execution plans -- you may only want to gather them if you want execution plans to change.

Answer 4

I don't agree that you should always rebuild your statistics after there have been lots of deletes or inserts. As ever, it depends. In a data warehouse situation, when re-building your materialized views you will be doing lots of deletes and inserts but the base structure of the data will not change.

You only need to re-calculate statistics on a table if there has been a significant change in its content. This does not necessarily mean after lots of deletes or inserts, but rather when deletes, inserts, or updates materially change the content with respect to possible execution plans.

If you are truncating tables and rebuilding (which will reset your statistics), instead of an expensive statistics calculation, you're often better off storing the statistics before truncating and restoring them once you've rebuilt the table.

For saving the current views of statistics you use:

dbms_stats.export_table_stats

and to restore them afterwards you use:

dbms_stats.import_table_stats

(There are corresponding procedures for schema and database.)