Detailed index statistics

A runstats operation for indexes with the DETAILED option collects statistical information that allows the optimizer to estimate how many data page fetches will be required, depending on the buffer pool size. This additional information helps the optimizer to better estimate the cost of accessing a table through an index.

Detailed statistics provide concise information about the number of physical I/Os that are required to access the data pages of a table if a complete index scan is performed under different buffer pool sizes. As the runstats utility scans the pages of an index, it models the different buffer sizes, and estimates how often a page fault occurs. For example, if only one buffer page is available, each new page that is referenced by the index results in a page fault. In the worst case, each row might reference a different page, resulting in at most the same number of I/Os as the number of rows in the indexed table. At the other extreme, when the buffer is big enough to hold the entire table (subject to the maximum buffer size), all table pages are read at once. As a result, the number of physical I/Os is a monotonic, nonincreasing function of the buffer size.

The statistical information also provides finer estimates of the degree of clustering of the table rows to the index order. The less clustering, the more I/Os are required to access table rows through the index. The optimizer considers both the buffer size and the degree of clustering when it estimates the cost of accessing a table through an index.

It is difficult to identify these conditions without previous knowledge or without forcing an index scan under varying buffer sizes and then monitoring the resulting physical I/Os. Perhaps the least expensive way to determine whether any of these conditions exist is to collect and examine the detailed statistics for an index, and to retain them if the resulting PAGE_FETCH_PAIRS are nonlinear.

When you collect detailed index statistics, the runstats operation takes longer to complete and requires more memory and processing time. The SAMPLED DETAILED option, for example, requires 2 MB of the statistics heap. Allocate an additional 488 4-KB pages to the stat_heap_sz database configuration parameter setting for this memory requirement. If the heap is too small, the runstats utility returns an error before it attempts to collect statistics.

CLUSTERFACTOR and PAGE_FETCH_PAIRS are not collected unless the table is of sufficient size (greater than about 25 pages). In this case, CLUSTERFACTOR will be a value between 0 and 1, and CLUSTERRATIO is -1 (not collected). If the table is relatively small, only CLUSTERRATIO, with a value between 0 and 100, is collected by the runstats utility; CLUSTERFACTOR and PAGE_FETCH_PAIRS are not collected. If the DETAILED clause is not specified, only CLUSTERRATIO is collected.