Oracle SQL Plan Management: Fast Yesterday, Slow Today

When a report that ran in seconds starts taking twenty minutes and nobody deployed anything, the SQL text is usually unchanged and the execution plan is not. Oracle SQL Plan Management is the feature that stops that happening again: the optimizer may still find new plans, but it is only allowed to run plans you have accepted. Oracle's own words for it are blunt - "SQL plan management prevents performance regressions caused by plan changes."

That sentence is the whole reason the feature exists. A plan flip does not announce itself; it arrives as angry phone calls about a screen that used to work.

This article is the working version of what I do about it: why plans move, what a baseline really does at parse time, how to pin a good plan while people are shouting, and the four or five situations where reaching for SPM is the wrong answer.

1. Why Was This Query Fast Yesterday and Slow Today?

Because the optimizer re-costed the statement and picked a different plan. The SQL text is the same, but something it depends on changed: new statistics, a new index or materialized view, a different bind value peeked at the hard parse, or a new optimizer version after an upgrade.

Oracle names the same triggers. The SQL Tuning Guide says "ongoing system and data changes can affect plans for some SQL statements, potentially causing performance regressions", and that "creating a materialized view or an index or repartitioning a table may make current plans more expensive than other plans."

Bind peeking: one plan, many different values

This is the one that catches people who have already ruled out statistics. In Oracle's words, "in bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement."

It does not look again on every parse. "The optimizer does not look at the bind variable values before every parse. Rather, the optimizer peeks only when the optimizer is first invoked, which is during the hard parse."

So the plan is shaped by whichever value happened to arrive first after the cursor aged out. Oracle states the consequence directly: "because the optimizer only peeks at the bind value during the hard parse, the plan may not be optimal for all possible bind values."

Adaptive cursor sharing exists to soften that, by letting "a single statement that contains bind variables use multiple execution plans". It marks a cursor bind-sensitive and watches it, which is useful - and is also why the same statement can quietly own two plans and alternate between them.

An upgrade is the scheduled version of the same event

Oracle is realistic about this: "a database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements." Most of those changes are neutral or good.

The documentation continues: "most plan changes result in either improvement or no performance change. However, some plan changes may cause performance regressions." A small percentage of a large application is still a long Saturday night.

That is the whole argument for SPM in one line. You cannot predict which statements will flip, so you carry the known-good plans across with you. My notes on an Oracle 19c to 26ai upgrade cover where this sits in the wider plan.

2. What Exactly Is a SQL Plan Baseline?

A SQL plan baseline is a set of accepted execution plans that the optimizer is allowed to use for one SQL statement. Oracle stores it in the SQL management base inside SYSAUX, keyed by a SQL signature, and each plan is held as a complete set of outline hints that reproduces that plan.

Oracle defines SPM itself as "a preventative mechanism that enables the optimizer to automatically manage execution plans, ensuring that the database uses only known or verified plans." The baseline is the list of what counts as known.

It has three moving parts, and most confusion comes from mixing them up:

The key that ties a plan to a statement is not the SQL text you typed. Oracle: "a SQL signature is a numeric hash value computed using a SQL statement text that has been normalized for case insensitivity and white space."

Normalized for case and whitespace - not for literals. Change WHERE status = 'A' to WHERE status = 'B' and you are looking at a different statement as far as the baseline is concerned.

Storage lives in one place. "The SQL management base is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles."

And since 12c the history is wider than the baseline: "Starting in Oracle Database 12c, the SMB stores the execution plans for all SQL statements in the SQL plan history." The history is every plan Oracle has seen; the baseline is the accepted subset.

3. Baseline, SQL Profile or Stored Outline - Which Is Which?

These three get used as synonyms in handover documents, and they are not the same thing at all.

Oracle's own summary is the sentence worth memorising: "in general, SQL plan baselines are proactive, whereas SQL profiles are reactive." A baseline is a seatbelt you fit before the crash; a profile is treatment afterwards.

