RID list (BACCTDR)

SUCCESSFUL

Number of times RID pool is used (QXMIAP)

This counter is incremented every time the RID pool in the database services address space is used. The RID pool is always used to sort index keys by the RBA so that list prefetch (read-ahead buffering) can be enabled against data pages.

Tuning Tip: RID pool usage and sizing is critical for effective DB2 performance. The RID pool size is set by the systems installer in DSNZPARM SPRMRDP on the DSNTIPC installation panel. The DB2 RID pool is used for two purposes:

• To use multiple indexes to access a table

  The index entries are read, sorted into RID sequence, unioned (for OR processing), or intersected (for AND processing). Then the data from the table is accessed using list prefetch.

• To resolve an answer set from a non-matching index scan or a nested loop join

  Again, the RIDs are sorted into RID sequence and the data is read using list prefetch. Failure of RID pool storage causes DB2 to revert to table space scan processing. For large tables, this can cause extremely poor performance. DB2 will also end list prefetching as inefficient if more than 25% of the rows in the table must be accessed.

FAIL-STORAGE

Number of RID pool failures due to no storage (QXNSMIAP)

This counter is incremented when DB2 detects that no storage is available in the RID pool for a multiple index access or list prefetch request. The field can be incremented at any stage (retrieval, sorting, ANDing/ORing of RID lists for non-matching index scan processing) or incremented when each index of a multiple index access scan finds no more storage is available.

Tuning Tip: This situation is extremely undesirable because the portion of the query that would use the index falls back to table scan processing. If this situation occurs often, the size of the RID pool specified in SPRMRDP of the DSNZPARMs (on installation panel DSNTIPC) should be increased.

FAIL-LIMIT

Number of RID pool failures due to exceeding internal limits (QXMRMIAP)

This counter is incremented when DB2 detects that a single index would exceed 50% availability in the RID pool for a multiple index access or list prefetch request. The field can be incremented at any stage (retrieval, sorting, ANDing/ORing of RID lists for non-matching index scan processing) or incremented when each index of a multiple index access scan finds no more storage is available.

Tuning Tip: DB2 attempts to protect the RID pool from a large result set that would exceed 50% of the pool or reach the maximum size of 16 million RIDs for a single RID; in other words, it will cause a RID pool failure if the number of RIDS from any single index or any index involved in a multiple index access attempt exceeds 50%. If this situation happens often, attempt to find the offending application and change the SQL or increase the size of the RID pool.