Dynamic plan stability - a Db2 12 greatest hit with John Campbell
Paul_McWilliams 110000JT36 Visits (5428)
Dynamic SQL statements are more susceptible to creating performance problems than static SQL. A full PREPARE occurs for every miss in the dynamic statement cache, and such SQL statements might go through query optimization several times per day. Whereas, with static SQL, the access paths are determined at bind time and the access path is stabilized and reused until the next BIND or REBIND operation. In the case of dynamic SQL, all it takes is a performance regression on a single high-volume SQL statement to result in a serious production performance problem.
Dynamic plan stability is a welcome new feature to bring some relief in the area of performance management for dynamic SQL statements. The goal is to provide consistent and more reliable performance. The sweet spot for dynamic plan stability is short-running dynamic SQL statements that run hundreds or thousands of time per day. Dynamic plan stability helps greatly with high-turnover periods in the dynamic statement cache, such as after the planned or unplanned recycle of a Db2 subsystem, or when hundreds or thousands of SQL statements that are related to an object are invalidated in the dynamic statement cache as a result of that object being subject to a RUNSTATS operation.
In Db2 11, a miss in the dynamic statement cache always requires a new full prepare, which is very CPU intensive and could possibly result in a bad access path. In Db2 12, you can stabilize SQL statements from the dynamic statement cache and the statements are persisted in the Db2 catalog. So, when there is a miss in the dynamic statement cache, no new full prepare is required. Rather, the previously prepared statement is loaded into the dynamic statement cache from the catalog. It also has the advantage that statements are invalidated by SQL DDL, just like static SQL packages.
A change of application compatibility (APPLCOMPAT) level, or changes to special registers such as DEGREE, OPTHINT, and others, result in a cache miss. There is currently no REBIND capability to "repair" such statements after they have become invalidated. You must wait for new stabilization. However, you can use some new EXPLAIN sysntax to capture EXPLAIN information for the invalidated statements:
EXPLAIN STABILIZED DYNAMIC QUERY STMTID nnn COPY INVALID"
Also, stabilized dynamic SQL statement groups can contain many statements. So, if you do FREE STABILIZED DYNAMIC QUERY STBLGRP(x) to free off all statements in a dynamic SQL statement group, you are not only purging those statements from the catalog; you are also invalidating those statements in the dynamic statement cache, which can cause a storm of full prepare activity.
Finally, stabilized dynamic SQL statements do consume more CPU than static SQL statements because a short PREPARE is still involved.
Watch a video
John Campbell is an IBM Distinguished Engineer for Db2 for z/OS development. Paul McWilliams is a technical writer for Db2 for z/OS.
Always get the latest news about Db2 for z/OS from the IBM lab! How to subscribe
Follow us on Twit