Oracle SQL Plan Management: Stabilize Query Performance After Schema Changes

Why Execution Plan Stability Matters

When you add an index, change a column type, or upgrade to a newer Oracle release, the optimizer may pick a different execution plan for an existing query. Even a minor plan shift can double response time or spike CPU usage. SQL Plan Management (SPM) is Oracle’s built‑in tool to lock in a known‑good plan so that the optimizer will not deviate unless a new plan is proven superior.

Getting Started – Is SPM Enabled?

From Oracle 12c onward, SPM is compiled into the database but not automatically enabled. To activate it, run:

ALTER SYSTEM SET SPM_ENABLED = TRUE SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

After restart, the optimizer will start recording execution plans as they are generated. No changes to application code are required.

Capturing a Baseline – The Core Workflow

1. Identify the Target Query

Suppose you have a reporting query that runs nightly:

SELECT * FROM sales
WHERE sale_date > SYSDATE - 30;

Run it a few times to generate a plan in the cursor cache.

2. Load the Plan into a Baseline

Use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to capture the plan:

DECLARE
  l_plans_loaded NUMBER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => 'INSERT_SQL_ID_HERE',
    plan_hash_value => NULL,
    num_rows        => 10,
    use_estimated_rows => FALSE);
  DBMS_OUTPUT.PUT_LINE('Loaded ' || l_plans_loaded || ' plans.');
END;

Replace INSERT_SQL_ID_HERE with the actual sql_id from V$SQL. The procedure returns the number of plans loaded.

3. Accept the Baseline

After confirming the plan is acceptable (see Verification), mark it as accepted:

BEGIN
  DBMS_SPM.CREATE_SQL_PLAN_BASELINE(
    sql_id          => 'INSERT_SQL_ID_HERE',
    plan_hash_value  => NULL,
    baseline_name    => 'SALES_30DAY',
    baseline_type    => 'MANUAL',
    status           => 'ENABLED',
    accept_plan      => 'YES');
END;

The baseline is now stored in DBA_SQL_PLAN_BASELINES and will be used for future executions.

Enforcing the Baseline After a Schema Change

Imagine you add a new index on sale_date. The optimizer might now choose a hash join instead of a range scan. Because a baseline exists, the optimizer will ignore the new plan unless the baseline is disabled or a new plan is accepted.

Verify Baseline Usage

Run the query with AUTOTRACE or DBMS_XPLAN.DISPLAY_CURSOR to confirm the baseline is in force:

SET AUTOTRACE ON;
SELECT * FROM sales WHERE sale_date > SYSDATE - 30;

Check the plan hash value in the output. It should match the baseline’s plan_hash_value from DBA_SQL_PLAN_BASELINES.

Monitoring Baseline Health

Periodically query the baseline table:

SELECT baseline_name, sql_id, plan_hash_value,
       accepted, enabled, status
FROM dba_sql_plan_baselines
WHERE sql_id = 'INSERT_SQL_ID_HERE';

Look for ENABLED = YES and ACCEPTED = YES. If you see ENABLED = NO, the baseline has been disabled (perhaps by a manual change).

Managing Baselines – Evolution and Cleanup

When you’re confident a new plan is better, you can evolve the baseline automatically:

BEGIN
  DBMS_SPM.AUTO_EVALUATE_SQL(
    sql_id          => 'INSERT_SQL_ID_HERE',
    plan_hash_value => NULL,
    accept_plan     => 'YES');
END;

This runs the current plan, compares performance, and accepts it if it meets thresholds.

Stale baselines can accumulate. Use:

BEGIN
  DBMS_SPM.CLEANUP_SQL_PLAN_BASELINES(
    cleanup_type   => 'ALL',
    retention_days => 30);
END;

to purge baselines older than 30 days.

Trade‑Offs and Limitations

• Dictionary Overhead: Each baseline adds rows to DBA_SQL_PLAN_BASELINES. A large catalog can increase dictionary size and affect query performance.
• Suboptimal Baselines: If you accept a plan that is actually slower, the optimizer will keep using it until you evolve or disable the baseline.
• Ad‑hoc SQL: SPM only influences statements that have a baseline. Dynamically generated SQL without a baseline will still be optimized normally.
• Maintenance Burden: You must monitor, evolve, or clean up baselines manually or via scripts.

Actionable Takeaways

1. Enable SPM in the database and let the optimizer record plans.
2. Capture baselines for critical queries using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and CREATE_SQL_PLAN_BASELINE.
3. After schema changes or upgrades, verify the baseline is still in use with DBMS_XPLAN.DISPLAY_CURSOR.
4. Use AUTO_EVALUATE_SQL to let the system accept better plans automatically.
5. Schedule periodic cleanup to keep the baseline catalog lean.

By locking in proven execution plans, you protect your workloads from unpredictable regressions and give the optimizer a stable reference point. SPM is a powerful, low‑overhead tool that, when used responsibly, becomes a cornerstone of database performance engineering.