Using Oracle SQL Plan Baselines to Prevent Execution-Plan Regressions

Why use SQL Plan Baselines

SQL Plan Management (SPM) lets you lock in a known‑good execution plan for a SQL statement. After you accept a baseline, the optimizer will only use plans that match an accepted baseline unless none are reproducible, in which case it falls back to a cost‑based plan. This prevents surprising regressions after statistics gathering, index changes, or a database upgrade.

Worked configuration: capture, accept, and evolve a baseline

1. Enable automatic capture (requires ALTER SYSTEM privilege). Run this once at the instance level:

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE SCOPE=BOTH;

1. Run the target statement twice so the optimizer generates a plan and SPM captures it. Replace :sql_text with the actual statement (or use a bind variable).

-- First execution (plan captured)
SELECT /*+ baseline_demo */ employee_id, salary
FROM   employees
WHERE  department_id = :dept_id;

-- Second execution (reinforces capture)
SELECT /*+ baseline_demo */ employee_id, salary
FROM   employees
WHERE  department_id = :dept_id;

1. Verify the capture in DBA_SQL_PLAN_BASELINES. You should see a row with ENABLED='YES' and ACCEPTED='NO' (captured but not yet accepted).

SELECT sql_handle, plan_name, enabled, accepted
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%baseline_demo%';

1. Evolve the baseline to test whether the captured plan is acceptable. This step compares the captured plan’s performance against the current best plan and only accepts it if it is not worse.

DECLARE
  l_report  CLOB;
BEGIN
  l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
                sql_handle => 'SQL_abcdef1234567890',
                plan_name  => NULL,   -- evolve all captured plans for this handle
                time_limit => DBMS_SPM.AUTO_LIMIT,
                test_execute => TRUE);
  DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

1. Check the result. After a successful evolve, the row will show ACCEPTED='YES'. Subsequent executions will display the note "SQL plan baseline used" in the plan output.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'BASIC +NOTE'));

Limits and considerations

• Baseline bloat: Leaving OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE indefinitely populates the SQL Management Base in SYSAUX with thousands of unaccepted plans. Periodically purge or set a retention policy via DBMS_SPM.CONFIGURE.
• Non‑reproducible plans: If an object referenced by an accepted baseline (e.g., an index) is dropped or becomes invalid, the baseline is marked REPRODUCABLE='NO'. The optimizer may then choose a new plan, which could be a regression. Monitor DBA_SQL_PLAN_BASELINES after DDL.
• Licensing: SPM is included with Oracle Enterprise Edition at no extra cost. Features like SQL Profiles or the SQL Tuning Advisor require the Tuning Pack.
• Interaction with adaptive features: Adaptive cursor sharing and adaptive plans can cause multiple accepted plans for a single statement (one per bind‑sensitivity pattern). This is expected but increases the baseline count.
• Transportability: Baselines can be moved between databases using DBMS_SPM staging tables, useful for upgrade validation from 12c to 19c or later.

Common mistakes to avoid

• Enabling capture globally and never evolving or purging, leading to SYSAUX exhaustion.
• Assuming a baseline guarantees plan usage; remember the optimizer can fallback when all accepted plans are non‑reproducible.
• Forgetting to re‑run the evolve step after statistics changes; an accepted baseline may become sub‑optimal.
• Using baseline evolution without a proper workload test; the evolve step only compares performance, not correctness.

Practical verification steps

1. Query DBA_SQL_PLAN_BASELINES to confirm ENABLED=YES and ACCEPTED=YES for your statement.
2. Force a plan change (e.g., gather stats with a different method_opt or drop/add an index) and run DBMS_XPLAN.DISPLAY_CURSOR; the note should read "SQL plan baseline used" if the baseline is still reproducible.
3. Run DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE on a test statement and inspect the returned evolve report for accepted vs. rejected plans.
4. Check SYSAUX usage: SELECT occupant_name, space_used_kbytes FROM v$sysaux_occupants WHERE occupant_name = 'SQL_MANAGEMENT_BASE'; Ensure usage stays within expected limits.