The Magic of DB2’s Prefetch

5 Pages

Dynamic prefetch will always have a maximum of 32 for all buffer sizes greater than 1,000, regardless of how large your buffer might be. This short discussion of prefetch quantities is based on a 4k page size. The prefetch quantity needs to be adjusted for other page sizes. Chapter 39 of the DB2 9 Performance Monitoring and Tuning Guide (SC18-9851) contains a chart that covers the prefetch quantities for all page sizes.

Tip: If you decide it’s time to start using DB2’s larger page sizes, make sure the DSNZPARM keyword DSVCI on macro DSN6SYSP, “VARY DS CONTROL INTERVAL” on panel DSNTIP7, is set to YES, the DB2 default. Any table spaces created after this is turned on will have a VSAM control interval equal to the page size.

Two more buffer pool keywords that have an effect on prefetch are VPSEQT and VPPSEQT.

VPSEQT controls the percentage of the buffer pool (VPSIZE) that can be used for sequential pages, the sequential steal threshold for that pool. Set VPSEQT to zero and you completely disable prefetch for that buffer pool. If the pool is primarily used for highly sequential data (lots of prefetch), then make this value large, going as high as 99 percent. If the pool is going to be shared by both random and sequential pages, choose a VPSEQT value low enough to allow for adequate random I/O to be successful.

VPPSEQT is the sequential threshold for parallel sequential activity and this value is a percentage of the VPSEQT value. Setting VPPSEQT to zero turns off parallel processing for that buffer pool. Because VPPSEQT is a percentage of VPSEQT, setting VPSEQT to zero has the same effect as setting VPPSEQT to zero, regardless of the percentage VPPSEQT is set. Both of these values are modified using the ALTER BUFFERPOOL command.

The PREFETCH column in the plan table is set to “S” when sequential prefetch is picked. As of DB2 9, ACCESSTYPE should be equal to “R.” Be careful to not assume that the “R” in ACCESSTYPE is a bad thing. Although an index access often is an excellent access path, there are those occasions when not using an index could be the better way to get to your data. Always do the extra analysis to determine if it’s actually the better access path.

Clustering is another aspect of DB2’s data that’s important to prefetch processing. If the data is highly clustered, you can take advantage of prefetch and reduce I/O processing. This is an example of why your design is so critical to your application’s potential performance. Picking the wrong key can defeat the advantages of using prefetch.

With sequential prefetch being such a cool feature of DB2 for so many years, you may be surprised to learn the occasions when DB2 will choose sequential prefetch as the access path by bind are about to significantly decrease. That’s because of dynamic prefetch, which is playing a far more prominent part as of DB2 9. Although dynamic prefetch has been around a long time, it really hasn’t been front-page news until recently, when it’s been showing up as the access path of choice more often than ever.

Sequential prefetch works well, but has its shortcomings. Because it’s picked at bind time, once sequential prefetch is chosen, you’re stuck with it. This is important because it’s possible for the decision to use sequential prefetch to be based on inaccurate estimations. Next, sequential prefetch requires a triggering page, which is a multiple of the prefetch quantity (our example used 32 pages), and if it’s missed, you’re stuck reading one page at a time until the next triggering page is reached. Finally, sequential prefetch works only when fetching forward; it isn’t used for a backward scan, an important fact now that DB2 has backward index access.

Dynamic Prefetch

5 Pages