The Stats Manager portlet can help prevent unnecessary statistic collection which may be contributing to an inefficient use of system resources. You may be collecting:
- Statistics that are not needed or provide no value
- Statistics that are needed but collected too frequently
Ensuring Unneeded Statistics Are Not Collected
Using the Stats Manager portlet, you can identify statistics that are not used by the Optimizer and stop collection of those statistics.
- Enable database query logging with the USECOUNT option.
- Create an analyze job.
- Enable the option to evaluate statistics to determine if they should be collected or deactivated.
- Set the length of time a statistic is not used in order for it to be considered inactive and recommended for deactivation.
- Manually run or schedule the analyze job to run.
The analyze job generates deactivate recommendations and these statistics are no longer collected by collect jobs.
Ensuring Statistics Are Collected at the Right Frequency
You can specify collect settings thresholds to decrease the frequency of statistics collection.
- Determine the statistics that are collected too frequently. For example, these could be statistics for data that is updated infrequently but the statistics are collected daily or are lower priority statistics that require additional time to collect.
- From the Statistics tab, drill down to locate the statistic.
- Edit the collect settings for the statistic.
- Change the collect age threshold, data change thresholds, or both.
- Select system-defined or user-defined thresholds to determine when to collect or skip collecting a statistic. System-defined thresholds are recommended initially, however user-defined thresholds provide more control over collection thresholds.
- [Optional] Review the collect job reports to verify that the statistics are skipped as expected based on the thresholds you set.