There is a trade-off in that, and Oracle says it out loud. "Because a profile does not constrain the optimizer to any one plan, a SQL profile is more flexible than a SQL plan baseline. For example, changes in initialization parameters and optimizer statistics enable the optimizer to choose a better plan."

Read that twice before you pin anything. A baseline buys stability by giving up the optimizer's freedom to improve.

If you are still carrying stored outlines from a 10g-era application, Oracle's position is to move: migrating them means you "maintain the same plan stability that you had using stored outlines while being able to use the more advanced features provided by SQL Plan Management, such as plan evolution."

And when you genuinely do not know which mechanism a statement needs, Oracle's advice is to stop guessing: "Oracle recommends that you use SQL Tuning Advisor. In this way, you follow the recommendations made by the advisor for SQL profiles and plan baselines rather than trying to determine which mechanism is best for each SQL statement."

4. How Does the Optimizer Actually Use a Baseline at Parse Time?

This is the part worth memorising, because it explains almost every "my baseline is being ignored" ticket. The optimizer does not skip its own work when a baseline exists.

Oracle's sequence: "when the database performs a hard parse of a SQL statement, the optimizer generates a best-cost plan. By default, the optimizer then attempts to find a matching plan in the SQL plan baseline for the statement. If no plan baseline exists, then the database runs the statement with the best-cost plan."

If a baseline does exist and the new plan is in it, that plan runs. If the new plan is not in the baseline, the optimizer "marks the newly generated plan as unaccepted and adds it to the plan history", then falls through three rules:

• "If fixed plans exist in the plan baseline, then the optimizer uses the fixed plan with the lowest cost."
• "If no fixed plans exist in the plan baseline, then the optimizer uses the baseline plan with the lowest cost."
• "If no reproducible plans exist in the plan baseline, which could happen if every plan in the baseline referred to a dropped index, then the optimizer uses the newly generated cost-based plan."

That last rule is the one that surprises people. A baseline is not a lock on the database; it is a preference list that quietly stands aside when none of its plans can be rebuilt.

Two other practical consequences follow from the same text. The new plan is kept, not discarded, which is what makes evolution possible later. And nothing happens at all on a soft parse, because the whole dance is tied to the hard parse.

You can see the outcome without guessing. When a baseline is used, the plan output carries a note naming it:

-- Oracle's documented example of the note under an execution plan
Note
-----
   - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement

That is Oracle's sample output, not a measurement from a live system - the plan name on your database will differ. The presence of the Note block is the point.

5. How Do I Capture Baselines in the First Place?

Two routes: let the database do it while statements run, or load plans you have already decided are good.

Automatic capture

One parameter switches it on, and it is off until you do.

-- Needs ALTER SYSTEM. Check first, then change.
SHOW PARAMETER SQL_PLAN

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;

Oracle: "you can enable automatic initial plan capture by setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true (the default is false)." The companion parameter is separate - "the settings of the preceding parameters are independent of each other."

A statement has to earn a baseline. "The first check for eligibility is repeated execution. If a statement is executed less than twice, then the database does not consider it eligible for a SQL plan baseline."

Three statement types are excluded outright: CREATE TABLE without AS SELECT, DROP TABLE, and INSERT INTO ... VALUES. There is also a trap for anyone testing with EXPLAIN PLAN alone - "SQL plan management does not protect statements that have been explained using EXPLAIN PLAN but have not been executed."

Now the warning I wish more people read before flipping the switch. Oracle's caution says enabling automatic capture "may result in the creation of an extremely large number of plan baselines", because the database captures recursive and monitoring SQL as well as your application's.

So filter it. DBMS_SPM.CONFIGURE accepts filters on SQL text, parsing schema name, module and action:

