Why a Single SQL Access Plan Snapshot Is Not Enough

Why a Single SQL Access Plan Snapshot Is Not Enough

An SQL access plan can be very useful when a job is slow. It shows how the SQL Optimizer chose to execute a statement at a particular moment.

But it is still only a snapshot.

The same SQL statement can use a different access plan later, even if the source code has not changed. The data may have changed, a table or member may have been recreated, an index may have become available or unavailable, or relevant runtime conditions may be different.

That means a plan captured after a performance problem has appeared does not necessarily explain why the problem appeared.

Why one plan is not the whole story

When an IBM i job has run acceptably for a long time and then suddenly takes much longer, it is tempting to inspect the current access plan and look for something unusual.

That can be the right first step. However, it does not tell us whether the statement used the same plan before the slowdown began.

Without that comparison, it can be difficult to distinguish between a plan that has always been used and a plan that changed shortly before the performance problem became visible.

A useful SQL investigation therefore needs more than the answer to: “What plan is being used now?”

It also needs to ask: “What plan was used before, when did it change, and what was recorded about that change?”

Plan history gives the investigation a starting point

IBM i records access-plan information in the Plan Cache. This provides valuable technical evidence, but the individual records are not designed as a simple historical explanation for a performance change.

GiAPA SQL Observer retrieves selected Plan Cache information over time. It makes it possible to see when an access plan changed and to retain the IBM-supplied reason codes associated with the recorded plans.

Those reason codes do not prove the root cause by themselves. They provide context for a more focused investigation.

For example, the recorded information may indicate that a table or member was recreated, that row counts changed significantly, that an index became unavailable, or that the Optimizer selected a different Open Data Path.

That is much more useful than beginning with a vague conclusion that “SQL became slower.”

A change is evidence, not a verdict

A changed plan is not automatically a bad plan. It may be entirely reasonable under the new conditions.

The important point is that a changed plan gives the technical team something concrete to examine. It connects a performance change to a particular time, a particular execution plan, and a recorded context.

This helps reduce guesswork. Instead of searching through every possible explanation, the investigation can begin with evidence of what actually changed.

The role of deeper SQL analysis

Historical plan information does not replace detailed SQL analysis. Once a relevant statement and plan have been identified, IBM SQL Performance Center remains the natural tool for examining that plan in depth.

The two approaches have different roles.

SQL Observer helps preserve history and identify where a change occurred. SQL Performance Center helps analyse the relevant statement and plan once that change needs a deeper technical explanation.

Together, they provide a more practical path from an unexpected slowdown to an evidence-based investigation.

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

‍