As Oracle professionals, we’ve all faced that moment of frustration — a SQL statement that ran flawlessly for months suddenly slows down after a patch, a data load, or an environment change. Performance dips, users start complaining, and your peaceful day as a DBA turns into a firefight.
That’s where Automatic SQL Plan Management (SPM) comes to the rescue.
Automatic SQL Plan Management is one of Oracle Database’s most intelligent self-healing features — designed to prevent performance regressions and keep your SQL execution plans stable without manual tuning. In this post, we’ll explore how it works, what problems it solves, and why it’s a must-have for maintaining performance consistency in modern Oracle environments.
What Is Automatic SQL Plan Management?
Automatic SQL Plan Management (SPM) is Oracle’s mechanism to prevent SQL performance regressions automatically, without user intervention. Think of it as a guardian angel for your SQL execution plans — watching over them, detecting when something changes, and correcting it before it becomes a problem.
When SQL statements are executed, Oracle uses the optimizer to choose the best possible execution plan. But sometimes, due to data growth, statistics refreshes, or environment changes, the optimizer might pick a new plan that performs worse than before. Automatic SQL Plan Management ensures that doesn’t happen.
Here’s what it does automatically:
- ✅ Resolves plan regressions without manual tuning
- 🔍 Searches for SQL statements in the Automatic Workload Repository (AWR)
- 🧩 Looks for alternative plans in all available sources and adds better-performing ones to the SQL Plan Baseline
In short, it ensures that only stable and high-performing execution plans are used — even when conditions change.
Meet the SPM Evolve Advisor
At the heart of automatic SQL plan evolution is the SPM Evolve Advisor.
This intelligent SQL advisor continuously evolves and improves your SQL plan baselines — without you lifting a finger.
The SPM Evolve Advisor:
- Identifies plans that have recently been added to the baseline
- Tests and compares them against existing plans
- Automatically accepts better-performing plans
- Simplifies the evolution process by removing the need for manual review
In other words, the SPM Evolve Advisor learns, tests, and adapts — just like an autonomous tuning assistant.
How the SPM Evolve Advisor Works (Step-by-Step)
You can configure automatic plan evolution using the DBMS_SPM package.
Whenever it runs in the maintenance window, it performs a series of smart actions:
Step 1: Checks AWR for top SQL
Oracle’s AWR stores the most resource-intensive SQL statements.
SPM Evolve Advisor identifies statements that are likely to benefit from SQL plan baselines and adds them automatically.
Step 2: Looks for alternative plans
It searches across all available repositories for better-performing plans not yet in the SQL Management Base (SMB) history.
This includes plans captured from AWR, manual captures, or tuning sets.
Step 3: Adds unaccepted plans to the plan history
If a potential new plan hasn’t been baseline-approved, it’s added to the plan history for testing.
Step 4: Tests plan performance
During the maintenance window, Oracle tests as many plans as possible.
Using a cost-based algorithm, it compares the new plan’s performance metrics with the existing baseline plan.
Step 5: Accepts or rejects the plan
If the new plan performs better, SPM Evolve Advisor accepts it and adds it to the baseline.
If not, the plan stays in the statement history but doesn’t impact performance.
Over time, this continuous loop keeps your SQL environment optimized — automatically.
Problems Automatic SQL Plan Management Solves
Let’s be real — SQL performance issues don’t happen in a vacuum. They often emerge from small changes that ripple across the database ecosystem. Automatic SPM addresses exactly that.
Here’s what it solves:
- Performance consistency: Keeps SQL statements stable for months or even years.
- Reduced outages: Prevents sudden performance dips caused by plan changes.
- Improved service levels: Maintains predictable response times for mission-critical workloads.
- Automatic plan repair: Recovers quickly from sub-optimal plans, improving application reliability.
Without SPM, even a minor change in the optimizer environment can cause massive headaches. With SPM, Oracle quietly takes care of it behind the scenes.
When to Use Automatic SQL Plan Management
SPM becomes particularly valuable in environments where:
- There’s a change in the database environment (e.g., upgrade or patching)
- Data growth or distribution causes plan shifts
- A critical SQL plan impacts application performance
Imagine this scenario:
A complex report query has been running perfectly for years. One day, after gathering fresh statistics, the optimizer suddenly decides a new plan is better — but it’s not. The query slows down dramatically, affecting your entire application.
With SPM enabled:
- The query executes using the new plan.
- Oracle immediately compares it to the previous baseline plan.
- If the old plan performs better, it’s automatically restored — ensuring continuous performance stability.
No manual tuning, no downtime, no panic.
How to Enable and Use It
Once enabled, no further manual intervention is required.
You can turn on automatic SPM evolution in just a few lines:
EXEC dbms_spm.configure('AUTO_SPM_EVOLVE_TASK', 'AUTO');
You can also monitor and report plan evolution using:
DBA_SQL_PLAN_BASELINES— view all plan baselinesDBA_SQL_PLAN_BASELINES.ORIGIN— shows how the plan was captured (e.g., FOREGROUND-CAPTURE)
These views give DBAs full visibility while Oracle automates the heavy lifting.
Benefits of Automatic SQL Plan Management
Let’s summarize why DBAs and Oracle professionals love this feature:
- Supports Autonomous Database principles
- Automatic SPM aligns with Oracle’s vision of a self-managing, self-optimizing database.
- Reduces manual intervention
- You no longer need to manually evolve or test SQL plans.
- Ensures transparent plan repair
- Oracle automatically detects and replaces poor-performing plans without disrupting workloads.
- Improves application performance reliability
- Keeps performance consistent across database upgrades, patches, and data changes.
- Saves time for DBAs
- Less firefighting and more focus on strategic database improvements.
In essence, SPM transforms SQL plan management from a manual, reactive task into a proactive, autonomous process.
Final Thoughts
In today’s data-driven world, performance consistency is critical.
Oracle’s Automatic SQL Plan Management and SPM Evolve Advisor ensure that your SQL statements always perform optimally — automatically adapting to changes without human intervention.
For DBAs and performance engineers, this feature is not just about automation; it’s about peace of mind.
With SPM enabled, you can rest assured that Oracle is constantly learning, evolving, and improving SQL plans — so your applications stay fast, stable, and reliable.
If you haven’t enabled Automatic SPM yet, now’s the perfect time to do it — and experience the future of self-tuning database performance.
