SQL Plan Baselines: Minimal Architecture and Trust Boundaries for Oracle 12c+

Execution-Plan Regressions and the Smallest Suitable Fix

Routine statistics gathering, index changes, or optimizer upgrades can flip an execution plan and turn a fast query into a latency outlier. SQL Plan Management (SPM) stores accepted execution plans in the database repository so the optimizer can reproduce a known-good plan for a specific SQL statement. The smallest suitable engineering decision is to load baselines only for a small set of business-critical SQL IDs, mark them fixed, and leave global automatic capture disabled.

Requirements and Assumptions

SPM is available in Oracle Database 12c and later, though default behaviors and feature depth have evolved across releases. This note assumes a 12c or 19c single-instance or RAC environment using cost-based optimization.

You need:

• A DBA-enabled account to load, evolve, and inspect baselines.
• The SQL ID of each critical statement, obtainable from V$SQLAREA, V$SQL, or an AWR report.
• Confirmation that the statement is repeatable and that its current plan is acceptable. Baselines preserve plans; they do not improve them.

Smallest Suitable Design

Do not enable OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES globally. On a busy OLTP system, automatic capture floods the repository with non-reproducible or stale plans and increases parse overhead.

Instead:

1. Identify a short list of business-critical SQL IDs whose plans you want to stabilize.
2. Load their current plans from the cursor cache using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE.
3. Mark those baselines as FIXED so the optimizer prefers them over newly computed plans.
4. Leave OPTIMIZER_USE_SQL_PLAN_BASELINES at its default TRUE and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES at its default FALSE.

A SQL tuning set (STS) is an alternative source when the statement is not currently in the cursor cache, but for most operational fixes the cursor cache is sufficient.

Trust and Data Boundaries

SQL Plan Baselines are not a security boundary. They are optimizer metadata stored in the SYSAUX tablespace. Any user who can execute the underlying SQL statement can benefit from the baseline; baseline use is not restricted by row-level security, Virtual Private Database (VPD), or privilege checks beyond normal SQL execution rights.

Managing baselines requires DBA privileges. Visibility into DBA_SQL_PLAN_BASELINES is also DBA-gated. Protect the repository by limiting who can execute DBMS_SPM routines and who can query the baseline dictionary views. Baselines do not encrypt SQL text or bind values; sensitive literals in pinned statements remain visible to authorized DBAs.

Operational Checks

After loading a baseline, verify it is active and reproduced.

Run as a DBA to confirm the baseline exists and is enabled:

SELECT sql_handle, plan_name, enabled, accepted, fixed, reproduced
FROM dba_sql_plan_baselines
WHERE sql_id = '&critical_sql_id';

Expected result: one or more rows with ENABLED = 'YES', ACCEPTED = 'YES', and FIXED matching your intent. If REPRODUCED = 'NO', the optimizer cannot currently match the stored plan to the SQL text or environment.

Execute the target statement, then inspect the cursor:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => '&critical_sql_id',
  format  => 'BASIC'
));

Look for a note such as "SQL Plan Baseline used for this statement". If the note is absent, the baseline was not reproduced and the optimizer fell back to normal cost-based optimization.

Re-run these checks after statistics gathering, patching, upgrades, or DDL. If REPRODUCED becomes NO or the baseline note disappears, reload or evolve the baseline.

Loading a Baseline

Run as a DBA in SQL*Plus or SQLcl. Replace &critical_sql_id with the target SQL ID.

SET SERVEROUTPUT ON
DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id          => '&critical_sql_id',
    plan_hash_value => NULL,
    fixed           => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

Risk: loading a plan that is already suboptimal pins that plan. Confirm the current plan is acceptable before loading.

Failure Modes

Non-reproduced plans after environment changes. Baseline matching is sensitive to exact SQL text unless force matching is used, to bind values through cursor sharing, and to optimizer environment settings. Index renames, partitioning changes, or optimizer parameter changes can break reproduction. A non-reproduced accepted plan is a candidate for reloading or removal.

Fixed baselines blocking improvement. A FIXED baseline prevents the optimizer from choosing a better plan after an index is added or statistics improve. Review fixed flags quarterly and after major schema changes.

Repository pollution. Enabling global capture on a busy system creates thousands of baselines, many for one-off statements. This increases SYSAUX consumption and parse overhead without stabilizing anything.

Adaptive plan interference. In 12c and later, adaptive plans and automatic indexing can interact poorly with fixed baselines. If the database adopts automatic indexing, a fixed baseline may suppress a superior auto-created index plan.

Conditions That Change the Design

Re-evaluate the baseline strategy when:

• Ad-hoc literal SQL dominates. If the workload uses mostly literal SQL rather than bind variables, exact-text matching creates an unmanageable number of baselines. Consider force matching or a different stabilization approach.
• Frequent schema evolution. If DDL regularly invalidates objects, baselines age quickly. Automate reloading after change windows or shift to SQL Patches for targeted fixes.
• Automatic indexing is enabled. Automatic indexing can conflict with fixed baselines. Decide whether to let automatic indexing propose plans or to pin existing ones.
• Targeted plan fixes are needed. For a small number of problematic statements, a SQL Patch or SQL Plan Quarantine (in later releases) may be simpler than a full baseline.

Removing a Baseline

Loading a baseline changes database state. If a baseline is no longer needed, disable it first, monitor for regressions, then drop it.

Run as a DBA. Replace &sql_handle with the handle from DBA_SQL_PLAN_BASELINES.

DECLARE
  l_dropped PLS_INTEGER;
BEGIN
  l_dropped := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    sql_handle => '&sql_handle'
  );
END;
/

Before dropping, confirm the statement performs acceptably without the baseline by checking DBMS_XPLAN.DISPLAY_CURSOR after disabling it.

Limitations and Final Verification

Baselines stabilize plans; they do not fix cardinality misestimates, bad schema design, or missing indexes. They also do not survive certain operations that invalidate cursors or optimizer environments unless reloaded. Plan evolution is a manual or gated process: automatic evolution can accept regressions if not reviewed.

A practical final check is to run the critical statement under representative load and confirm the plan hash value matches the baseline's plan hash value. If it does not, the baseline is not active for that execution.