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.
