|
Part 4: Statistics Manager (V5R2)
Within Version 5 Release 2 of OS/400, DB2 UDB for iSeries has a new, SQL Query Engine (SQE). As part of this new SQL query engine, a statistics manager component is responsible for generating, maintaining and providing statistics to the SQE optimizer. As mentioned earlier, sources for statistics within DB2 UDB for iSeries come from default values and/or indexes. With SQE, the optimizer has another source, namely column statistics stored within the table object.
During query optimization, the SQE optimizer will ask the SQE statistics manager a question, such as "how many rows contain CUSTOMER_NUMBER = 2358." The statistics manager will answer the question using the best source of information available at the time. The source of the answer can be default values, indexes, or columns statistics. If an index (radix or EVI) or column stat is not available to satisfy the SQE optimizer's request, the statistics manager will provide an answer based on default values. In addition, a column statistic collection is automatically requested. The column statistics will be generated by a low priority, background job; with the goal of having the column stat available for future execution(s) of this query. This automatic collection of statistics allows the SQE optimizer to benefit from columns stats, without requiring an administrator to be responsible for the collection and management of statistics, as is true for other RDBMS products. Even though it is not required, statistics can also be manually requested for iSeries users who want to take on the task of statistics collection and management, without waiting for the statistics manager to recognize the need for a column statistic. The column statistics that are generated are only used by the new, SQL query engine. The original, Classic Query Engine (CQE) continues to use only default values and indexes for statistics.
Comparing an index (used for statistics) to a column stat, you will find the storage required for column stat is much smaller. On average, a column stat takes up approximately 8K - 12K per column. This additional space will be reflected in the table's size. As the data changes in the table, a column stat is not maintained, and thus does not affect the I/O performance on the table. On the other hand, if the data in the table is changing a lot, then the stats can become stale or outdated. Indexes are maintained immediately as the data in the table changes. This allows the indexes to always provide up-to-date statistics. The cost of this maintenance might be reflected in slightly longer insert, update, and delete times.
If you want to avoid a possible first time degradation in performance after upgrading to V5R2, then you should proactively collect statistics by manually requesting statistics on columns that are frequently searched and that do not have indexes created over them. The searched column(s) must be the leading key columns in the index for that index to provide good statistics to the optimizer. If only statistics from indexes are available, then it is possible that SQE will generate a different access plan than CQE using the same index statistics.
With the statistics enhancements provided by SQE, there may be cases where indexes created primarily for giving the optimizer statistics, can now be dropped in favor of column statistics. This situation only applies to queries that exclusively use SQE. If the query uses CQE for optimization and execution, then the indexes will still be required to provide the statistics, and should not be dropped.
The introduction of automatic column statistics does not replace the need for a good indexing strategy. Remember, the goal of the optimizer is to provide the fastest access plan for a given request. While statistics help the optimizer reach this goal, the fastest access method may require the presence and use of an index.
More information on the V5R2 DB2 UDB for iSeries SQL Query Engine (SQE) enhancements can be found at: ibm.com/eserver/iseries/db2/sqe.html
and redbook: Preparing for and Tuning the V5R2 SQL Query Engine on DB2 Universal Database for iSeries
[Back | Next]
|
|
|
