SQL Plan Management (Excerpt from Upgrading Oracle Databases)
Upgrading to Oracle 26ai introduces major Oracle Optimizer advancements, including Real-Time SQL Plan Management, but maintaining SQL performance stability is still critical. As part of our 12 Days of Oracle 26ai series, we’re sharing a direct excerpt from the SQL Plan Management chapter of Upgrading Oracle Databases.
This chapter explains how Oracle captures, evaluates, and stabilizes SQL execution plans to prevent performance regressions during upgrades, data changes, and optimizer enhancements. The excerpt below focuses on the fundamentals of SQL Plan Management and how it has evolved into a real-time performance safeguard in modern Oracle releases.
Below is a selected portion of the chapter.
Excerpt: SQL Plan Management
When the Oracle Database executes a SQL statement, it follows a defined set of steps known as an execution plan. That plan lives in memory until it ages out. If the statement is reparsed or removed from memory, the optimizer may generate a new execution plan. Sometimes the new plan performs better, and sometimes it performs much worse.
Because execution plans are not persisted to disk and memory is finite, DBAs historically had no reliable way to know how a SQL statement behaved in the past. This is the problem SQL Plan Management (SPM) was designed to solve.
Introduced in Oracle 11g and included in both Standard and Enterprise Editions, SQL Plan Management provides a mechanism to stabilize SQL performance by ensuring that only known, verified execution plans are used. Since its introduction, Oracle has continued to enhance SPM in every major release, including Oracle 23ai and now 26ai.
At a high level, SPM consists of three main steps:
- Plan capture – storing relevant information about execution plans
- Plan selection – allowing the optimizer to choose only accepted plans
- Plan evolution – evaluating and accepting new plans that perform better
Execution plans are stored as SQL plan baselines in the SQL Management Base (SMB), located in the SYSAUX tablespace. By default, unused baselines are aged out after 53 weeks, though this behavior can be adjusted. Baselines may be accepted for use, and optionally marked as fixed to prevent alternative plans from being considered.
By default, Oracle will use accepted SQL plan baselines if they exist, behavior that has remained consistent since 11g. While this can be disabled using the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter, most environments rely on this feature to prevent unexpected plan regressions.
A Simple Manual Example
To demonstrate the core concept, consider a query that joins two tables containing both duplicate and unique data. This data distribution allows for multiple possible execution plans. Without intervention, the optimizer may choose different plans over time depending on statistics, bind values, or adaptive behavior.
Using the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE procedure, we can manually capture a known-good execution plan from the cursor cache and store it as a SQL plan baseline. Once created, the baseline is enabled by default but not fixed, meaning Oracle may still attempt to capture and evaluate additional plans.
If plan stability is critical, the baseline can be fixed using the DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure. Fixing a plan tells the optimizer to stop considering alternative execution paths for that statement.
You can confirm baseline usage by querying views such as DBA_SQL_PLAN_BASELINES, or by checking the NOTES section of an explain plan using DBMS_XPLAN. These tools allow you to see when a baseline was last used, how it was verified, and whether it is currently active.
Automation and Evolution
Manual baselines are only part of the SPM story. Oracle also supports automatic plan capture and plan evolution. While automatic capture has existed since 11g, it was often avoided due to the overhead of capturing thousands of statements.
Later releases introduced automated evolution tasks, allowing the optimizer to test alternative plans and accept them only if they perform better. In Oracle 19c, this evolved further with high-frequency SPM evolution, reducing the time required to correct regressions.
Oracle 23ai takes this one step further with Real-Time SPM. Rather than testing plans in a background job, the optimizer now evaluates plan changes during SQL execution itself. If a plan regresses, Oracle can immediately test alternatives, create a new baseline, and disable the old one, all within the same session.
This makes SPM a proactive, real-time performance protection mechanism rather than a reactive tuning tool.
Why This Matters During Upgrades
Database upgrades often introduce optimizer changes that affect a small but critical subset of SQL statements. SQL Plan Management provides a safety net, allowing DBAs to preserve performance while still benefiting from new optimizer features.
With Real-Time SPM in Oracle 23ai and beyond, Oracle has transformed SPM into a core performance capability. One that actively protects applications from plan regressions as data, workloads, and systems evolve.
Want the Full Deep Dive?
This post highlights just a portion of what SQL Plan Management can do. For a complete walkthrough, including hands-on examples, automation strategies, and real-world upgrade scenarios, check out Upgrading Oracle Databases, authored by our Oracle ACE Directors, Sean Scott, Gary Gordhamer, and Charles Kim.
Happy Holidays! 🎄
Ready to Take Your Oracle Skills to the Next Level?
Join OraPub, Viscosity’s training hub for Oracle professionals, packed with expert-led courses and exclusive paid member benefits.
Check out Viscosity’s event page for upcoming virtual and on-site training opportunities.
SUBMIT YOUR COMMENT