Help
Observation: LIST SEQUENTIAL PREFETCH
The DB2 optimizer has chosen LIST PREFETCH as part of the access strategy for your query. LIST PREFETCH reads a set of data pages determined by a list of RIDs taken from an index. LIST PREFETCH is chosen as part of the access strategy when any of the following is true:
-
A single index is being used that has a cluster ratio lower than 80%.
-
On indexes with a high cluster ratio, if the estimated amount of data to be accessed is too small to make sequential prefetch efficient, but large enough to require more than one regular read.
-
To access data by multiple index access.
-
To access data from the inner table during a hybrid join.
DB2 ends LIST SEQUENTIAL PREFETCH when the estimated number of RIDs to be processed exceeds 50% of the RID POOL, when the query is executed. This condition is known as RID POOL FAILURE, and can severely degrade the performance of your application. RID POOL FAILURES can be detected by analyzing the DB2PM Accounting Detail reports.
If a RID POOL FAILURE is detected, notify the DBA that the condition is occurring, and that the RID POOL size may need to be increased. Also, consider making the following modifications to your SQL in order to disable LIST PREFETCH:
-
Add an OPTIMIZE FOR n ROWS clause.
-
Add a GROUP BY clause.
-
Modify one or more host variable definitions.