The Importance of Being Prepared in DB2 for z/OS by Doug Mullins

We all know the importance of avoiding Prepares in dynamic SQL.  But performance tuning often involves balancing one cost against another, so are there are times when incurring a Prepare cost is justified in the bigger picture?  There might be cases where this is true, but if you don’t measure the costs you’re balancing you might get a surprise.  I’ll show an example of something that surprised me below.

The Prepare

When any dynamic SQL runs, it must first undergo the Full Prepare process.  In a Full Prepare, the optimizer determines an access path and DB2 builds the control structure, known as the skeleton copy or SKDS, that is used to run the query.

The process of determining the access path and building the SKDS takes time and CPU, which is why any subsystem where more than a trivial amount of dynamic SQL runs will (or should) have dynamic statement caching enabled.  Dynamic statement caching allows Prepared dynamic SQL to be cached so that it can be run again without having to be Prepared.  The Dynamic Statement Cache (DSC) is enabled by setting ZPARM CACHEDYN to YES.  The size of the cache is specified with ZPARM EDMSTMTC.

With the DSC enabled, not all SQL needs to be Prepared.  The first time a given SQL statement is executed, it must go through the Full Prepare process (determine access path and build SKDS).  The SKDS is cached in the DSC and can be reused for subsequent executions of the same query.

When that same query is executed in another UOW, the SKDS will be found in the DSC and copied to local thread storage.  This is known as a Short Prepare.  This requires a lot less effort than a Full Prepare, as the access path has already been determined and the SKDS already exists and needs only to be copied.

What exactly is “the same query?”

When matching a query to one in the DSC, the queries must be exactly the same to the smallest detail – even including spaces (but not necessarily including literal values – more below).  Consider these two queries:

SELECT COL01, COL02
FROM TABLEA
WHERE COL03 = ?

SELECT COL01 , COL02
FROM TABLEA
WHERE COL03 = ?
 

These queries are different – the second query has a space between “COL01” and the comma whereas the first query does not.  Even though both queries will look identical once parsed by the optimizer, they are matched in the DSC before parsing (remember, the purpose of the DSC is to avoid all the work done by a Prepare).

Matching a query in the DSC also includes any ATTRIBUTES specified in the PREPARE.  DB2 adds the ATTRIBUTE value to the end of the SQL text before matching.  Thus, if the first query above were executed with ATTRIBUTES SKIP LOCKED DATA and later with ATTRIBUTES USE CURRENTLY COMMITTED, the second execution would not find a match in the DSC.  This is how the two queries would look when being checked for a match in the DSC:

---------------------------
SELECT COL01, COL02
FROM TABLEA
WHERE COL03 = ?
SKIP LOCKED DATA

SELECT COL01, COL02
FROM TABLEA
WHERE COL03 = ?
USE CURRENTLY COMMITTED

Thankfully, though, DB2 can be more forgiving now (as of v10) if you choose to use CONCENTRATE STATEMENTS WITH LITERALS.  With that in effect, DB2 will replace any literal values with an ampersand before checking the DSC for a match.  Thus even though these two statements have different literal values:

---------------------------
SELECT COL01, COL02
FROM TABLEA
WHERE COL03 = ‘ABC’

SELECT COL01, COL02
FROM TABLEA
WHERE COL03 = ‘XYZ’


before DB2 looks for a match in the DSC, it will transform both to:

---------------------------
SELECT COL01, COL02
FROM TABLEA
WHERE COL03 = &
 

Of course, there are some gotchas to be aware of when using CONCENTRATE STATEMENT WITH LITERALS.

  • If a statement has both literals and parameter markers it will not qualify for literal constant replacement.
  • Two otherwise identical statements may not match after literal constant replacement if there are other significant differences, such as a difference in literal data types.
  • For statements that do qualify, it requires two searches of the DSC to find a match.  First, DB2 searches for a match of the statement as coded (with the literal values).  Then, only if a match is not found will it search a second time for a match with literals replaced by ampersands.

Prepare avoidance

