Dynamic list prefetching

Dynamic list prefetching is used to prefetch only those pages that are accessed while a specific portion of a table is scanned.

This prefetch method maximizes the number of pages that are retrieved by asynchronous prefetching (while it minimizes synchronous reads) by queuing work until the required pages are loaded into the buffer pool.

The number of pages that each subagent can prefetch simultaneously is limited by the prefetch size of the table space that is being accessed (PREFETCHSIZE). The prefetch size can have significant performance implications, particularly for large table scans. You can use the PREFETCHSIZE clause on either the CREATE TABLESPACE or the ALTER TABLESPACE statement to specify the number of prefetched pages that are read from the table space when prefetching is being done. The value that you specify (or AUTOMATIC) is stored in the PREFETCHSIZE column of the SYSCAT.TABLESPACES catalog view. Although dynamic list prefetching typically prefetches up to PREFETCHSIZE pages at a time, this amount of prefetching might not always be possible and in some cases, the PREFETCHSIZE value might be automatically adjusted for performance reasons. If you observe I/O waits while a query is prefetching data by using dynamic list prefetching, try increasing the value of PREFETCHSIZE. Setting PREFETCHSIZE to a large value might increase memory requirements.