-- Capture only the SH schema, and never the statements tagged TEST_ONLY.
-- Needs ADMINISTER SQL MANAGEMENT OBJECT (or EXECUTE on DBMS_SPM).
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_PARSING_SCHEMA_NAME', 'sh', true);
EXEC DBMS_SPM.CONFIGURE('AUTO_CAPTURE_SQL_TEXT', '%TEST_ONLY%', false);

-- What is configured right now
SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

Two rules about those filters. Oracle says "the settings are additive", and that "you cannot configure multiple values for the same parameter in the same procedure" - call it once per value. The filters only apply while OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is TRUE.

One more thing that trips up audits. Once a statement has a baseline, new plans keep arriving regardless of that parameter: "for any SQL statement that has an existing SQL plan baseline, the database automatically adds new plans to the SQL plan baseline as unaccepted plans", and the capture parameter "does not control the automatic addition of newly discovered plans to a previously created SQL plan baseline."

Manual loading

Manual capture is a bulk load, and it has a property automatic capture does not: "manually loaded plans are always marked accepted" - which is exactly why it works in an outage. If you want them loaded but idle, Oracle notes you can "load plans without enabling them by setting the enabled parameter to NO in the DBMS_SPM.LOAD_PLANS_FROM_% functions."

Oracle documents five sources - AWR, a SQL tuning set, the shared SQL area, a staging table, and a stored outline. The three that carry the real work:

-- 1. From the cursor cache: the plan is still in memory.
VARIABLE cnt NUMBER
EXEC :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '27m0sdw9snw59');

-- 2. From AWR: the plan has aged out of memory but is in a snapshot range.
--    begin_snap and end_snap are required; there is no plan_hash_value parameter,
--    and enabled defaults to YES - so load idle (see section 7, step 4) and look first.
EXEC :cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR( -
  begin_snap => 212, -
  end_snap   => 213, -
  enabled    => 'NO');

-- 3. From a SQL tuning set: the upgrade pattern.
EXEC :cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'PREUPGRADE_STS');

Each of those returns the number of plans loaded, and each needs the ADMINISTER SQL MANAGEMENT OBJECT privilege. Oracle's own AWR example also assumes the caller can read the AWR views and has been granted the privilege.

A word on the AWR call's basic_filter, because Oracle describes it in two places. The SQL Tuning Guide calls it "any WHERE clause predicate that can be specified against the column DBA_HIST_SQLTEXT.SQL_TEXT", while the PL/SQL packages reference calls it a filter "defined on attributes of the SQLSET_ROW", a row type whose attributes include sql_id and plan_hash_value.

This article does not rely on that filter to pick exactly one plan during an incident. Section 7 loads from AWR with enabled => 'NO', looks at every plan that arrived, and then enables only the one it meant.

The cursor-cache route has a use beyond emergencies: Oracle says "loading plans directly from the shared SQL area is useful when application SQL has been hand-tuned using hints." You run the hinted version, then load its plan against the original statement's text so the untouched application gets the tuned plan.

For upgrades, the tuning-set route is the documented one: "bulk loading execution plans from an STS is an effective way to prevent plan regressions after a database upgrade." Build the STS on the old release, carry it over, load it on the new one.

Moving baselines between databases uses a staging table and Data Pump - CREATE_STGTAB_BASELINE, PACK_STGTAB_BASELINE, export, import, UNPACK_STGTAB_BASELINE. Oracle's own unpack example passes fixed => 'yes', which is worth noticing: a vendor-shipped baseline arriving fixed will not evolve on your site.

6. What Do ENABLED, ACCEPTED and FIXED Really Mean?

They are three independent flags on each plan. ENABLED means the optimizer may consider it, ACCEPTED means it has been verified or loaded as trusted, and FIXED means it is preferred over every non-fixed plan and will not be evolved.

A plan is a real candidate only when it is enabled and accepted. Disabling is how you take a plan out of play without losing it.

Changing a flag is one call, and it returns how many plans it altered:

