68 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
򐂰 Estimated frequent values list (Figure 4-5)
This list provides an estimate of the most frequent values and an estimated count for each
value. This information is shown on the Estimated Most Common Values page.
Figure 4-5 Estimated frequent value list
4.1.3 Automatic statistics collection
Most databases use statistics to assist with query optimization. They often rely on a database
administrator (DBA) to create the statistics and to refresh the statistics when they become
stale or no longer provide a meaningful representation of the data. With the goal of being an
easy-to-manage database, Statistics Manager is set, by default, to collect and refresh column
statistics automatically. Column statistics are automatically collected after an SQL statement
is executed based on statistics requests from the optimizer.
When Statistics Manager prepares its responses to the optimizer, it keeps track of which
responses are generated by using default filter factors, because column statistics or indexes
were not available. It uses this information while the access plan is written to the plan cache
to automatically generate a statistics collection request for such columns. As system
resources become available, the requested column statistics are collected in the background.
The intent is that the missing column statistics are available on future executions of the query.
This allows Statistics Manager to provide more accurate information to the optimizer. More
statistics make it easier for the optimizer to generate a good performing access plan.

Get Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.