Although a good way to efficiently process an unordered group of pages, list prefetch does come with rules. At static bind, if it’s determined that the list of RIDs could use 50 percent or more of the RID pool, list prefetch won’t be chosen. The RID pool is used for processing RIDs in DB2. It’s defined at installation and can be modified by changing your DSNZPARM member. Correctly sizing the RID pool will minimize the chances of RID pool failures. There are suggestions and formulas for determining the RID pool size in the DB2 Installation Guide. Setting the RID pool size to zero will disable RID processing and therefore disable list prefetch.
One additional bind time constraint on list prefetch is that it won’t be chosen by the optimizer if the table has been created with, or altered to use, the VOLATILE keyword.
At execution time, DB2 can abandon list prefetch if it accesses more than 25 percent of the rows of a single table. This last threshold, if reached, can really crush any chances of optimal performance. After already spending time processing data using list prefetch as the access method, when DB2 stops using list prefetch at run-time, DB2 has to fall back to sequential prefetch to access the data. DB2 also has a low-end check it performs for list prefetch. If DB2 is using an intersection of multiple RID lists and detects less than 32 RIDs, it stops the intersection and just uses the RIDs it has.
If you want to find out what list prefetch is actually doing, you can track IFCID 125, available with a class 8 performance trace.
If you want to try to influence the optimizer to not pick list prefetch, you can specify OPTIMIZE FOR n ROWS on the SQL SELECT statement. The OPTIMIZE FOR n ROWS clause will reduce the likelihood of list prefetch being picked.
There are reasons you may not wish to use list prefetch. One of them is the chance of increasing elapsed time by increasing the amount of processing that needs to be performed before a row is returned to the application. Another would be no index only access can be used in combination with list prefetch.
To determine if the optimizer would use list prefetch, you can check the PREFETCH column in the plan table for an “L.”
When used at the right time, prefetch can be a real asset to your application, helping it achieve its best performance. You must understand when it’s a benefit and when another access path may be a better choice. When adequate statistics are available, depend on the optimizer making the right choice. If you’d like to learn more about the basics of prefetch and how it all started, look for the article, “Managing IBM Database 2 Buffers to Maximize Performance,” which is available for download in Adobe Acrobat PDF format from IBM’s Website.