A Plan Snapshot Does Not Explain an SQL Performance Change

When an SQL-based job suddenly takes longer than usual, it is natural to inspect the access plan currently being used. That plan is valuable evidence, but it only describes one moment in time.

The more useful question is often not simply: “What plan is being used now?” It is: “What plan was used before, when did it change, and what else changed at the same time?”

The SQL Statement May Be Unchanged

With SQL, the developer describes the result that is wanted, while the SQL Optimizer decides how to obtain it. The same SQL statement can therefore use different access plans when relevant conditions change.

Those conditions may include the available indexes, the number of rows in a table, the state of a table or member, query options, or aspects of the job environment. A statement that has performed acceptably for a long time may therefore behave differently without any change to the source code itself.

This is not a defect in SQL. It is part of the flexibility that makes SQL powerful. But it also means that source code alone does not always explain why performance changed.

One Plan Is Only One Point in Time

An access-plan snapshot can show how the Optimizer chose to run a statement at a particular moment. It may reveal a table scan, the use of an index, a temporary table, or a particular Open Data Path.

What it cannot show by itself is whether the same statement used a different plan earlier. Nor can it show when the change occurred or which conditions may have caused the Optimizer to make a different choice.

That distinction matters when investigating an unexpected slowdown. If the only available evidence is the plan captured after the problem became visible, the investigation begins without knowing whether that plan is new, normal, or only one part of a longer pattern.

Why Plan History Matters

Historical plan data makes comparison possible. It allows technical teams to see that an access plan changed, when the change occurred, and what information was recorded with that change.

IBM i provides reason codes that can help explain why a plan was regenerated or why a different access method was selected. Depending on the case, these may point to such events as a recreated table or member, changed row counts, an added or unavailable index, altered query options, or a change in the Open Data Path.

A reason code is not a final diagnosis. It is a clue that gives the investigation a better starting point. A plan that changed after a table was recreated raises a different question from a plan that changed because an index was no longer available.

From Raw Records to Usable Evidence

The relevant information exists in the IBM i Plan Cache, but it is stored in compact technical records that are not designed to be read as a simple explanation of a performance change.

GiAPA SQL Observer retrieves selected Plan Cache data at defined intervals and retains it as historical evidence. It can expand the recorded information into a report that shows which plans were used, when they changed, and which IBM-supplied reasons were associated with them.

This does not replace IBM’s SQL analysis tools. It helps identify where and when a meaningful change occurred, so that the relevant statement and access plan can be examined in greater depth.

A Better Starting Point for SQL Investigation

SQL performance work becomes much more practical when it starts with evidence rather than guesswork. Instead of beginning with the broad statement that “SQL got slower,” the team can begin with a more specific question: which plan changed, when did it change, and what recorded condition may have influenced that change?

That does not remove the need for SQL expertise. It makes that expertise more effective by giving it historical context.

This article is part of GiAPA’s guide: Why SQL Performance Changes: What Access-Plan History Can Reveal on IBM i.

The full guide explains how access-plan history, IBM reason codes, GiAPA SQL Observer, and IBM SQL Performance Center can work together to make SQL performance changes easier to investigate.