This article is Part 2 of a two-part primer about the SQL Plan Cache. If you haven't already read Part 1, I encourage you to do so before reading this article (see "Rock Your Query Environment: SQL Plan Cache Primer, Part 1"). 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.
Part 1 left us with a cliffhanger: We didn't get to cover the major aspects of how to use the SQL Plan Cache, System i Navigator, and DB2 for i–supplied procedures to keep the data center humming along at an optimal speed. In Part 2, I'll show how you can use event and statistics monitors, as well as plan cache utility procedures, to solve your toughest query engine mysteries.
After you read these two articles, if you need to move beyond basic knowledge of 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 City, and other locations throughout the year.
SQL Plan Cache: More Foundation Building
In Part 1, I gave you a brief optimizer history lesson and provided a basic description of how the plan cache works. I also showed you how you can efficiently probe the plan cache to gain insight into the optimizer. Now it's time to establish the remaining pillars of our foundation of plan cache knowledge.
The SQL Event Monitor is a tool you can use to capture information about access plans that are being removed from the SQL Plan Cache as a result of either too many plans per query or the plan cache size threshold enforcement. It's no accident that the name is similar to the SQL Performance Monitor: The SQL Event Monitor captures information about queries as they're removed from the SQL Plan Cache, using the same format as the output from an SQL Performance Monitor (aka Database Monitor). The common structure that all these types of monitors and snapshots use analysis (transferrable knowledge is a wonderful concept).
For the SQL Query Engine (SQE) to make good decisions regarding the choice of access plan, accurate, query-related statistics are essential. A background system job named QDBFSTCCOL collects statistics that help the SQE decide how to implement queries. By default, this job is always active on the system. A system value of the same name (QDBFSTCCOL) controls how statistics are gathered.
The SQL Plan Cache GUIs are great, but you can't use them to automate plan cache operations. For example, if you want to save the plan cache before an IPL, you need the administrator to create a snapshot before entering PWRDWNSYS. An ideal approach to handling this checklist-style task would include automation. Fortunately, DB2 for i provides many SQL Plan Cache utility procedures.
Capture Information with Event Monitor
The plan cache, as the name implies, is a cache of information. As with any cache, the contents change over time, with old information being removed and new information being added. Unfortunately, taking a snapshot or looking at the plan cache at any particular time means you could miss information that was in the cache but later removed.
What to do? Event Monitor to the rescue! The SQL Plan Cache Event Monitor, when enabled, captures query information about access plans as they're being removed from the plan cache. The captured information is put into the same format as a snapshot. The same tools, techniques, and analysis approaches that you use with snapshots can also be used against the event monitor output.
Figures 1 and 2 show the options available when you're starting a new Event Monitor. You can establish multiple Event Monitors by using different criteria. Event Monitors are similar to SQL Performance Monitors in that you can analyze them while they're active, or you can end them and review them later. When the system undergoes an IPL, active Event Monitors are automatically closed out.
The statistics manager background system job QDBFSTCCOL may become noticeable if you look for active jobs on the machine. The QDBFSTCCOL job might consume more CPU when:
- A new file is introduced to the system, such as with a large restore. A new file introduced to the system means it must have statistics created over it.
- Existing statistics have become stale because records have been added or changed. QDBFSTCCOL refreshes those stale statistics.
- A set of new queries is running on the system. New queries may require that additional statistics be collected. You may want to run the queries once, let the QDBFSTCCOL finish collecting new statistics, and then run the queries again. The query optimizer may be able to choose a better implementation after the statistics are available.
The QDBFSTCCOL system job cannot be ended. As I mentioned earlier, you can use the QDBFSTCCOL system value to control how often the statistics manager runs. Even though it's common for the QDBFSTCCOL to be frequently active, it shouldn't have a detrimental effect on overall system performance.
You should keep the QDBFSTCCOL system value set to *ALL. Although some administrators might feel the urge to turn off statistics collection in order to control system activity, there's far more benefit to be realized by leaving the system configured to allow statistics collection. In fact, administrators who turn off statistics collection usually cause system problems by forcing the database to use stale statistics or no statistics at all, resulting in poor plan decisions by the optimizer.
After you understand the role, purpose, and importance of the SQL Plan Cache, you might end up with a strong opinion about how you want to integrate (or institutionalize) the tracking and management of the SQL Plan Cache into your data center operations. Figure 3 lists the DB2 for i–supplied procedures that are the key enabling element to achieving such integration. To fully understand the authorization requirements for these procedures, read the article "Improved Security Controls Open Door to DB2 for i Tool Usage" listed in the "Find Out More" section below.
Most of what I describe in this article is generally available to IBM i customers on IBM i releases 6.1 and 7.1. The QSYS2.REMOVE_PERFORMANCE_MONITOR() procedure was delivered on IBM i 6.1 - DB2 for IBM i PTF Group SF99601 Level 26 and 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 the "Find Out More" section below (note that these are the same resources I listed in Part 1's "Find Out More" section; I've provided them again here for your convenience).
Ready, Set, Solve
Besides providing a system-wide construct for improved query performance, the SQL Plan Cache makes life a lot easier for the database performance analyst. You need only approach the SQL Plan Cache to easily observe and interrogate active queries and review the performance characteristics of queries, as long as they used the SQE and haven't been pruned from the plan cache.
This two-part primer should help you frame the pertinent topics and clarify best practices related to the SQL Plan Cache. Database technologies have large topical areas with extensive educational resources. Even though the SQL Plan Cache contains wide-ranging and complex query information, I hope I've succeeded in showing you how easy it is to use.
You're now well equipped to approach the SQL Plan Cache and improve your understanding of query execution on DB2 for i. Use the insight you gain to solve mysteries and whodunits—and truly optimize your SQL query environment.
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" (February 2007)
Database performance and query optimization (IBM DB2 for i 7.1 book)
DB2 for i SQL Performance Workshop (payable IBM workshop)
"The SQL Plan Cache Uncovered" (May 2007)