This Db2 for z/OS News from the Lab blog entry was originally published on 2018-06-11.

By John Campbell, Terry Purcell, and Paul McWilliams. This post is part of a series that provides John Campbell's perspective on some of the most popular new capabilities in Db2 12 for z/OS.

In Db2 12, you can expect to see increased use of list prefetch access, and possibly more use of hybrid join access paths by design, except where Db2 previously chose a sort-avoidance access plan, such as for statements that use SQL pagination. Db2 12 introduces enhancements to the optimizer cost model, to more closely reflect the true costs and benefits of list prefetch, especially for improving I/O performance for data access through indexes with low cluster ratios.

The main complaint against list prefetch is that it degrades performance when it is chosen for SQL statements in OLTP applications, because it must accumulate all qualified RIDs before fetching the rows to return to the application. It is particularly problematic for queries that contain an ORDER BY clause when an index exists that Db2 can use to avoid the sort. In many cases the Db2 optimizer does not know whether your application opens a cursor to fetch only the first 10, 20, or 50 rows to fill a single screen, or fetches the entire result. The OPTIMIZE FOR n ROWS or FETCH FIRST n ROWS clauses in the SQL statements can provide that clarity, but these clauses are often missing. For that reason, we've been careful with these optimizer enhancements to also enable Db2 to avoid selecting list prefetch when a query requires a sort and Db2 can instead exploit an index to avoid the sort.

Another frequent complaint is that large queries, or multiple concurrent queries, can consume all RID pool resources. In Db2 10, we increased the default RID pool size (the MAXRBLK subsystem parameter) from 8000 to 400000. However, a recent study of client ZPARM settings by Db2 development uncovered that many of our clients still under-size their RID pools—either by keeping that prior default, or by using another value that is still much less than Db2 recommends. If RID pool resources become constrained, Db2 can fall back to writing the RIDs to a workfile, but it has a performance penalty. So, avoiding failover to workfile has a performance benefit, and an adequately sized RID pool minimizes such failovers.

After the situations where list prefetch might result in regression are resolved, list prefetch is a very effective approach for improving the performance of synchronous random I/O. And this is of course the design point for list prefetch. The accumulation of potentially non-contiguous pages improves the elapsed time for retrieving those pages from disk and CPU savings result both from the consolidation into fewer I/Os, and from fewer getpages, because repeated random accesses to the same page converge to a single getpage for each unique data page. List prefetch can also reduce the need for more frequent table space REORGs when insert activity degrades clustering.

So, don't be surprised or alarmed if you see increased use of list prefetch in DB2 12, and size your RID pools adequately to get the most benefit from list prefetch.

Read more

For more information about these enhancements, see "List prefetch and hybrid join cost improvements" and "Runtime Adaptive Index" in IBM Redpaper: Db2 12 for z/OS Optimizer.

John Campbell is an IBM Distinguished Engineer for Db2 for z/OS development; Terry Purcell is the lead designer for the DB2 for z/OS Optimizer at IBM's Silicon Valley Lab and is recognized worldwide for his expertise in query optimization and performance with DB2 for z/OS; and Paul McWilliams is a technical writer for Db2 for z/OS.