Monitoring background statistics collection
The system value QDBFSTCCOL controls who is allowed to create statistics in the background.
- *ALL
- Allows all statistics to be collected in the background. *ALL is the default setting.
- *NONE
- Restricts everyone from creating statistics in the background. *NONE does not prevent immediate user-requested statistics from being collected, however.
- *USER
- Allows only user-requested statistics to be collected in the background.
- *SYSTEM
- Allows only system-requested statistics to be collected in the background.
When you switch the system value to something other than *ALL or *SYSTEM, the statistics manager continues to place statistics requests in the plan cache. When the system value is switched back to *ALL, for example, background processing analyzes the entire plan cache and looks for any existing column statistics requests. This background task also identifies column statistics that have been used by a plan in the plan cache. The task determines if these column statistics have become stale. Requests for the new column statistics as well as requests for refresh of the stale columns statistics are then executed.
All background statistic collections initiated by the system or submitted by a user are performed by the system job QDBFSTCCOL. User-initiated immediate requests are run within the user job. This job uses multiple threads to create the statistics. The number of threads is determined by the number of processors that the system has. Each thread is then associated with a request queue.
There are four types of request queues based on who submitted the request and how long the collection is estimated to take. The default priority assigned to each thread can determine to which queue the thread belongs:
- Priority 90 — short user requests
- Priority 93 — long user requests
- Priority 96 — short system requests
- Priority 99 — long system requests
Background statistics collections attempt to use as much parallelism as possible. This parallelism is independent of the SMP feature installed on the system. However, parallel processing is allowed only for immediate statistics collection if SMP is installed on the system. The job that requests the column statistics also must allow parallelism.