Making the Most of Metrics and Statistics Collection

If your databases have weekly workload cycles, like major batch jobs during the weekend, you can use the default AWR retention period of seven days.

Your database should have enough data to provide accurate and valid results with Automatic Database Diagnostic Monitor, SQL Tuning Advisor, SQL Access Advisor, Undo Advisor, and Segment Advisor. So Oracle recommends that you set a larger AWR retention period to capture at least one complete workload cycle, which could be over a month for most companies.

You can also manually gather statistics depending on your application needs. For a read-only database with incremental table updates, you may elect to gather new statistics every week or fortnight or even once a month. You can use a script or job-scheduling tool to run the GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS procedures.

For bulk-load OLTP applications where tables are updated in batch cycles, you should gather statistics as part of the batch job after the data load. For partitioned tables, you may choose to gather statistics only on affected partitions rather than on entire tables.

Oracle also recommends that automatic snapshot collection should not be turned off unless absolutely necessary under exceptional circumstances. You will not be able to manually create snapshots when the snapshot interval is set to 0. When the automatic snapshot collection is turned off, automatic collection of the workload and statistical data is stopped and the self-management functionality is mostly disabled.

The baselines will help you to compare database workloads when you have performance problems, so it is very important for the DBA to create baselines for typical performance periods.

Statistics Collection During Database Upgrades

See MetaLink Note #263809.1 for the scripts to collect statistics during database upgrades. Make sure that you test the scripts in a QA environment before trying them in production databases.

When you are upgrading an older version of an Oracle database to Oracle Database 10g, the optimizer statistics will be calculated for those dictionary tables that lack statistics. The statistics collection will be time-consuming and proportional to the number of dictionary tables whose statistics are lacking or have changed during the upgrade process. When you upgrade from Oracle 9i, you can decrease the database downtime by collecting statistics for the dictionary tables prior to the upgrade.