IBM 5695-DF1, 5655-DB2 manual Dynamic Prefetch, List Prefetch, Prefetch Quantity

instances that do not have total overlap, in which wait times will still appear in the accounting records.

Sequential prefetch can be used to read data pages, by table space scans or index scans with clustered data reference. It can also be used to read index pages in an index scan. Sequential prefetch allows CP and I/O operations to be overlapped.

Because sequential prefetch reads multiple pages in one I/O operation, it has an important performance advantage over the normal read for applications that process multiple sequential pages. The DB2 virtual buffer pool must be large enough to avoid situations in which prefetched pages are being stolen by another application before they are referenced.

10.2.3 Dynamic Prefetch

Standard sequential prefetch is established at bind time, when the optimizer establishes a sequential access path to the data. Sequential prefetch can also be initiated at execution time. DB2 uses a ’sequential detection’ algorithm to determine that pages are accessed in a sequential access pattern, and which activates sequential prefetch. This type of sequential prefetch is called dynamic prefetch. An algorithm is also used to disable the dynamic prefetch.

Dynamic prefetch occurs when the optimizer establishes a non-sequential access path to the data (for example: SELECT ... WHERE KEY = :variable); and the keys provided to the program are many, and in sequential order, or very nearly so. It provides the same advantages as sequential prefetch.

Dynamic prefetch requests are detailed in DB2 PM reports. For an example, see D in Figure 33 on page 109.

10.2.4 List Prefetch

List prefetch is used to prefetch data pages that are not contiguous (such as through non-clustered indexes). List prefetch reads a set of data pages that are determined by a list of RIDs taken from an index. Before the read is performed, the RIDs are sorted in sequential order, allowing clustered accesses. List prefetch can also be used by incremental image copy. List prefetch requests are detailed in DB2PM reports. For an example, see C in Figure 33 on page 109.

10.2.5 Prefetch Quantity

The sequential, dynamic, and list prefetch operations each read a set of pages. The maximum number of pages read by a request issued from an application program is determined by the size of the buffer pool used.

When the virtual buffer pool is very small, sequential prefetch is disabled. Prefetch is also disabled if the sequential prefetch thresholds is reached. This is explained in 10.2.7, “Sequential Prefetch Threshold” on page 107.

DB2 I/O Operations 105