Why SQL Performance Changes: What Access-Plan History Can Reveal on IBM i

SQL and native I/O do not ask the system to work in the same way. In a traditional native-I/O program, the developer normally decides which records to read and which access path to use. In SQL, the developer specifies the result that is wanted, while the SQL Optimizer chooses the access method.

The Optimizer can take many conditions into account, including the expected number of rows, the current environment and relevant query options. This gives SQL considerable flexibility, but it also means that a SQL statement cannot always be understood from source code alone. Two executions of the same statement may use different access plans when conditions have changed.

This is not a weakness in SQL. It is an important part of how the database works. But it changes what evidence is needed when runtime suddenly increases or behaviour becomes difficult to explain.

An access plan shows how the SQL Optimizer chose to run a statement at a particular moment. That is valuable, but one plan is only one point in time.

It does not show whether the same statement used a different plan earlier, or whether the conditions surrounding the execution have changed. The SQL statement may be unchanged, while the plan, the data, the job environment, or relevant query options are not.

This matters especially when a job that has run acceptably for a long time suddenly takes much longer. Without a history of the plans used over time, it can be difficult to know where the change began.

Historical Plan Cache evidence makes comparison possible. It can show when a plan was rebuilt or changed, and provide recorded reasons that give the investigation a more focused starting point.

IBM i stores access-plan information in the Plan Cache. That information can be very useful, but it is not presented as a simple explanation of what changed and why.

GiAPA SQL Observer retrieves selected Plan Cache data at defined intervals and records it over time. It then expands the compact technical records into a report that can show which access plans were used, when they changed, and which IBM-supplied reason codes were recorded.

This is important because a plan change can have many explanations. A table may have been recreated, row counts may have changed, an index may have been added or become unavailable, or the optimizer may have selected a different Open Data Path.

The purpose is not to replace IBM’s SQL analysis tools. It is to make the historical evidence easier to find and use, so that the right plan can be examined when a performance change needs a deeper explanation.

When GiAPA SQL Observer shows that an access plan has changed, the next question is not simply whether the new plan is better or worse. The first question is what the recorded change tells us about the conditions surrounding the statement.

IBM provides reason codes that can explain why a plan was regenerated or why a different access method was selected. These may point to changes such as a recreated table or member, a significant change in the number of rows, a new or unavailable index, altered query options, or a change in the Open Data Path selected for the call.

Such reasons do not replace an investigation. They provide the context needed to ask better questions. If the plan changed after a table was recreated, for example, that is a different starting point from a plan that changed because an index became unavailable.

This is where historical plan data becomes particularly useful: it ties a possible explanation to a specific time and plan, rather than leaving the team with a generic statement that “SQL got slower.”

IBM’s SQL Performance Center in Access Client Solutions is a powerful tool for analysing individual SQL statements and access plans in depth. It is the natural place to go when a specific plan needs detailed technical examination.

GiAPA SQL Observer has a different role. It records selected Plan Cache information over time and helps identify which plans changed, when the changes occurred, and which IBM-supplied reasons were associated with them.

In other words, SQL Observer helps preserve the history and reduce the search area. SQL Performance Center supports the deeper investigation once the relevant statement and plan have been identified.

The two approaches are complementary. One helps make change visible across time; the other provides the detailed analysis needed when that change should be understood more fully.

Historical SQL data is valuable only if it can be collected and retained without becoming a burden in its own right. That is why the scope of collection matters.

GiAPA SQL Observer allows the user to select which jobs should be observed and how often the relevant Plan Cache data should be retrieved. This makes it possible to focus on the jobs where SQL activity, data volume, or performance importance justifies closer attention.

Collecting every possible plan for every job at a high frequency would create unnecessary CPU usage, I/O, and storage needs. A more selective approach makes continuous observation practical while still preserving the evidence needed when an important performance change occurs.

The aim is not to collect data for its own sake. The aim is to make the right SQL history available when a question needs to be answered.

SQL brings a different kind of uncertainty to IBM i performance work. The statement may be unchanged while the access plan and the runtime conditions evolve around it.

That does not make SQL unmanageable. It means that useful SQL performance analysis needs a record of what happened over time, not only a snapshot taken after a problem has become visible.

GiAPA SQL Observer is designed to retain that historical context for selected jobs. It can show changes in access plans and the associated IBM reason codes, helping technical teams find the right starting point for further analysis.

When a deeper explanation is required, the relevant plan can then be examined with IBM SQL Performance Center. Together, these approaches make it easier to move from “SQL suddenly got slower” to a specific, evidence-based investigation.

SQL performance is not difficult because the optimizer is mysterious. It becomes difficult when an important change occurs and there is no reliable record of what the optimizer did before and after the change.

The SQL statement, the access plan, the runtime environment and the available data all form part of the explanation. A useful investigation needs enough history to compare these conditions rather than relying on a single plan captured after the fact.

GiAPA SQL Observer is intended to make that history available for the selected jobs that matter. It helps reveal plan changes and their recorded context, while IBM SQL Performance Center remains the tool for detailed analysis of the plan itself.

This creates a more practical path from an unexpected SQL slowdown to a focused technical investigation.