A query that ran in fifty milliseconds for a year is suddenly taking thirty seconds. Nobody changed the code. Nobody changed the query. And that's exactly the problem: the optimizer is allowed to change its mind. New statistics, a bind variable it peeked at differently, an upgrade, a dropped index — any of them can hand the same statement a new plan, and the new plan can be worse. Most of the time you never notice. The time you notice, it's a pager at 2 a.m.
The two posts before this one were about making the optimizer's estimate right — reading the plan to find where the estimate went wrong, and fixing the statistics behind it. This one is about the plan you've already got right and want to keep. Because "get the estimate right" is a moving target: statistics get re-gathered, data shifts, and a plan that was correct on Tuesday is not guaranteed on Wednesday. SQL Plan Management is the seatbelt. It lets the optimizer keep improving plans everywhere else while refusing to let a known-good plan silently regress on the statements you can't afford to have go wrong.
What a SQL plan baseline actually is
A SQL plan baseline is a stored set of accepted execution plans for one SQL statement. It lives in the SQL Management Base in SYSAUX, and it's keyed by the statement's signature — a hash of the normalized SQL text (case- and whitespace-insensitive, but literal-sensitive, which is one more reason to use bind variables). When a statement with a baseline parses, the optimizer does something subtly different from its usual job:
- It builds its best-cost plan the way it always does, from the current statistics.
- Then it checks the baseline. If that best-cost plan is already accepted, it just uses it — nothing to protect against.
- If the best-cost plan is not accepted, the optimizer sets it aside as a non-accepted plan (kept for later, in case it's genuinely better) and instead runs the best accepted plan that still reproduces against today's schema.
- Only if no accepted plan can be reproduced — say the index it needs was dropped — does it fall back to the best-cost plan.
The effect is a ratchet. The optimizer is free to find new plans, but it can't use one you haven't blessed. A plan you captured while things were good stays in force even when the cost model, fed newer statistics, starts preferring something else.
What the optimizer does when an enabled baseline exists. It still computes its best-cost plan — it just isn't allowed to run it unless you've accepted it. A newly found plan is parked as non-accepted for later verification, not used on the spot.
The three flags that decide everything
Every plan in a baseline carries three independent states, and mixing them up is the single most common source of "SPM isn't working":
-
ENABLED— the plan is eligible for consideration at all. Disable it and the optimizer ignores it. -
ACCEPTED— the plan is allowed to run. This is the one that matters. A captured plan can be enabled but not accepted (it's a candidate, waiting to prove itself); the optimizer will not use it until it's accepted. -
FIXED— the plan is preferred. When a baseline has any fixed plans, the optimizer chooses only among those and stops adding new candidates. A fixed baseline is how you say "this, and nothing else, until I say otherwise."
The mental model: ENABLED is "in the pool," ACCEPTED is "cleared to fly," FIXED is "and it's the captain." Most of SPM is moving plans between those states deliberately instead of letting it happen to you.
Capturing the plan you want to keep
There are three ways plans get into a baseline. The one you'll reach for most is loading a plan you already have in the cursor cache — you found the good plan, now you pin it:
-- pin the plan currently in the cursor cache for one SQL_ID
DECLARE
n NUMBER;
BEGIN
n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'a1b2c3d4e5f6g');
DBMS_OUTPUT.PUT_LINE(n || ' plan(s) loaded');
END;
/
Plans loaded this way arrive enabled and accepted — ready to enforce immediately. The other two sources:
-
Automatic capture. Set
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUEand Oracle records a baseline for every repeatable statement it sees. The first plan for a statement is auto-accepted (it becomes the reference); any different plan found later is captured as non-accepted, waiting for you to verify it. Powerful, but it's a blanket — turn it on around a risky change (an upgrade, a big data load), capture what you need, then turn it back off rather than leaving it on forever. -
From a SQL tuning set / AWR.
DBMS_SPM.LOAD_PLANS_FROM_SQLSETpulls plans out of a tuning set, which is how you seed baselines from AWR history — including from the old release before an upgrade, so the plans that worked last quarter are on file before the new optimizer ever runs.
That last one is the killer use case: capture baselines from the current release, upgrade, and the optimizer inherits a floor. New plans still get found and parked as candidates, but nothing regresses on day one because every statement already has its old, proven plan accepted.
Confirm it's actually in force
Two checks. First, does the baseline exist and what state is it in:
SELECT sql_handle, plan_name, enabled, accepted, fixed, origin
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%shop.orders%';
Then the check that matters — that the plan the optimizer actually built came from the baseline. The plan's Note section says so outright:
SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));
-- ...
-- Note
-- -----
-- - SQL plan baseline SYS_SQL_PLAN_1a2b3c4d used for this statement
If that line is there, the baseline held. If it isn't — and you expected it to — the usual reasons are OPTIMIZER_USE_SQL_PLAN_BASELINES turned off, the plan isn't accepted (only enabled), or the accepted plan can no longer be reproduced because the schema changed underneath it.
Evolving: how a baseline gets better without getting worse
A baseline that never changes eventually holds a plan that's genuinely stale — a new index really would be faster now. That's what evolution is for. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE takes the non-accepted candidates piling up in the baseline, runs them, and accepts one only if it actually performs better than the current accepted plan by a real margin. This is the crucial difference from just letting the optimizer loose: a plan is promoted on measured performance, not on estimated cost.
-- verify the parked candidates and accept only the ones that prove faster
SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => 'SQL_1a2b3c4d5e6f7g8h') FROM dual;
From 19c on, the automatic SPM evolve advisor task does exactly this in the maintenance window: it evaluates the candidates that accumulated during the day and auto-accepts the ones that verify faster. So the healthy steady state is: baselines protect you from regressions, and evolution quietly promotes real improvements after they've proven themselves. You get stability and progress, instead of choosing.
Don't take my word for it — run it. The SQL Plan Management lab builds a million-row
ORDERStable with a rare, indexedstatusand a histogram, soWHERE status = 'OPEN'gets the cheap index plan. It captures that plan as an accepted baseline, then drops the histogram to simulate the everyday stats drift that breaks plans — now the optimizer estimates a third of the table and costs a full scan as cheaper. The lab runs the same query twice and asserts both outcomes: with baselines off, the plan regresses to a full table scan; with baselines on, the accepted index plan is held (it's still reproducible because the index exists) andDBMS_XPLANreports SQL plan baseline ... used for this statement. If the regression doesn't happen, or the baseline doesn't stop it, the run fails. The whole before/after is proven on every CI push.
What teams get wrong
-
Confusing enabled with accepted. A captured candidate plan is enabled but not accepted, and the optimizer won't run it. People load a plan, see it in
DBA_SQL_PLAN_BASELINES, and assume it's in force — then wonder why nothing changed. Check theACCEPTEDcolumn, not just that a row exists. -
Fixing a baseline and forgetting. A
FIXEDbaseline stops accepting new candidates entirely. That's correct for a statement you never want touched — and a slow poison for one whose data grew tenfold since, because the plan can no longer improve even when it should. Fixed plans need a review date. -
Leaving automatic capture on forever. Blanket capture around an upgrade is smart; leaving it on fills the SQL Management Base with baselines for one-off and ad-hoc statements and quietly grows
SYSAUX. Turn it on with intent, capture, turn it off. -
Baselining statements full of literals. Signatures are literal-sensitive. A statement that inlines
WHERE id = 48213has a different baseline fromWHERE id = 48214. Without bind variables you're baselining a million near-identical statements, none of which recurs. Bind first, baseline second. - Treating a baseline as a fix for bad statistics. SPM freezes a plan; it doesn't make the estimate correct. If you pin a plan to paper over stale stats, you've hidden the problem, not solved it — and every other statement on that table still gets the bad estimate. Fix the number, then baseline the good plan it produces.
- Capturing on the new release, after the regression. The highest-value moment to capture is before an upgrade, from the release that still works. Seed baselines from AWR on the old version and the new optimizer inherits a floor instead of a surprise.
Frequently asked questions
What is SQL Plan Management in Oracle?
SQL Plan Management (SPM) is an Oracle feature that prevents execution plans from regressing by controlling which plans the optimizer is allowed to use for a statement. It stores accepted plans as SQL plan baselines in the SQL Management Base. When a statement that has a baseline is parsed, the optimizer still computes its best-cost plan, but it will only execute a plan that has been accepted into the baseline; a newly found plan is recorded as a non-accepted candidate for later verification rather than used immediately. This lets the optimizer keep finding better plans while guaranteeing that a known-good plan cannot be silently replaced by a worse one.
What is the difference between enabled, accepted, and fixed plans in a baseline?
Enabled means the plan is eligible for the optimizer to consider at all. Accepted means the plan is allowed to actually run — this is the state that determines whether a plan is used, and a plan can be enabled but not accepted, in which case it is only a candidate. Fixed means the plan is preferred: when a baseline contains any fixed plans, the optimizer chooses only among the fixed plans and stops adding new candidates to the baseline. A plan loaded from the cursor cache arrives enabled and accepted; a plan captured automatically as an alternative arrives enabled but not accepted until it is evolved or manually accepted.
How do I capture a SQL plan baseline?
There are three main ways. First, load a plan already in the cursor cache with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE for a specific SQL_ID — this loads the plan enabled and accepted, ready to enforce. Second, enable automatic capture by setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE, which records a baseline for every repeatable statement (the first plan is auto-accepted and later alternatives are captured as non-accepted). Third, load plans from a SQL tuning set with DBMS_SPM.LOAD_PLANS_FROM_SQLSET, which is how you seed baselines from AWR history, including from an older release before an upgrade.
How do I know if a SQL plan baseline is being used?
Run the statement and pull its plan with DBMS_XPLAN.DISPLAY_CURSOR using a format such as ALLSTATS LAST or TYPICAL. If a baseline built the plan, the Note section at the bottom reads "SQL plan baseline used for this statement." You can also query V$SQL.SQL_PLAN_BASELINE for the cursor, which holds the baseline plan name when one was applied. If you expected a baseline and neither appears, check that OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE, that the plan is accepted and not merely enabled, and that the accepted plan is still reproducible against the current schema.
What does it mean to evolve a SQL plan baseline?
Evolving a baseline means testing the non-accepted candidate plans that have accumulated for a statement and accepting one only if it actually performs better than the current accepted plan. DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE runs the candidates and promotes them based on measured performance, not estimated cost, which is what makes evolution safe. From Oracle 19c on, the automatic SPM evolve advisor task performs this verification in the maintenance window and auto-accepts candidates that prove faster, so baselines protect against regressions while still allowing genuinely better plans to be adopted after they have been verified.
Does a SQL plan baseline fix bad statistics?
No. A baseline freezes which plan a statement uses; it does not correct the optimizer cardinality estimate. If you pin a plan to work around stale or missing statistics, you have hidden that one symptom while every other statement against the same table still gets the wrong estimate. The correct order is to fix the statistics first — a histogram for a skewed column, extended statistics for correlated columns, a fresh gather where the data moved — and then capture a baseline of the good plan that results, so it is protected going forward. SPM is a stability tool, not a statistics tool.
Should I use SQL plan baselines before an Oracle upgrade?
Yes — an upgrade is one of the strongest use cases. A new optimizer version can change plans for statements that were previously fine, and some of those changes are regressions. If you capture baselines from the current release before upgrading — for example by loading plans from AWR history into a SQL tuning set and then into baselines — the upgraded optimizer inherits an accepted plan for each of those statements and cannot silently regress them on day one. New and potentially better plans are still found and parked as candidates, so you can evolve them deliberately after the upgrade rather than discovering regressions in production.
What is the difference between a SQL plan baseline and a SQL profile?
A SQL plan baseline stores one or more complete, accepted execution plans and constrains the optimizer to use an accepted plan, which makes it a plan-stability mechanism. A SQL profile does not store a plan; it stores corrective information — essentially adjustment factors for the optimizer estimates — that helps the optimizer build a better plan, but it does not lock a specific plan in place. In practice you use a SQL profile to help the optimizer estimate correctly and a baseline to guarantee a specific proven plan is used. They can coexist: a profile improves the estimate while a baseline fixes the resulting plan.
SQL Plan Management is the last stop in the same performance discipline as the rest: an AWR report points you at the expensive SQL, wait events tell you what a session is stuck on, the execution plan shows where the estimate went wrong, and statistics are usually why. Once you've done that work and the plan is right, a baseline is how you keep it right — the optimizer stays free to improve everything else, and the plan you fought for doesn't quietly unravel the next time the numbers move. Prove it end to end with the SQL Plan Management lab: watch a plan regress to a full scan when it's unprotected, and watch the baseline hold the line when it isn't.
Originally published at uptimearchitect.com.
