SQL and Access Paths › Read Mechanisms › Sequential Prefetch

Sequential Prefetch

When your query reads data in a sequential manner, DB2 elects to use the sequential prefetch to read the data ahead of the query requesting it. DB2 launches asynchronous read engines to read data from the index and table page sets into the DB2 buffers. This prefetch loads the data pages into the buffers in expectation of the query accessing them.

The size of the buffer pool that is used determines the maximum number of pages that are read by a sequential prefetch operation. When the buffer pool is smaller than 1000 pages, the prefetch quantity is limited due to the risk of filling up the buffer pool. If the buffer pool has more than 1000 pages, 32 pages can be read with a single physical I/O.

Note: A value of S in the PREFETCH column of the PLAN_TABLE indicates the sequential prefetch.