Snapshot Scheduling New Year's Resolution?

The start of a new year can be filled with scheduling related to New Year's resolutions. For example, if you made health-related New Year's resolutions, then there’s a good chance you'll be scheduling an appointment to sign up for a gym membership or scheduling your annual physical exam with a doctor. 

In 2023, I’d like to challenge you to make a New Year’s resolution to schedule Plan Cache snapshots on your system.  Scheduling a Plan Cache snapshot will not improve the health of your system or database, but it is a good proactive action to make the resolution of query performance issues easier.

Let’s start with a review of the Plan Cache and Plan Cache snapshot objects, so you can better understand the benefits of scheduling a snapshot.

The Plan Cache is used by Db2 for i to store the access plans (or query plans) created by the query optimizer. An access plan must be in place before a query can be run since it contains the details on what methods (e.g., table scan, index probe, etc.) and objects (e.g., index) will be used by Db2 during the runtime execution of a query.  As the “cache” part of the Plan Cache name implies, the contents of this object are dynamic. Thus, there’s no guarantee that the access plan used to run a query yesterday will still reside in the Plan Cache when that query is run again today.  

The dynamic nature of the Plan Cache is what drives the need for Plan Cache snapshots. The contents of the Plan Cache can be copied into a snapshot to provide a static repository of the query plans used by Db2 for i. The static nature of the Plan Cache snapshot makes it easy to analyze the performance and implementation of your queries without having to worry about an access plan disappearing on you.

The static view of your query plans provided by a snapshot not only can deliver insights on your current query performance and behavior, but it also enables comparisons of query performance and behavior. And this comparison capability is where the scheduling of snapshots provides value.

Let’s say that you have a query this week that’s running noticeably slower.  If you have a snapshot from last week and a snapshot from his week on your system, you could easily use Visual Explain to compare the implementation as shown in the following figure.

A quick review of this output reveals that the faster run of the query from last week shows an index being used while the slower run this week is using a table scan. Now, you can go review your database to see if someone accidentally deleted the index (or keyed logical file) that was being used in the query plan generated by Db2 last week.

If the comparison of the access plans doesn’t lead to the root cause of your performance problems, then you may have a situation that requires contacting IBM Support. The good news is that your snapshots can also help IBM more quickly resolve your performance issue. Maybe you’ve contacted IBM Support in the past about a performance problem and had a tough time answering their question: “How much slower is your report running?”.  Not only does a Plan Cache snapshot make it easy to answer that question, the IBM team can also use the snapshot data to more quickly understand the performance issue and come up with a resolution.

Thanks to the IBM i integrated job scheduler and Db2 Plan Cache services, scheduling the daily or weekly creation of a Plan Cache snapshot is pretty simple. Most job schedule entries call a CL program, so you just need to create a CL program that uses the RUNSQL CL command to invoke the DUMP_PLAN_CACHE service.  The following example copies the Plan Cache contents to the specified snapshot table: SOMELIB/SNAP1

RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE(''SOMELIB'',''SNAP1'')')

If you’re worried about the disk space consumed by the collection snapshots, remember that the snapshot is a Db2 table – thus, it would be easy to add logic to your CL program to perform a save with compression to a save file.

The advantages and simplicity of Plan Cache snapshot scheduling should be clear, so get 2023 off to a good start by getting your snapshots scheduled.