SQL Plan Baselines and Plan Stability

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Advanced
SQL Plan Baselines and Plan Stability
Advanced Topic 6 of 7

The Problem: A Good Query Suddenly Gets Slow

Yesterday a report ran in 2 seconds. Today it takes 20 minutes. Nothing in the SQL changed, but the optimizer chose a new plan after statistics were gathered or the database was upgraded. This is called a plan regression.

Simple example: A plan baseline is a GPS 'favorite route'. The optimizer may find a new route, but it only uses it if it proves to be at least as fast as your saved route.

Find the Plans a Query Has Used

sql SELECT sql_id, plan_hash_value, executions, ROUND(elapsed_time/1e6/NULLIF(executions,0),3) AS sec_per_exec FROM v$sql WHERE sql_id = '&sql_id';

If you see two different plan_hash_value numbers for the same query, with very different times, you have a regression and you know which plan is the good one.

Create a Baseline from the Good Plan

sql DECLARE l_plans PLS_INTEGER; BEGIN l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '&sql_id', plan_hash_value => &good_plan_hash); DBMS_OUTPUT.PUT_LINE(l_plans || ' plan(s) loaded'); END; /

Check the Baselines

sql SELECT sql_handle, plan_name, enabled, accepted, fixed, origin FROM dba_sql_plan_baselines ORDER BY created DESC;
ColumnMeaning
enabledMay be used
acceptedVerified and allowed to be used
fixedOptimizer prefers this plan above others
originHow it was created (manual load, auto capture ...)

Make Sure Baselines Are Used

sql SHOW PARAMETER optimizer_use_sql_plan_baselines -- should be TRUE

How the Optimizer Behaves

  1. Optimizer finds the best plan as usual.
  2. If it matches an accepted baseline plan, it runs it.
  3. If it finds a new plan, it records it as not accepted.
  4. The new plan is used only after it is verified (evolved) and found to be better.

Evolve New Plans

sql SELECT DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => '&sql_handle') FROM dual;

In recent versions an automatic SPM Evolve Advisor task also tests new plans in the background and accepts the ones that are clearly better.

Remove a Baseline

sql DECLARE l_n PLS_INTEGER; BEGIN l_n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '&sql_handle'); END; /
When to use SPM: Use baselines for your most critical queries, and before upgrades. Do not create thousands of them. Each baseline has to be managed.

Conclusion

SQL Plan Management protects good plans. Capture the good plan, let the optimizer try new ones and accept them only when they prove better.

Quick Summary: Load the good plan with DBMS_SPM, check DBA_SQL_PLAN_BASELINES, evolve new plans after testing.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators