Get savvy about SQL query performance by reading this clear guide
The SQL Plan Cache is a key operational construct at the center of DB2 for i query operations. The plan cache exists on every IBM i machine and gets used to ensure that query implementation is done as efficiently as possible when SQL queries are executed with the SQL Query Engine (SQE). Access plans are stored in a database-wide cache where they become eligible to be reused by any job executing the same or a similar query. This notion of building plans once and reusing them many times is a tried-and-true approach to realizing performance gains and enabling scalability.
The SQL Plan Cache is created, maintained, and used by DB2 for i. There's no hard requirement for you, as a database administrator, to understand the inner workings of DB2 for i or how the SQL Plan Cache enables DB2 for i to deliver optimal query performance and scaling. However, the plan cache can provide you with a wealth of information about the SQL query environment. A little knowledge of the SQL Plan Cache is a good thing for anyone in charge of running the data center, and it's downright required for anyone responsible for maintaining a performance-critical environment on IBM i.
This topic is extensive enough that I decided to split this article into two parts. As with any good primer, the goal of these two articles is to give you an understanding of the most important aspects of the topic. In Part 1, I explain how to approach, observe, interact with, and influence the SQL Plan Cache so that you can leverage it to help you not only get to the bottom of performance problems but also optimize your query environment. In Part 2, I'll show you how you can use event and statistics monitors, as well as plan cache utility procedures, to solve your toughest query engine mysteries.
After reading these articles, if you need to move beyond the primer level on DB2 for i SQL query performance and tuning, you might want to sign up for IBM's DB2 for i SQL Performance Workshop, which I highly recommend. This is a fee-based workshop that IBM offers in Rochester, New York, and other cities throughout the year.
CQE and SQE: Two Optimizers, One Database
A short history lesson is necessary at this point. DB2 for i includes two query engines: The Classic Query Engine and the SQE. The query engine includes an optimizer, which is responsible for determining the best database access for a given query, and a runtime component, which implements that access. The CQE came first and served database applications well for many years. In fact, it still serves well for certain SQL queries (before IBM i 7.1) and for non-SQL database file access.
The SQE has been around since IBM i 5.2. Over time and over OS releases, the SQE has matured and taken over the majority of query engine work. If you're using IBM i 6.1 or 7.1, you can measure query activity and volume, broken down by query engine. To learn more about this topic, read the "Gauge SQE use in DB2 for i 6.1" article listed in Find Out More, below.
The SQE has some important advantages over the CQE. Central to these advantages is that the SQE has a database-scoped work area in which query (access) plans are maintained and reused. The plans are automatically added and removed based on use and value. This database-wide work area is called the SQL Plan Cache.
SQL Plan Cache: Basics You Need to Know
The SQL Plan Cache contains access plans created and used by the SQE to implement an SQL query. Access plans can be reused across query executions by the same user or across users and jobs. Typically, there are at most three access plans kept within the cache for any query. When the SQE receives the task of executing a query, it searches the cache for a match. If the SQE finds existing access plans for a matching query, the SQE considers the access plans based on current statistical information. The SQE determines whether one of the existing plans can be used "as is" or whether a new plan needs to be built. To maintain the limit of three access plans per query, the SQE removes the least valuable plans from the cache based on criteria such as whether a plan remains valid or by comparing selectivity range or related temporary storage costs. A plan can become invalid in many ways; for example, as a reaction to changes in the database architecture if a view was deleted or redeployed with a definition that renders some access plans unusable.
The SQL Plan Cache is shipped with an initial size threshold of 512MB. When the access plans stored within the plan cache exceed the plan cache's size threshold, a plan cache pruning task executes in a background task (i.e., TASKNAME=DBOP-PLANCACHE) and looks for plans to remove from the cache. The pruner uses a least-valuable-plan algorithm and removes access plans until the active size of the plan cache is below the threshold size.
So those are the SQL Plan Cache basics you need to know for now. In Part 2 of this series, I'll cover the SQL Event Monitor, the QDBFSTCCOL statistics manager, and SQL procedural interfaces for the SQL Plan Cache.
Step Right Up and Ask Your Questions
One of the highlights and hallmarks of the SQL Plan Cache is that you can extract information about currently executing queries and about queries that have finished executing. Typical questions that arise in the datacenter include:
- What's going on with this job that's consuming a lot of resources?
- Why did an application run much longer than expected last night?
Let's consider these questions further. The first question asks about what's currently happening. Traditional approaches to isolate the scope of the question might include using Work Management commands or interfaces or possibly using a tool such as Job Watcher. When the analysis points to a long-running database query, the SQL Plan Cache provides several easy-to-use options to gather details and gain insight.
The second question is historical because the application with a long-running query has completed. If the query information hasn't been removed from the SQL Plan Cache, you can extract, study, and save the same detailed query information each time the application runs and gain insight.
SQL Plan Cache: Properties and Size
The System i Navigator SQL Plan Cache features are few in number, but they're robust in capability. The action names and user interaction are consistent with SQL Performance Monitors. Anything you've learned in one topic is transferrable to the others, and that's cool.
As Figure 1 shows, each database, whether an iASP or SYSBAS, has its own plan cache. Right-clicking SQL Plan Cache for a particular database and then selecting Properties brings up a Properties box, similar to the one you see in Figure 2. The SQL Plan Cache size threshold is the only modifiable property. The complete footprint or impact of the plan cache on system temporary storage is typically greater than the Current Plan Cache Size value. Additional temporary storage is needed to manage the cached runtime objects associated with the plans in the plan cache. The additional temporary storage is usually two to four times the current plan cache size.
Figure 1: System i Navigator: SQL Plan Cache features
Figure 2: SQL Plan Cache Properties screen
You can explicitly set the SQL Plan Cache size threshold to a different size. In 2011, IBM made enhancements to IBM i 6.1 and 7.1 to preserve the threshold size across IPLs. The plan cache contents (i.e., the various access plans) aren't preserved across IPLs or iASP vary off/on. There's no way to save and restore the SQL Plan Cache contents in such a way that the SQE can reuse the contents because the plan cache resides within temporary data structures. We currently have no workarounds other than reducing the number of IPLs (avoidance) or accounting for application and query warm-up after an IPL (tolerance). You can capture an SQL Plan Cache Snapshot at any point in time in order to analyze and understand the contents of the plan cache. In fact, capturing a snapshot before certain activities, such as installation of service, OS upgrades, and IPLs, is a best practice that I'll cover later in this article. The snapshot is there to enable a performance analyst to quickly and easily compare the before and after implementation of a query. SQL Plan Cache snapshots are currently a one-way road because snapshots can't be imported into the SQL Plan Cache.
On IBM i 7.1 (see the "Service Level" section at the end of this article for details), the SQE can automatically manage the SQL Plan Cache size threshold. If the size threshold hasn't been explicitly set, the SQE automatically increases it if the number of reused access plans indicates that a larger threshold would result in improved access plan reuse. The SQE compares the amount of temporary storage used by the plan cache against the machine size and automatically reduces the SQL Plan Cache size threshold to avoid overuse of temporary storage.
If you're explicitly controlling the plan cache size, strongly consider turning over control of the plan cache size management to the SQE. To do this, reset the plan cache size to default. You can accomplish this task either through System i Navigator by selecting Properties on the plan and resetting the plan size threshold, as shown in Figure 3, or by calling the SQL procedure CALL QSYS2.CHANGE_PLAN_CACHE_SIZE(0).
Figure 3: Enabling SQE management of the Plan Cache Size Threshold
When the SQE is managing the size threshold, the size reverts to the default (512MB) after an IPL, and auto-sizing begins again. This behavior lets the database properly adjust to the workload, which often varies over time. (Note that although I've mentioned the default threshold size twice in this article, I do so only to illustrate the current behavior and not to imply that the current default size will always remain the same.)
If plan cache sizing is unavailable to you, or if you choose to manually control the plan cache size, you should regularly review the Plan Cache Size Threshold, Current Plan Cache Size, and Total Plans Pruned values. A regular review of these data points will help you understand whether the threshold size is adequate or needs to be adjusted.
SQL Plan Cache: Snapshots
Snapshots are aptly named because they give you the ability to capture details at a specific point in time. When captured, the data can be reviewed at a later time or even on a different machine. A snapshot can include all plan cache entries or use filtering criteria to narrow the snapshot output. In either case, when the snapshot is created, it can be:
- Analyzed: Examine and understand the contents of the plan cache
- Interrogated: Use the Show Statements action with filtering criteria to find specific queries
- Compared: Use the Compare action to contrast two snapshots
- Saved: Share snapshots with analysts to easily answer important questions
- Restored elsewhere: View the snapshot on a different machine (the snapshot's external form is a physical file [*FILE])
Figure 4 shows the Snapshot window. Each filter selected adds another "and" condition to limit queries found within the cache.
Figure 4: SQL Plan Cache Snapshot screen
SQL Plan Cache Snapshot: Best Practice 1. Capture a complete snapshot prior to installation of service, upgrading OS levels, or making extensive application or index strategy changes. Some customers extend this best practice to include IPLs or whenever the type of workload on the system changes.
Why? If a performance critical query suddenly runs much slower, it's easier to isolate the reason for the change in performance if a before image exists for comparison. You can compare before and after plan cache snapshots or view a side-by-side Visual Explain to understand the differences in query implementation.
SQL Plan Cache Snapshot: Best Practice 2. Use a dedicated library or set of libraries as the destination for snapshots.
Why? Snapshots contain sensitive information (e.g., the queries executed, the host variable values used in the queries, environmental information) that you might want to protect. Using a dedicated library for snapshots and SQL Performance Monitors will ease the job of security administration by controlling who can work with those objects.
SQL Plan Cache: Show Statements
In addition to using snapshots, DB2 for i also supports the ability to Show Statements directly against the live SQL Plan Cache. This live view of queries is delivered with an abundance of information. The database automatically puts the statements that have the most cumulative runtime at the top, so you can quickly see which statements would be good candidates for performance tuning. In addition, Show Statements offers a set of optional filters that let you focus on specific query executions you might be interested in.
Figure 5 shows a simple example of limiting the list to the 25 statements with the largest total accumulated runtime. For each statement, you have many options to consider.
- Visual Explain gives you a pictorial representation of the plan that the optimizer chose, including the number of records selected, relevant index advice, and much more.
- Show Longest Runs, Show Active Jobs, and Show User History let you further investigate current and historical information about the execution of the plan.
- Work with SQL Statement launches a new Run SQL Scripts session and primes it with the query text. Use the Work with SQL Statement and Variables option if you want host variables substituted into the statement text.
- If there are statements you want to save for later analysis, highlight one of more statements and use the Save to New option to create a new snapshot of just those plans.
Figure 5: SQL Plan Cache: Show Statements screen
Most of what I've described in this article is generally available to IBM i customers on IBM i releases 5.4, 6.1, and 7.1. The improvement to preserve the user-configured SQL Plan Cache size threshold across IPLs was delivered on IBM i 6.1 - DB2 for IBM i PTF Group SF99601 Level 20 and IBM i 7.1 - DB2 for IBM i PTF Group SF99701 Level 9. The improvement to automatically manage the SQL Plan Cache size was delivered on IBM i 7.1 - DB2 for IBM i PTF Group SF99701 Level 14. Visit the DB2 for IBM i Group PTF planning page for DB2 for i PTF Group details. You can find a link to that page, along with many other relevant resources, in Find Out More, below.
How do I keep your attention and convince you to come back and read Part 2 of this series? Maybe a good cliffhanger would serve my purpose. As a fan of the fictional detective Ellery Queen (both in the TV series and within the mystery magazine bearing the same name), I know the value of a few good clues. Even the absence of information can be a clue unto itself.
The SQL Plan Cache and the tools described in these two articles are your clue generators. You've read Part 1, so consider yourself on probationary detective status. Until Part 2 appears, spend some time exploring the SQL Plan Cache by using System i Navigator. Observe the properties at different times. Become familiar with Show Statements and Snapshots. Try to find some of the most frequently executed queries and possibly delve into the murky mist to discover the query with the longest total accumulated runtime.
Part 2 illuminates how you can use the SQL Event Monitor, the statistics manager, and the DB2 for i–supplied plan cache utility procedures to solve query engine mysteries. Stay tuned!
Find Out More
The following resources contain information relevant to DB2 for i and the SQL Plan Cache.
"5 Essential Ways to Use iSeries Navigator - SQL Plan Cache" (System iNEWS, February 2007, article ID 835 at iProDeveloper.com)
Database performance and query optimization (IBM DB2 for i 7.1 book)
DB2 for i SQL Performance Workshop (payable IBM workshop)
"Improved Security Controls Open Door to DB2 for i Tool Usage" (System iNEWS, January 2011, article ID 65628 at iProDeveloper.com)
"The SQL Plan Cache Uncovered" (System iNEWS, May 2007, article ID 916 at iProDeveloper.com)