RID pool and sort pool
The RID pool is an area of storage used for sorting record IDs (RIDs) during execution of SQL that uses list prefetch (including hybrid joins and multiple index access).
The size of the RID pool is specified in an installation parameter and can be between 128 KB and 10,000 MB (if set to 0, RID list processing is disabled).
The sort pool is not part of the buffer pool. Sort pool storage is allocated separately for each concurrently active thread that requests sort processing.
RID pool problem
RID pool processing can fail under any of the following circumstances:
- The number of RID entries is greater than the RDS limit (25% of the table being accessed).
- The number of RID entries is greater than the Data Manager limit of 16 million rows (an absolute limit that cannot be changed).
- There is insufficient virtual storage.
- A process attempts to use more than half of the RID pool.
RID pool failures are usually a sign of poor resource allocation, high concurrent activity, or application SQL with access path problems
Sort pool problem
The size of the sort pool is limited to a range between 240 KB and 128 MB per thread (the default is 2 MB) and is specified in an installation parameter. So the amount of storage needed depends on the number of active threads at any time. Failures can result from the lack of available virtual storage.
Solution
Although little can be done to tune the RID and sort pools,
Pool Advisor
monitors them constantly and can spot problems before they become critical.
Pool Advisor can warn you of these situations or (if OPERTUNE for DB2 is installed) dynamically alter the pool allocations to prevent failures.
Related topic