-- Disable one plan for one statement (reversible; the plan stays in the SMB).
VARIABLE n NUMBER
EXEC :n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
  sql_handle      => 'SQL_b6b0d1c71cd1807b', -
  plan_name       => 'SQL_PLAN_bdc6d3jsd3g3c1234abcd', -
  attribute_name  => 'enabled', -
  attribute_value => 'NO');

Leaving plan_name as NULL applies the change to every plan for that handle - useful, and easy to do by accident. The attributes you can set are enabled, fixed, autopurge, plan_name and description.

There is one side effect of FIXED that deserves a line in your runbook. Oracle: "if new plans are added to a baseline that contains at least one enabled fixed plan, then the optimizer cannot use the new plans until you manually declare them as fixed."

That is the straitjacket. Fix a plan in March, add a perfect index in July, and the index sits there unused until someone remembers.

7. How Do I Pin a Known-Good Plan While People Are Shouting?

This is a five-step version short enough to keep beside the console. It assumes you already know which statement is hurting, usually from AWR and ASH.

Step 1 - find the statement and both plans

-- Every plan the cursor cache currently holds for this SQL_ID.
SELECT sql_id, plan_hash_value, child_number,
       executions, buffer_gets,
       ROUND(elapsed_time/GREATEST(executions,1)/1e6, 2) AS sec_per_exec,
       sql_plan_baseline
FROM   v$sql
WHERE  sql_id = '&sql_id'
ORDER  BY child_number;

Two different PLAN_HASH_VALUEs for one SQL_ID is the plan flip in one line. The SQL_PLAN_BASELINE column tells you whether a baseline was already in force.

Before you go any further, save the good plan itself, not just its hash. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('27m0sdw9snw59', &good_child_no)); while it is still in memory: enter the CHILD_NUMBER of the good-plan row selected above when SQL*Plus or SQLcl prompts for &good_child_no. Use DBMS_XPLAN.DISPLAY_AWR once it is not.

That printout is the evidence you compare against in step 4, and it costs ten seconds now against a guess later.

Step 2 - confirm the good plan is still in memory

If it is not, skip to AWR. The plan has to be somewhere Oracle can read it; you cannot pin a memory of it.

Step 3 - load it, fixed

-- Load ONLY the good plan_hash_value, as a fixed plan.
VARIABLE cnt NUMBER
EXEC :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( -
  sql_id          => '27m0sdw9snw59', -
  plan_hash_value => 1234567890, -
  fixed           => 'YES');
PRINT cnt

Naming plan_hash_value matters. Leave it NULL and Oracle's default is "capture all plans present in the cursor cache for the SQL statement identified by SQL_ID" - including the bad one, accepted.

Step 4 - if the good plan has aged out, load from AWR disabled, then promote one plan

This is the step that bites people, so write it as five small calls rather than one big one. The cursor-cache route in step 3 lets you name a single plan_hash_value; LOAD_PLANS_FROM_AWR has no such parameter. Oracle's description of leaving its basic_filter null is blunt: "NULL means all plans in AWR are selected."

On top of that, enabled defaults to 'YES' - "the loaded plans will be considered by the optimizer".

Put those two together and the one-call version does something you would never do deliberately in the middle of an incident: it makes every plan AWR kept for that statement in that snapshot range an accepted, enabled candidate - the plan you are running away from included - before you have looked at a single one of them. Manual loads are always marked accepted, so there is no second gate behind it. Load them idle, read them, then promote the one plan you meant.

-- 4a. Write down what the baseline already holds, BEFORE you load anything.
--     Anything ENABLED here is still a candidate afterwards; anything FIXED
--     outranks every plan you are about to add (see the note under 4e).
SELECT plan_name, origin, enabled, accepted, fixed, created
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SQL_b6b0d1c71cd1807b'
ORDER  BY created;

-- 4b. Pick a snapshot range from before the regression.
SELECT snap_id, begin_interval_time
FROM   dba_hist_snapshot
ORDER  BY snap_id DESC FETCH FIRST 20 ROWS ONLY;