When the same query is executed a second (or third, or fourth, etc.) time in the same UOW, no Prepare of any kind is required.  The SKDS is already in local thread storage and can simply be executed.  If the KEEPDYNAMIC YES bind option is used, then the SKDS is cached in the thread’s Local Dynamic Statement Cache (subject to the limit set by the ZPARM MAXKEEPD), allowing the query to be executed in separate UOWs without a Prepare.  The tradeoff, though, is that retaining the SKDS in local thread storage through commits also requires that threads stay active through commits.  Thus, enabling KEEPDYNAMIC could increase the number of concurrently active DBATs and increase total thread storage.

Prepare avoidance is the goal for any frequently-executed query.  But the DSC has limited space to hold Prepared queries, and not all queries are executed frequently so their SKDS may eventually be flushed from the DSC (which is done on a LRU basis).

Real life requires choices

We’ve seen that DB2’s rules for matching statements in the DSC are pretty strict.  But it’s a messy world out there, and sometimes we have to make compromises in order to maximize performance.  I recently worked with an application that made one of those choices but got a surprise.

Imagine this.  An application has built a Created Global Temp Table (CGTT) that holds several thousand rows.  It now needs to perform several calculations on that data.  One of the first rules in performance tuning is to remove redundancy, so the designers of this application decided (reasonably) that using a single query to perform all calculations would be more efficient than coding several queries, each of which would have to scan through the same data.  The trade-off, though, was that this required a more complex query.  In fact, because the app generated each query for each transaction, and because the input to each transaction was highly variable, the generated queries differed so much that they tended to get flushed from the DSC before they could be reused.

That meant that most queries required a Full Prepare.  But how much could that cost for a query against a CGTT?  Surely the optimizer would take only a cursory glance and decide that the only possible access path was a table space scan, then the SKDS would be built and the query would run.

Turns out, there’s more to it than that.  When the app started running, our exception monitor was capturing some queries with very high Prepare costs – sometimes exceeding 2 seconds of CPU.  To try to understand this, I ran some benchmarks of various queries generated by the app.  As a gauge of the complexity of each query, I counted the number of parameter markers in each query.  The CPU cost of the Prepares is in the chart below:

preparecpu.jpg

 

This chart admittedly has too few data points, but the trend is apparent – the more complex the query, the higher the cost of the Prepare.

But why would this be true if the optimizer has no decision to make? Here’s a clue. The following chart shows snapshot data from the DSC.  The application I’m describing is APPUSR01.

cache.jpg

 

Note that the average SKDS size of APPUSR01’s 506 statements is over 1MB.  That’s huge, particularly compared to the average size of most other app’s SKDSs.  The clue here is that there’s more to a Prepare than just setting up the access path.  Remember that the SKDS is a control structure so, besides encoding the access path, the optimizer has to parse the SQL and build an SKDS that incorporates the logic of the SQL.  Complex SQL means complex parsing and complex logic, and that seems to have translated into larger SKDSs.  All that work has to be done during the Prepare, and that makes the Prepare for our simple table space scan expensive.

Further impact and resolution

Another thing worth noting in the above chart is the total size of the DSC used by each authid.  APPUSR01 dominates the cache, using more than 80%.  This means that our app may be spreading the pain – causing less frequently executed queries for the other apps to be flushed out of the DSC more quickly than otherwise, and thus causing those apps to incur a Prepare overhead that normally would be avoided.  With a shared resource like the DSC, all apps should play well together.

The solution to this problem was to break the highly variable single query into multiple, less variable queries.  This required more scans of the data in the CGTT, but resulted in smaller and less unique queries.  Because the queries were less unique, they tended to be matched in the DSC so Full Prepares were avoided much of the time.  Daily CPU usage for the app was reduced by over 60%.  

This experience demonstrates the importance of testing assumptions.  In the design phase we knew we would have to balance the cost of Prepares against the cost of multiple table space scans.  But this was not a case where incurring a Prepare cost was acceptable.  Contrary to expectations, what was assumed to be a trivial cost turned out to be quite expensive.  Always test your assumptions!

 


Doug Mullins has worked as a DB2 DBA, systems programmer and developer for 29 years. He has spent that time getting his hands dirty sifting through the nuts and bolts. He welcomes your thoughts at dougmullins@gmail.com

Recent Stories
Lessons Learned of Locking and Latches by Adrian Burke

Db2 for z/OS locking basics

A Beginners Guide to Locks, Latches, Claims and Drains in DB2 for z/OS by Steve Thomas