DB2 tablespace scans and prefetch

Synchronous I/O versus prefetch

A synchronous read brings one page per I/O and the thread waits. Prefetch is Db2's way of saying “we will need a run of pages soon” and issuing asynchronous multi-page I/Os into the buffer pool ahead of the application. Elapsed time drops when prefetch guesses right. CPU and buffer steal can rise if prefetch reads pages nobody uses (for example a scan you abandon after ten rows without telling the optimizer).

IBM describes three prefetch styles you must be able to name in an interview and on a monitor report:

Table scan (tablespace scan)

A table scan (PLAN_TABLE ACCESSTYPE = R, historically “relational scan”) reads the table's data pages. On a universal or segmented table space, Db2 scans pages that belong to that table, not every object that happens to share a space. ACCESSNAME is blank. Sequential prefetch is the normal I/O pattern (PREFETCH = S). Predicates are applied to each row as it is read—there is no index start/stop key.

When the optimizer prefers a scan:

• No usable index exists
• The table is small (scanning it is cheaper than probing an index plus data)
• A large fraction of rows qualify—index RID access would random-read most of the table anyway
• Predicates are not indexable, so an index would not reduce the pages enough

A scan is not an insult from the optimizer. It is the wrong answer when you have a selective equal on a unique key and still see R—that usually means the predicate is not matching (stage 2, wrong type, or no index). OPTIMIZE FOR n ROWS or FETCH FIRST can discourage a scan-plus-prefetch plan when the application will stop after a handful of rows.

1
2
3
4
5
-- No WHERE: a table scan is expected on a modest table
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE;
 
-- PLAN_TABLE: ACCESSTYPE = 'R', PREFETCH = 'S', ACCESSNAME blank

Sequential prefetch

Sequential prefetch is the scan workhorse. When the target table space (or sequential multi-table segmented access without an index) is entered, Db2 reads ahead by prefetch quantity—historically two prefetch quantities to start, then another quantity each time a trigger page is touched, trying to stay at least one quantity ahead. For 4 KB pages the quantity is often up to 32 pages (256 KB), depending on buffer pool VPSEQT and prefetch quantity settings.

Sequential prefetch is largely a bind-time choice for access paths that will read pages in physical order. If you only need 10 rows of a 10,000-row result, prefetch may still charge ahead unless OPTIMIZE FOR / FETCH FIRST told the optimizer the truth. Sequential prefetch and dynamic prefetch are different mechanisms; do not use the names interchangeably.

Sequential detection and dynamic prefetch

Sequential detection is the runtime algorithm that watches the pattern of page numbers being read. If pages look clustered or sequential, Db2 treats them as a sequence. If they jump around, they are random.

Dynamic prefetch uses that algorithm: multi-page asynchronous I/O for the sequential pages, synchronous I/O for the random ones. It is the usual prefetch for many index-to-data paths where clustering is mixed—some ranges of the key sit together on disk, some do not. Dynamic prefetch can turn on and off during one statement as the pattern changes.

This is why a “matching index” is not always random I/O. If the clustering index matches the predicate and CLUSTERRATIOF is high, data pages come in order and dynamic or sequential-style prefetch pays off. If the index is unclustered, you see more synchronous getpage waits unless list prefetch takes over.

Random access

Random access means the next page is not the neighbor of the last one. Typical case: unique index lookup of one row (a few index pages plus one data page), or nested loop inner-table lookups with unclustered keys. PLAN_TABLE PREFETCH is often blank. Accounting shows synchronous reads. Random is the right I/O for true singleton OLTP. It is the wrong I/O for “read 30% of the table through an unclustered index”—that is when list prefetch or a scan should have won.

List prefetch

List prefetch reads a set of data pages named by RIDs, not a physical sequential run. Db2 also uses it for non-consecutive index leaf pages (from non-leaf information) and for LOB pages (from the LOB map). It does not use sequential detection. Instead it:

1. Collects RIDs from a matching index scan (or from multiple indexes, or from the log)
2. Sorts the RID list by page number
3. Issues asynchronous I/Os for those pages (often in 32-page-style bundles), trying to stay ahead