-- 4c. Load the candidates IDLE: not enabled, and not changing anything's fixed flag.
VARIABLE cnt NUMBER
EXEC :cnt := DBMS_SPM.LOAD_PLANS_FROM_AWR( -
  begin_snap   => 212, -
  end_snap     => 213, -
  basic_filter => q'[sql_id = '27m0sdw9snw59']', -
  enabled      => 'NO', -
  fixed        => 'NO');
PRINT cnt

-- 4d. See what landed, then READ each new plan before trusting any of it.
SELECT plan_name, origin, enabled, accepted, fixed, created
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SQL_b6b0d1c71cd1807b'
ORDER  BY created;

SELECT t.*
FROM   TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
               'SQL_b6b0d1c71cd1807b', 'SQL_PLAN_bdc6d3jsd3g3c1234abcd')) t;

-- 4e. Only now, and only for the ONE plan_name you identified in 4d.
VARIABLE n NUMBER
EXEC :n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
  sql_handle      => 'SQL_b6b0d1c71cd1807b', -
  plan_name       => 'SQL_PLAN_bdc6d3jsd3g3c1234abcd', -
  attribute_name  => 'enabled', -
  attribute_value => 'YES');
PRINT n

EXEC :n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
  sql_handle      => 'SQL_b6b0d1c71cd1807b', -
  plan_name       => 'SQL_PLAN_bdc6d3jsd3g3c1234abcd', -
  attribute_name  => 'fixed', -
  attribute_value => 'YES');
PRINT n

How to recognise the right plan in 4d. DBA_SQL_PLAN_BASELINES does not carry a plan-hash column, so you cannot look up the good PLAN_HASH_VALUE from step 1 and match on it.

Compare the shape instead: run DISPLAY_SQL_PLAN_BASELINE once per new plan_name and put it beside the good plan you kept from step 1 - SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('27m0sdw9snw59', 1234567890)); prints that one from AWR. The operations, their order, the access paths and the join methods either match or they do not. If two loaded plans both match, leave the extra one disabled; you can always enable it later.

Account for what was already there. Step 4a is not paperwork.

A plan that was enabled and accepted before you started is still a candidate after the load, and a plan that was already fixed is worse than that - Oracle: "if new plans are added to a baseline that contains at least one enabled fixed plan, then the optimizer cannot use the new plans until you manually declare them as fixed." So if 4a showed an enabled fixed plan and that plan is the problem, disable it explicitly with ALTER_SQL_PLAN_BASELINE and attribute_name => 'enabled', attribute_value => 'NO', naming it. Promoting your new plan while the old one stays fixed changes nothing.

Never leave plan_name null in 4e. A null plan_name applies the change to every plan under that handle, which in this situation means enabling and fixing exactly the set of plans you spent 4d filtering down. Each call returns the number of plans it altered: PRINT n should say 1, and if it says anything else, stop and read 4d again.

One detail about fixed => 'NO' in 4c: it is the default, and it does not un-fix anything. Oracle defines it as meaning "the loaded plans will not change the current 'fixed' property of the SQL plan baseline into which they are loaded". It is written out in the call because this procedure is read at 3 a.m., and a reader should not have to remember a default.

AWR also only holds what its retention kept, which is the argument for a sensible retention window long before you need it.

Step 5 - prove the plan is actually being used, then write down the date

Nothing in step 3 or step 4 is proof. A row in DBA_SQL_PLAN_BASELINES says a plan exists and is allowed; it does not say the optimizer picked it. Three checks, in this order:

-- 5a. The baseline rows say what you intended: one enabled plan, reproduced.
SELECT plan_name, origin, enabled, accepted, fixed, reproduced
FROM   dba_sql_plan_baselines
WHERE  sql_handle = 'SQL_b6b0d1c71cd1807b'
ORDER  BY created;

