This month I decided to continue with a Plan Cache-related topic as a result of a recent discussion thread on IPL frequency on the IBM i Global TechXchange Community. System IPLs are relevant to the Db2 for i Plan Cache used by the SQL Query Engine (SQE) because it’s a temporary object that gets recreated each time the system is restarted. Before diving into IPL considerations for the Plan Cache, let’s first focus on how SQE uses the Plan Cache and the benefits it provides.
As I highlighted in my January entry, the Plan Cache is used by SQE 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. SQE’s usage of the Plan Cache as a single
repository for all access plans on the system benefits IBM i clients in several
different ways.
First, a centralized repository reduces the amount of system
resources used to build query plans. If you had the same SQL request run by
different programs, the Classic Query Engine (CQE) had to create and store a
separate access plan for each program. With SQE, one copy of the access plan can
be created and shared across multiple programs.
Second, the Plan Cache enables a tremendous Db2 performance
toolset with no overhead. Usage of plans from the Plan Cache is part of the
normal SQE query execution process, so there’s no additional tool that must be
run to collect data for detailed performance analysis with Visual Explain. The
IBM i Access Client Solutions (ACS) SQL Performance Center interface shown
below makes it fast and simple to perform analysis on a query plan. Just hit
the highlighted “Show Statements” button and find the query that needs
performance analysis.
Prior to the Plan Cache, Visual Explain analysis could only be performed if you had first collected database monitor data. This requirement was problematic because of the noticeable overhead caused by the database monitor collection process. This overhead meant that you couldn’t just keep a database monitor always running on your production system. When a query performance problem arose on your production system, Visual Explain could not be used to analyze the query because the required performance data wasn’t available. Then, when you went to collect the requisite database monitor data, you ran the risk of negatively impacting the performance of other workloads on your system. The Plan Cache automatically contains the data required to perform detailed analysis with Visual Explain without adding extra overhead to your system.
Lastly, the Plan Cache’s centralized plan repository
provides a foundation for self-learning query optimization techniques. One of
those self-learning query optimization techniques is Adaptive Query Processing or AQP for short. The ACS SQL Performance Center displays
a Plan Cache property, Number of Plans Rebuilt due to AQP, where you can
see how often this self-learning technology automatically has tuned the
performance of queries on your system.
Now that you know about the amazing benefits the Plan Cache
provides, hopefully it’s also clearer how these great benefits are somewhat
negated by frequent system IPLs.
The biggest issue is slower performance on Monday morning
after a weekend featuring a system IPL. An empty Plan Cache has the potential
to add to the Monday
blues for your end users since all the query plans that we’re being
reused by your applications and reports on Friday have to be rebuilt by the Db2
query optimizer on Monday morning when users start running your applications
and reports. Building access plans takes time and system resources which can
lead to sluggish system performance.
This performance slowdown can be further compounded if some
of your queries had been auto-tuned by SQE with the creation of maintained temporary
indexes (MTIs). These temporary indexes obviously also pull a disappearing act when
an IPL is performed. A query which previously relied on a temporary index will run
slower post IPL until SQE makes the decision to auto-tune the query again. In
addition, the recreation of these temporary indexes can cause additional
overhead on your production system.
So, what actions can you take to minimize the performance
impact of an IPL causing your Plan Cache to disappear?
If your system is frequently IPLed, then start by determining
if frequent IPLs are truly required. If the answer is we’ve always done it that way - that’s a good indicator you should keep investigating.
When the AS/400 was first launched, weekly IPLs were common because it was
possible for the system to run out of temporary addresses. However, that launch
was 35 years ago! Even though IBM has delivered enhancements over the years including
the switch to 64-bit RISC processors in 1995 which significantly expanded the
temporary address support, our IBM Technology Expert Labs team encounters a significant
number of clients still employing weekly IPLs when we’re helping them with SQL performance
analysis and tuning. If your company is hesitant to change this old practice,
make a small change in IPL frequency and evaluate the impact - for instance, move
from weekly IPLs to biweekly or monthly IPLs.
Regardless of the IPL frequency, proactively replacing the
MTIs on your system can minimize the post IPL query performance blues. In 2022,
the Db2 for i development team delivered the MTI_INFO
service that makes it really easy to identify the temporary indexes currently
on your system and to replace them with
permanent indexes. I’d recommend modifying your IPL preparation procedure to
include using the MTI_INFO service to replace the temporary indexes with
permanent indexes before they disappear at IPL time. The MTI_INFO service is supported
back to the IBM i 7.3 release.
The final action you can take is to alter your post IPL procedures
to include a warmup of key applications and reports. After an IPL on the
weekend, set up a process to run some of your key applications and reports prior to Monday
morning. This warmup action will pre-populate some of the Plan Cache to lessen
the performance pain when end users ramp up system activity on Monday morning.
Hopefully, one of these actions or a combination of them will help you maximize the performance benefits that the Plan Cache can provide to your application and reports. Please reach out, if you need assistance learning how to leverage Visual Explain and the SQL Performance Center to manage and tune query performance on your systems.