PLAN_TABLE shows PREFETCH = L. List prefetch is attractive when qualified rows are not in clustering order, when skip-sequential access is sparse, or when DATAREPEATFACTORF says repeating the same pages would be expensive without a sorted RID list. Multiple-index access (ACCESSTYPE M) almost always involves list prefetch of the surviving RID list.

RID list and RID pool

A RID is the record identifier of a row (page and slot). A RID list is a collection of those identifiers, often sorted, sometimes ANDed (intersection) or ORed (union) with another list. Building that list requires memory: the RID pool.

• MAXRBLK — maximum RID pool size (ZPARM)
• MAXTEMPS_RID — whether / how RID processing may continue in work files if the pool is short

The RID pool is also used for hybrid joins, multiple index access, list prefetch, and some multi-row uniqueness enforcement. It is allocated as needed, up to the maximum.

When RID processing fails:

• Not enough RID pool storage
• Work-file limits
• RDS limit — if too large a fraction of the table's RIDs qualify (a classic figure is about 25%), Db2 may terminate the RID list because a scan would be cheaper. Stale stats after huge table growth make this fire incorrectly until RUNSTATS and REBIND

Accounting and Statistics “RID list” sections count these terminations. Non-zero RID failures on a workload that was supposed to list-prefetch are a tuning ticket: enlarge MAXRBLK, fix stats, or accept a scan.

1
2
3
4
5
6
7
-- Selective OR of two indexed columns: candidate for MX / MI or MU + list prefetch
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
    OR EMPNO = '000010';
 
-- Look for ACCESSTYPE M / MX / MI / MU and PREFETCH L

Hybrid join

Hybrid join is PLAN_TABLE METHOD = 4. Think of it as a nested loop that does not probe the inner table once per outer row with random I/O. Instead Db2:

• Scans the outer (composite) table
• For qualifying outer rows, finds inner RIDs (typically via an index on the join column)
• Builds a RID list, sorts it, and list-prefetches inner data pages
• Concatenates inner rows back to the outer rows

Hybrid join needs join columns (unlike nested loop, which can nest even for a Cartesian-style nest). It shines when the inner index is not well clustered: you still use the index to find RIDs, but I/O to data is list prefetch instead of a storm of synchronous inner gets. It consumes RID pool. If the RID list is huge or the pool is short, hybrid join can degrade—the same failure modes as other RID processing.

Nested loop and merge scan are covered in detail on the join methods page. Remember hybrid here because its performance story is prefetch and RIDs, not just “another join name.”

Putting I/O together

When you read a slow query:

1. ACCESSTYPE R + PREFETCH S — expected scan; ask whether too many rows really qualify
2. ACCESSTYPE I + blank prefetch + high sync I/O — random index-to-data; check clustering and whether list prefetch should have been chosen
3. PREFETCH L — RID list; check RID failures if elapsed is bad
4. METHOD 4 — hybrid join; same RID questions plus join cardinality

Buffer pool VPSEQT (sequential steal threshold) and deferred write behavior affect how much prefetch you can absorb. Prefetch that fills the pool and then steals its own pages is a pool-sizing problem, not an optimizer insult.

Explain It Like I'm Five

A tablespace scan is reading every page of a picture book from page 1 to the end. Sequential prefetch is Mom bringing the next ten pages before you ask. Random access is jumping to page 47, then page 3, then page 90, one trip per page. Sequential detection is Mom noticing “oh, you are reading in order now” and starting to bring stacks, or “you are jumping again” and stopping. List prefetch is writing down all the page numbers you need, sorting them, and fetching those pages in handfuls. The RID pool is the scrap paper those page numbers are written on—if you run out of scrap paper, Mom gives up and reads the whole book. Hybrid join is using the index to write the scrap-paper list for the inner table, then fetching those pages in handfuls.

Exercises

1. EXPLAIN a SELECT without a WHERE on a sandbox table. Confirm ACCESSTYPE R and PREFETCH S.
2. Compare Accounting sync I/O versus prefetch pages for a clustered matching index versus an unclustered one.
3. Find MAXRBLK and MAXTEMPS_RID in your ZPARM documentation.
4. On a monitor Statistics report, locate RID list failures (storage vs RDS limit).
5. EXPLAIN a two-table join and identify METHOD 1 vs 2 vs 4. For METHOD 4, check PREFETCH on the inner table.