-- 5b. Run the statement, then read the plan the optimizer really used.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('27m0sdw9snw59', &actual_child_no));

-- 5c. And the cursor itself names the baseline.
SELECT sql_id, child_number, plan_hash_value, sql_plan_baseline
FROM   v$sql
WHERE  sql_id = '27m0sdw9snw59';

For 5b, select the child that actually executed from V$SQL on the same instance, then enter its CHILD_NUMBER at the SQL*Plus/SQLcl &actual_child_no prompt. Omitting that argument defaults to child 0; it does not reliably select the child you just ran. The default format includes the Note section naming any baseline used; inspect that same child and compare its plan with the saved good plan.

5c is the same answer from the other side: SQL_PLAN_BASELINE should hold your plan_name, and PLAN_HASH_VALUE should be the good plan's from step 1.

REPRODUCED = NO in 5a is the first thing to check when the note is missing; Oracle defines it as whether "the optimizer was able to reproduce the plan". No note and no baseline name in V$SQL means the pin did not take, whatever the baseline table says - and proof over belief is the whole method.

8. How Do Plans Evolve, and Should Oracle Do It for Me?

Evolution is how an unaccepted plan becomes accepted. Oracle test-executes the candidate, compares it with the current baseline plan, and promotes it only if it performs better - which is what keeps a baseline from freezing you out of genuine improvements.

The reason it exists is in the documentation: "if new plans were never added to SQL plan baselines, then the performance of some SQL statements might degrade." A baseline with one plan from 2024 is not stability, it is a slow leak.

You can run it by hand as a four-call task:

-- Create, run, read, then implement. Nothing is accepted until you say so.
VARIABLE tname VARCHAR2(30)
VARIABLE rep   CLOB
VARIABLE n     NUMBER

EXEC :tname := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_b6b0d1c71cd1807b');
EXEC DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => :tname);
EXEC :rep := DBMS_SPM.REPORT_EVOLVE_TASK(task_name => :tname, type => 'TEXT');
PRINT rep
EXEC :n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => :tname);

Leaving sql_handle as NULL is legal: the default "considers all SQL statements with non-accepted plans". On a busy database that is a big job, so give it a handle unless you mean it.

The report is the interesting part. Oracle's sample verdict reads: "It passed the benefit criterion because its verified performance was 2.01 times better than that of the baseline plan."

That 2.01 is Oracle's example output. Your report will carry its own number, and the useful habit is to read the reason line, not just the FIND/ACCEPT summary.

The automatic tasks

Oracle's recommendation is unambiguous: "Oracle recommends that you configure SPM Evolve Advisor to run automatically."

There are two automatic routes, and they are not the same task.

One detail catches people who try to disable just the evolve half: "no separate scheduler client exists for the Automatic SPM Evolve Advisor task. One client controls both Automatic SQL Tuning Advisor and Automatic SPM Evolve Advisor."

And if you want to change the daily task's parameters, you need the right login. Oracle: "because the SYS_AUTO_SPM_EVOLVE_TASK task is owned by SYS, only SYS can set task parameters."

-- Read the automatic task's current parameters (no change made).
SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SPM_EVOLVE_TASK'
AND    parameter_name IN ('ACCEPT_PLANS','TIME_LIMIT',
                          'ALTERNATE_PLAN_LIMIT','ALTERNATE_PLAN_SOURCE');

Worth knowing before an audit question: for the automated task Oracle documents accept_plans as true by default, which means the database can promote plans without anyone pressing a button. That is usually what you want, and it should still be a conscious decision.

9. How Do I See What SPM Is Actually Doing?

One view answers most questions. DBA_SQL_PLAN_BASELINES is where an SPM investigation starts.

-- The working status query.
SELECT sql_handle, plan_name, origin,
       enabled, accepted, fixed, reproduced, adaptive,
       optimizer_cost, created, last_executed
