Use the analyze-based approach to evaluate the importance and staleness of statistics, and to prioritize the statistics submitted for collection.
During the collection process, statistics most in need of collection are submitted first. This makes sure that if a collect job does not complete in the allotted duration, then the higher priority statistics are more likely to have been collected.
If using the analyze-based approach, make sure the collect lists are automatically generated.
You can prioritize individual statistics by setting the user importance.
- Enable database query logging features (Requires DBQL option with STATSUSAGE. XMLPLAN is optional.) at least one week prior to running analyze jobs.
- Create an analyze job.
Analyze all statistics on the system and select the Use DBQL option.
- Schedule the analyze job to run at least once a week, so data changes are captured.
- Access the Analyze job report once the analyze job has run.
Look for stale statistics. Verify that all stale statistics are scheduled for collection again in the near future.
- From the Statistics tab, do the following:
- Review the STALE STATS column to see which statistics are considered stale.
- For collect jobs, make sure STALE STATS is nearing zero.
If the value is not nearing zero, increase the duration of the collect job or schedule the collect job to run again in the near future.