If you’re like me during the Christmas season, you’re devoting a fair amount of brain cycles and time trying to come up with that perfect gift idea for a family member. There are obvious Christmas gifts to avoid like clothes for little kids, a gym membership for your spouse, etc. Then, there’s the controversial gift of cash which can lead to the question of how much thought you put into the gift (if you don’t think cash is controversial, try your own internet search on: is cash a good Christmas gift).
Luckily, Db2
for i has avoided all that controversy and debate by giving you the gift of cache
instead of cash. Even better, you don’t have to wait until Christmas Day for
this gift since the Db2 query engine has already given you the gift of cache - way
back in V5R2!.
Several
months ago, I highlighted one of the cache gifts – the Plan Cache. This month, I’m going to highlight some under the radar
caching associated with Plan Cache that Db2 performs automatically to improve
the performance of frequently run queries on your system.
Occasionally,
the access plan (aka, query plan) constructed by the query optimizer utilizes temporary data structures and access methods to implement your query. For example, a
temporary sorted list may be used to perform an in-memory sort if your query
contains an ORDER BY clause like the following.
SELECT ordID, ordQty FROM orders
WHERE ordCustID = :inpID ORDER BY ordDate
When a query
is done running and the query plan includes a temporary data structure, Db2
sometimes decides to leave that temporary data structure on the system
populated with the data from your table. The reason that it decides to leave your table
data cached in temporary data structures is that it can improve performance on
the next run of that query.
If that
query is run again and the data in the underlying table has not been changed,
Db2 can skip the work of repopulating the temporary data structure with your
table data. Depending on the amount of table data being copied, this can
provide a nice performance boost to the next run of the query. If a query
contains predicates that reference parameter markers or host variables like ordCustID
= :inpID in this example query, then Db2 will also validate that the query
is searching for the same value In addition to verifying that the table data is
unchanged. If a different host variable or parameter value is being searched
for by the query, then the cached data in the temporary data cannot be used.
The Db2
query engine provides feedback when it boosts your query performance with
cached temporary data structures on a couple of different interfaces. If you’re analyzing a query plan with Visual
Explain (VE), you can see below that the highlighted Final Select icon has a
couple of fields in the Actual Runtime Information section providing
feedback on the use of cached temporary data structures.
VE provides a count of the number of times a cached temporary result was used during the execution of the query. If Db2 was unable to reuse the cached data in a temporary data structure, then it provides the last reason that the cached data could not be used. For this query, the cached data was not able to be reused because the rows in the table had had changed (e.g., Insert, Update or Delete) since the temporary data structure had been populated.
The live
Plan Cache viewer in the IBM i Access Client Solutions (ACS) SQL Performance
Center can also be used to get feedback on the reuse of cached data from
temporary data structures. After you launch the ACS SQL Performance Center, the
live Plan Cache viewer is accessed by clicking on the Show Statements
button. As you can see in the figure below, this interface allows you to see
cached results feedback at a statement level. Most likely, you’ll have to use
the Show Statements Columns editor to get your Total Cached Results Used column
to display next to the query text.
In this case, half of the query runs were able to use the table data cached in a temporary data structure instead of spending time and resources copying the same data into the temporary data structure. The application did nothing but run the query 8 times and Db2 automatically boosted the performance on half of those runs with its temporary data structure caching.
As you’ve
learned here, your mileage may vary on the frequency of cached results on your
system based on how often the underlying tables are changed and how often the
same host variable and parameter marker values are searched. Our IBM Technology
Expert Labs team has noticed several instances were 10-15% of some query
executions on busy systems are benefitting from this cache as we’ve assisted
IBM i clients with SQL performance tuning.
This Db2 gift of cached temporary results is not a new gift, but hopefully you have a better understanding of the query performance benefits that this type of caching can provide. Merry Christmas and I look forward to talking to you in 2024!