FROM   dba_sql_plan_baselines
ORDER  BY created DESC FETCH FIRST 50 ROWS ONLY;

The ORIGIN column tells you how each plan arrived - AUTO-CAPTURE, MANUAL-LOAD-FROM-CURSOR-CACHE, MANUAL-LOAD-FROM-AWR, MANUAL-LOAD-FROM-STS, AUTO-SQLTUNE, STORED-OUTLINE and several more. On an inherited database that one column usually explains the whole history.

Two columns need a caveat before you use them in a report. LAST_EXECUTED is not live - "for performance reasons, this column is not updated immediately after each execution of the plan baseline", so a plan may have run more recently than it says.

And the workload columns are blank for anything auto-captured. Oracle's footnote: "if the value of the ORIGIN column is equal to AUTO-CAPTURE, then data for this column is not populated."

To read the plan itself rather than its metadata, use DBMS_XPLAN:

SELECT t.*
FROM   dba_sql_plan_baselines b,
       TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle, b.plan_name, 'basic')) t
WHERE  b.sql_handle = 'SQL_b6b0d1c71cd1807b';

And to check what is happening live, V$SQL.SQL_PLAN_BASELINE names the baseline a running cursor is using. If that column is null for a statement you pinned, your pin is not doing what you think.

10. How Do I Drop Baselines and Keep SYSAUX Under Control?

Dropping is one function, and it is sharper than it looks.

-- Drop ONE plan.
VARIABLE n NUMBER
EXEC :n := DBMS_SPM.DROP_SQL_PLAN_BASELINE( -
  sql_handle => 'SQL_b6b0d1c71cd1807b', -
  plan_name  => 'SQL_PLAN_bdc6d3jsd3g3c1234abcd');
PRINT n

Omit plan_name and you drop everything for that statement: the default "means to drop all plans associated with the SQL statement identified by sql_handle". The function returns the number of plans dropped, so PRINT n is your receipt.

If you only want a plan out of circulation, disable it instead. A drop is not reversible; a disable is.

The space budget

The SMB shares SYSAUX with everything else that lives there, so Oracle gives it a budget. The default is 10% of SYSAUX, settable between 1 and 50.

Budget is the right word, not ceiling. A weekly background process measures what the SMB occupies, and when the limit is exceeded it writes a warning to the alert log and goes on warning every week until the limit is raised, SYSAUX is grown, or objects are purged. Nobody stops the growth for you.

Unused plans are cleaned up on a timer: the retention period defaults to 53 weeks - "1 year plus 1 week", so an annually executed query survives - and can be set between 5 and 523 weeks. Purging is driven by the LAST_EXECUTED timestamp stored in the SMB.

-- Current settings
SELECT parameter_name, parameter_value FROM dba_sql_management_config;

