How I Fixed a Sudden Oracle SQL Slowdown by Binding Execution Plans (And What Went Wrong)

Background

On August 17, 2016, a routine email alerted me that a homepage‑loading SQL query had become suddenly slow on all regional Oracle databases, while the central database remained fast. The slowdown occurred without any code changes and affected a query that retrieves basic data.

Investigation

Initial clues were:

Sudden slowdown – no prior performance issues.

No code modification – the SQL itself had not changed.

Region‑only impact – identical SQL performed well on the central server but poorly on regional servers.

I obtained the SQL text and its SQL_ID from ASH, then examined the execution plans. The ASH data showed a stable plan, so the hypothesis of a plan deviation was rejected. Further checks confirmed that data volume, statistics, and indexes were identical across central and regional databases because data is synchronized via Oracle GoldenGate (OGG).

All regions were slow, and the business required a fix before the end of the workday.

Comparing Execution Plans

The regional execution plan (

) differed markedly from the central plan (

). The key difference was on line 20: the central plan used UNION ALL PUSHED PREDICATE on the view BAS_PROJECT_ALL_V, while the regional plan used a plain UNION ALL . This predicate push‑down reduced the cost from 15 322 on the region to 225 on the center, explaining the performance gap.

Plan‑Binding Attempt

To quickly resolve the issue, I proposed binding the central execution plan to the regional databases via a SQL_PROFILE. After the DBA applied the profile, the regional plan appeared to be bound (

), but the predicate push‑down was still missing, and performance degraded further (4–5 seconds instead of sub‑second).

Version Mismatch Discovery

An email from the DBA revealed the root cause: the central database ran a newer Oracle version that supports predicate push‑down, whereas the regional databases used an older version lacking that feature. Consequently, binding the plan could not introduce the missing optimization.

Final SQL Rewrite

Given the version limitation, I reverted to SQL‑level optimization. First, I tried a previously successful rewrite, but the SE rejected it as risky. I then introduced a WITH clause to pre‑fetch the user’s PROJECT_NUMBER list and join it to the view, resulting in a sub‑second execution (

). This approach was accepted by the SE because it involved only a logical restructuring of the query.

Conclusion

The case demonstrates that sudden performance regressions can stem from subtle differences in execution‑plan features across database versions. While plan binding can be a quick fix, version incompatibility may render it ineffective, necessitating careful SQL rewriting that respects business constraints and tight deadlines.