RID List Processing

This topic shows detailed information about Statistics - RID List Processing.

The RID pool is used for:

List prefetch
Multiple index access
Hybrid joins

Db2 uses a matching index scan to collect those record identifiers (RID) that match the selection criteria and places them in a list in the RID pool. The list is sorted by page number, which is contained in the RID. Db2 then uses the sorted list to access the table by reading up to 32 pages per I/O and attempting to read ahead one block of 32 pages before use.

The RID pool is allocated dynamically as it is needed. The maximum size of the pool is determined by the ZPARM MAXRBLK.

The work file database is used to store a RID list when the RID pool storage cannot contain all the RIDs of the list. When RID pool storage overflow occurs for a RID list, Db2 attempts to store the RID list in work file storage instead of falling back to a relational scan.

The maximum number of RIDs (measured in RID blocks) that Db2 is allowed to store in the work file database is determined by ZPARM MAXTEMPS_RID.

Statistics - RID List Processing

The field labels shown in the following sample layout of Statistics - RID List Processing are described in the following section.


RID LIST PROCESSING          QUANTITY  /SECOND  /THREAD  /COMMIT 
---------------------------  --------  -------  -------  ------- 
SUCCESSFUL                    8680.8K  4667.07      N/C     7.28 
NOT USED-NO STORAGE              0.00     0.00      N/C     0.00 
NOT USED-MAX LIMIT               0.00     0.00      N/C     0.00 
NOT USED-NOT CONSTRUCTED        14.00     0.03      N/C     0.19
                                                                 
MAX RID BLOCKS ALLOCATED       147.00      N/A      N/A      N/A 
CURRENT RID BLOCKS ALLOCAT.      7.74      N/A      N/A      N/A 

MAX RID BLOCKS OVERFLOWED        0.00      N/A      N/A      N/A  
CURRENT RID BLOCKS OVERFL.       0.00      N/A      N/A      N/A  

STORAGE LIMIT EXCEEDED           0.00     0.00      N/C     0.00 
RDS LIMIT EXCEEDED               0.00     0.00      N/C     0.00 
DM LIMIT EXCEEDED                0.00     0.00      N/C     0.00 
PROC.LIMIT EXCEEDED              0.00     0.00      N/C     0.00 
OVERFLOWED-NO STORAGE            0.00     0.00      N/C     0.00  
OVERFLOWED-MAX LIMIT             0.00     0.00      N/C     0.00  
INTERRUPTED (HJ)-NO STORAGE      0.00     0.00      N/C     0.00  
INTERRUPTED (HJ)-MAX LIMIT       0.00     0.00      N/C     0.00  
SKIPPED-INDEX KNOWN              0.00     0.00      N/C     0.00

SUCCESSFUL

The number of times RID list (also called RID pool) processing is used.

During RID (RECORD ID) list processing, Db2 uses an index to produce a list of candidate RIDs, which is called a RID list. The RID list can be sorted and intersected (ANDed) or unioned (ORed) with other RID lists before actually accessing the data pages. RID list processing is used for a single index (index access with list prefetch) or for multiple indexes (multiple index access), which is when the RID lists are ANDed and ORed.

This field is incremented once for a given table access when RID list processing is used for index access with list prefetch, for multiple index access, or for both. For multiple index access, if a final RID list is obtained through ANDing and ORing of RID lists, the counter is incremented once, even if not all indexes were used by the RIDs in the multiple index access.

Background and Tuning Information

A nonzero value in this field indicates that Db2 has used list prefetch. If this is the case, check the access path selection.

Field Name: QXMIAP

This is an exception field.

NOT USED-NO STORAGE

The number of times Db2 detected that no storage was available to hold a list of RIDs during a given RID list process involving one index (single index access with list prefetch) or multiple indexes (multiple index access).

This field can be incremented during retrieval, sorting, ANDing, and ORing of RID lists for index access with list prefetch (single index). For single index access, this field can only be incremented once per access. For multiple index access, it can be incremented for every index involved in the ANDing and ORing of RID lists.

Field Name: QXNSMIAP

This is an exception field.

NOT USED-MAX LIMIT

The number of times Db2 detected that a RID list exceeded one or more internal limits during a given RID list (or RID pool) process involving one index (single index access with list prefetch) or multiple indexes (multiple index access). The internal limits include the physical limitation of the number of RIDs a RID list can hold and threshold values for the retrieval, ORing, and ANDing of RIDs.