-- Change them (ADMINISTER SQL MANAGEMENT OBJECT; both are Oracle's documented examples)
EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent', 30);
EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks', 105);

You will be told when it overflows, though not loudly. "A weekly background process measures the total space occupied by the SMB. When the defined limit is exceeded, the process writes a warning to the alert log."

Weekly, in the alert log. If nothing reads your alert log, nothing will tell you.

One more dependency worth knowing before a SYSAUX maintenance window: "because the SMB is located entirely within the SYSAUX tablespace, the database does not use SQL plan management and SQL tuning features when this tablespace is unavailable."

11. When Is SPM the Wrong Tool?

SPM reproduces plans. It does not create better ones, and most bad SPM deployments are built on forgetting that.

Oracle puts the hard limit in one sentence: "SQL plan baselines cannot help when an event has caused irreversible execution plan changes, such as dropping an index."

Here is what to reach for instead:

There is also a timing trap with literals. Because the signature is normalized only for case and whitespace, an application that builds SQL with literals instead of bind variables will generate a different signature for every value. Oracle's first eligibility check is repeated execution - a statement executed fewer than twice never gets a baseline - so a value seen once is safe.

The trouble is the values that come round again. Every one of those is a separate repeatable statement, and capture will fill the SMB with a baseline per literal.

That is not an SPM problem. That is a tuning and application design problem wearing an SPM costume, and no amount of plan pinning will cure it.

The rule worth keeping: use SPM to hold a win, never to manufacture one. Pin in the outage, fix the cause in daylight, then unfix the plan and let evolution do its job.

Frequently Asked Questions

Does turning on SQL plan management slow the database down?

Using baselines is on by default: OPTIMIZER_USE_SQL_PLAN_BASELINES is true out of the box, and most databases run that way without anyone noticing. The setting worth thinking about is automatic capture, OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, which is false by default. Oracle warns that enabling it may result in the creation of an extremely large number of plan baselines, because the database captures recursive and monitoring SQL too, so use DBMS_SPM.CONFIGURE filters if you turn it on.

What is the difference between a SQL plan baseline and a SQL profile?

A SQL plan baseline is a set of accepted plans the optimizer is allowed to use, and each plan is stored as hints that fully specify that plan. A SQL profile is a set of auxiliary statistics for the statement; Oracle says the profile does not itself determine a specific plan, it corrects the optimizer's estimates. Oracle describes baselines as proactive and profiles as reactive, and notes that a profile is more flexible because it does not constrain the optimizer to one plan.

Why is my SQL plan baseline being ignored?

Check three things in DBA_SQL_PLAN_BASELINES: ENABLED, ACCEPTED and REPRODUCED. A plan is only a candidate when it is both enabled and accepted, and Oracle's selection rules say that if no reproducible plans exist in the baseline, which could happen if every plan in the baseline referred to a dropped index, the optimizer uses the newly generated cost-based plan instead. Also confirm OPTIMIZER_USE_SQL_PLAN_BASELINES is still true, and remember that plans sit against a normalized SQL signature: Oracle normalizes it for case and white space, so reformatting the statement does not move it, but a changed literal gives you a different signature and therefore a different statement.

How do I pin one specific plan and stop Oracle changing it?

Load that plan from the cursor cache with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE, naming both the sql_id and the plan_hash_value you want, and pass fixed => 'YES'. Oracle's definition of the fixed attribute is that the SQL plan baseline is not evolved over time and that a fixed plan takes precedence over a non-fixed plan. Treat it as a tourniquet: it also means new plans added later cannot be used until you declare them fixed yourself.

Do SQL plan baselines survive an Oracle upgrade?

That is one of the uses Oracle documents for them. The SQL Tuning Guide says a database upgrade that installs a new optimizer version usually results in plan changes for a small percentage of SQL statements, and that baselines significantly minimize potential regressions from an upgrade. The documented pattern is to capture the good plans on the old release, for example into a SQL tuning set, and load them on the new one; Oracle calls bulk loading from an STS an effective way to prevent plan regressions after a database upgrade.

How much SYSAUX space does the SQL management base use?

Its budget is 10 percent of SYSAUX by default, and you can set that between 1 and 50 percent with DBMS_SPM.CONFIGURE('space_budget_percent', n). It is a budget rather than a hard limit: Oracle says a weekly background process measures the total space the SMB occupies and, when the defined limit is exceeded, writes a warning to the alert log and goes on generating alerts weekly until the limit is increased, SYSAUX is increased, or SQL management objects are purged. Unused plans are purged after the retention period, which defaults to 53 weeks and can be set between 5 and 523 weeks.

Can SQL plan management fix a query that was never fast?

No. SPM reproduces plans you already have; it cannot invent a better one, and Oracle states plainly that SQL plan baselines cannot help when an event has caused irreversible execution plan changes, such as dropping an index.

If the only plans in the history are bad ones, the problem is upstream in statistics, indexing or the data model. Fix that first, then use SPM to hold on to the good plan once it exists.