Dynamic prefetch has none of those shortcomings. When dynamic prefetch is picked at bind time, it can switch between sequential prefetch and index access based on the data pages accessed using sequential detection. Dynamic prefetch doesn’t rely on a triggering page, so there’s no chance of “missing it.” Dynamic prefetch also can scan both forward and backward, which is handy. Remember, full backward index scan is available as of DB2 V8. Finally, the dynamic prefetch engines can run in parallel. Parallel processing is almost always a good thing if you’re trying to reduce elapsed time.
As of DB2 9, dynamic prefetch is used for index scans and table access via table scans. A few of the utilities via APAR PK44026 also are recent converts to dynamic prefetch. Dynamic prefetch is enabled for the UNLOAD phase of REORG INDEX, the UNLOAD and BUILD phases of REORG TABLESPACE PART, the BUILD phase of LOAD REPLACE PART, and the RUNSTATS phase of RUNSTATS INDEX.
In addition, the changes to dynamic prefetch in DB2 9 apply to single table access, multi-table join, outer join, subquery, and union. All this is available in DB2 9 Conversion Mode (CM), formerly compatibility mode in V8. DB2’s traditional sequential prefetch, as of DB2 9, will only be used for table space scans.
The part that hasn’t changed is determining when you’re using dynamic prefetch. The plan table column PREFETCH will still contain a “D” when bind decides to use dynamic prefetch. However, you may have to check IFCID 0003 and IFCID 0006 to find out what actually happened if DB2 made the switch to dynamic prefetch at run-time. In this case, dynamic prefetch wouldn’t be reflected in the plan table.
Dynamic prefetch could have all kinds of performance advantages over sequential prefetch because it isn’t dependent on the optimizer making the correct decision the first time and allows the access path to be changed on the fly as data patterns change. It’s interesting that DB2 just doesn’t make dynamic prefetch its first choice for everything and then just do whatever is right at run-time. However, dynamic prefetch, using sequential detection, does incur a CPU overhead from monitoring page access patterns. For this reason, it’s not turned on in a table scan in which all consecutive pages are being read anyway. In the test performed for the DB2 9 Performance Redbook, there was up to a 50 percent improvement when performing an index scan using dynamic prefetch.
So what’s sequential detection? Although almost always referred to as dynamic prefetch today, the term dynamic prefetch was often used interchangeably with sequential detection when this function was first introduced in DB2 V2.3. Today, sequential detection is almost exclusively used to describe the mechanism that trips dynamic prefetch. Dynamic prefetch can “dynamically” switch from an index access to sequential prefetch using the sequential detection algorithm to determine if pages are being sequentially accessed. Regardless of what you call it, it’s a nice feature that lets the optimizer make an access path decision with a fallback plan changing how data pages are retrieved while an application runs.
Sequential detection can work on all page sizes (4K, 8K, 16K, and 32K) and can be used on leaf pages or data pages. Sequential detection also can be used to trigger dynamic prefetch for a backward index scan. If used to trip dynamic prefetch at run-time, there’s no way of detecting the access change in the plan table; it always tells you what was known at bind time.
Chapter 39 of the DB2 9 Performance Monitoring and Tuning Guide (SC18- 9851) contains a detailed explanation of how sequential detection works. This is my interpretation of that discussion.
Sequential detection is a two-step process. DB2 first needs to determine if the most recently accessed page is page sequential. If so, it then determines if the data is being sequentially accessed. Both calculations are performed on a moving target. When determining if a page is page sequential, DB2 checks to see if the page is p/2 (where p is the prefetch quantity) pages from the current page. If it is, it then checks to see if five or more of the last eight pages were sequentially accessed. If they were, it switches to sequential prefetch. The pages continuously checked move forward and are always checked. If it’s detected that the pages are no longer page sequential, then DB2 reverts to the original access path. DB2 will flip-flop between index and sequential access while continually checking to see if access is page sequential.