Filter
Top Products
DB2 I/O Operations 105
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.