Tuning Subsystems › RID Pool › RID Pool Statistics to Monitor

RID Pool Statistics to Monitor

The three statistics to monitor for RID pool problems are as follows:

RIDs Over the RDS Limit

Indicates the number of times list prefetch is turned off because the RID list that is built for a single set of index entries is greater than 25 percent of the number of rows in the table. If this is the case, DB2 determines that, instead of using the list prefetch to satisfy a query, it would be more efficient to perform a table space scan, which may or may not be good depending on the size of the table accessed. Increasing the size of the RID pool does not help in this case. This is an application issue for access paths and has to be evaluated for queries using list prefetch.

There is one very critical issue regarding this type of failure. The 25 percent threshold is stored in the package/plan at bind time; therefore, it may no longer match the real 25 percent value, and in fact could be far less. It is important to know what packages/plans are using list prefetch and on what tables. If the underlying tables are growing, rebinding the packages/plans that are dependent on them should be rebound after a RUNSTATS utility has updated the statistics. Key correlation statistics and better information about skewed distribution of data can also help to gather better statistics for access path selection and may help avoid this problem.

RIDs Over the DM Limit

RIDs over the DM limit occur when over 28 million RIDs are required to satisfy a query. DB2 has a 28 million RID limit. The consequences of hitting this limit can be fallback to a table space scan. To control this issue, you have several options:

• Fix the index by doing something creative.
• Add an index better that is suited for filtering.
• Force list prefetch off and use another index.
• Rewrite the query.
• Use a table space scan.

Insufficient Pool Size

Indicates that the RID pool is too small.