For index access with list prefetch (single index), this field can only be incremented during RID list retrieval. For multiple index access, this field can be incremented during RID list retrieval, ANDing, and ORing. This counter reflects the number of times internal limits or threshold values were exceeded for the RID lists obtained directly from an index as well as for RID lists derived during the ANDing and ORing process.

Background and Tuning Information

Before you increase the RID list storage size, investigate the cause of the failure using the statistics record or the performance trace. You can specify the size for the RID list on the Db2 installation panel DSNTIPC.

Field Name: QXMRMIAP

This is an exception field.

NOT USED-NOT CONSTRUCTED

The number of times RID list processing was not used. This field is incremented once when RID list processing could not be used for a given table access for Index Access with list prefetch and/or for Multiple Index Access.

For example, RID list processing is used with multiple index access when performing ANDing. If the retrieved RID list of one leg exceeds a certain threshold and Db2 decides to not use this RID list, field QXMRMIAP is incremented by 1. But a final RID list can still be obtained based on the other legs of the ANDing. So, RID list processing is still used successfully.

This may cause some confusion. So, this counter QXRFMIAP show exactly how many times a final RID list could not be constructed and "RID list processing was not used".

Field Name: QXRFMIAP

MAX RID BLOCKS ALLOCATED

The highest number of RID blocks in use at any time since Db2 startup. This is a high-water mark.

Field Name: QISTRHIG

This is an exception field.

CURRENT RID BLOCKS ALLOCAT.

The number of RID blocks currently in use (snapshot value).

Field Name: QISTRCUR

This is an exception field.

MAX RID BLOCKS OVERFLOWED

This field is currently not set by Db2.

Field Name: QISTWFRHIG

CURRENT RID BLOCKS OVERFL.

This field is currently not set by Db2.

Field Name: QISTWFRCUR

STORAGE LIMIT EXCEEDED

The number of times the DBM1 storage was exhausted during RID list processing.

Background and Tuning Information

This failure occurs when the DBM1 storage limit is reached.

Field Name: QISTRSTG

This is an exception field.

RDS LIMIT EXCEEDED

The number of times when the number of RIDs that can fit into the guaranteed number of RID blocks was greater than the maximum limit (25% of table size).

Background and Tuning Information

Ideally, this value should be 0.

The matching index scan part of the RID list processing scanned more than 25% of the index. RID list processing is then terminated, the index scan is abandoned and normally replaced by a tablespace scan.

Reasons for this are:

Inaccurate or incomplete RUNSTATS statistics. To avoid this, you should collect all statistics on a regular basis, especially simple and correlated column statistics. Using RUNSTATS with SHRLEVEL(CHANGE) does not prevent access to data.
Optimizer error. In this instance, you could disable RID list processing by adding the clause OPTIMIZE FOR 1 ROW to the SQL statement, or force the access path to index only by adding the necessary columns to the index.

Field Name: QISTRLLM

This is an exception field.

DM LIMIT EXCEEDED

The number of times the number of RID entries required to process the SQL was greater than the physical limit. In Db2® Version 11 and earlier, the limit is approximately 26 million RIDs. In Db2 Version 12, the limit is approximately 16 million RIDs.

Field Name: QISTRPLM

This is an exception field.

PROC.LIMIT EXCEEDED

The number of times the maximum RID pool storage was exceeded.

The size is determined by the installation parameter RID POOL SIZE (Db2 install panel DSNTIPC). It can be 0, or between 128 KB and 10 GB. The general formula for calculating the RID pool size is:

(Number of concurrent RID processing activities) x (average number of RIDs) x 2 x (5 bytes per RID).

Field Name: QISTRMAX

This is an exception field.

OVERFLOWED-NO STORAGE

The number of times a RID list was overflowed to a work file because no RID pool storage was available to hold the list of RIDs.

Field Name: QXWFRIDS

OVERFLOWED-MAX LIMIT

The number of times a RID list was overflowed to a work file because the number of RIDs exceeded one or more internal limits.

Field Name: QXWFRIDT

INTERRUPTED (HJ)-NO STORAGE

The number of times a RID list append for a hybrid join was interrupted because no RID pool storage was available to hold the list of RIDs.

Field Name: QXHJINCS

INTERRUPTED (HJ)-MAX LIMIT

The number of times a RID list append for a hybrid join was interrupted because the number of RIDs exceeded one or more internal limits.

Field Name: QXHJINCT

SKIPPED-INDEX KNOWN

The number of times a RID list retrieval for multiple index access was skipped because it was not necessary due to Db2 being able to predetermine the outcome of index ANDing or ORing.

Field Name: QXRSMIAP