Minimal Oracle SQL Plan Baselines for Critical OLTP Queries: Design, Trust, and Operational Checks

The Problem: Silent Plan Regressions After Routine Changes

After optimizer statistics are refreshed, a patch is applied, or a PDB parameter changes, a critical OLTP query can switch to a slower plan, causing noticeable latency. The optimizer is not at fault; it simply re‑optimizes with new information. A minimal, supported mechanism to prevent such regressions is Oracle SQL Plan Management (SPM) with a baseline that pins the plan shape.

Requirements

• Identify the high‑frequency or high‑impact SQL_ID statements that must retain a known plan.
• Capture a reproducible plan while the system is healthy.
• Enable the baseline feature at the database level.
• Load the plan from the cursor cache or a SQL tuning set; do not auto‑capture all statements.
• Allow evolution only after verification that a better plan truly improves performance.

Smallest Suitable Design

Enable the feature and load a single baseline for the target SQL_ID. Use FIXED = 'NO' initially so the baseline can be evolved.

-- DBA role or ADMINISTER SQL MANAGEMENT OBJECT privilege required
ALTER SYSTEM SET optimizer_use_sql_plan_baselines = TRUE SCOPE = BOTH;

DECLARE
  l_plans_loaded PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id            => '9tu5xg3z8v7k1',   -- replace with target SQL_ID
    plan_hash_value   => 1234567890,       -- optional, specific plan hash
    fixed             => 'NO',            -- allow evolution
    enabled           => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans_loaded);
END;
/

Permissions: the caller needs the ADMINISTER SQL MANAGEMENT OBJECT privilege (granted to DBA by default). Run the load when the target plan is resident in the shared pool, preferably during a maintenance window.

Trust and Data Boundaries

• Storage: Baselines are stored in the SQL Management Base inside the SYSAUX tablespace; they are database objects, not files.
• Authority: Acceptance, enablement, and evolution are controlled by the database engine; DBA privileges are required to modify them.
• Scope: A baseline is bound to the normalized SQL text and the database version. After a major upgrade, baselines may become non‑reproducible and are ignored unless re‑loaded.

Operational Checks

Verify that the baseline exists, is accepted, and is actually used:

-- 1. Confirm baseline metadata
SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed,
       origin,
       last_executed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%SELECT /*+ BUSINESS_CRITICAL */%'
ORDER  BY last_executed DESC;

-- 2. Compare baseline plan vs. executing plan
-- Baseline plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
  sql_handle => 'SYS_SQL_9tu5xg3z8v7k1',
  plan_name  => 'SQL_PLAN_9tu5xg3z8v7k1_abc123',
  format     => 'BASIC +OUTLINE'));

-- Executing cursor (run while the statement is active)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
  sql_id => '9tu5xg3z8v7k1',
  format => 'BASIC +OUTLINE'));

Expected: the plan shape, access paths, and join order should match. A mismatch indicates the baseline is not being used.

Evolution: Controlled Acceptance of New Plans

When the optimizer proposes a plan not in the baseline set, it marks it UNACCEPTED. Use the evolve task to evaluate it:

DECLARE
  l_report CLOB;
BEGIN
  l_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SYS_SQL_9tu5xg3z8v7k1',
    plan_name  => 'SQL_PLAN_9tu5xg3z8v7k1_new456',
    time_limit => 300,
    verify     => 'YES'
  );
  DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

The report shows elapsed time, buffer gets, and whether the new plan is accepted. Review before promoting it.

Failure Modes

Failure Mode	Symptom	Root Cause
Baseline rejected at parse time	Optimizer ignores baseline; REPRODUCED = 'NO'	Referenced objects changed (index dropped, partition added) or statistics make the plan non‑reproducible.
Silent plan switch after upgrade	Performance drops post‑upgrade; baseline missing for the SQL_ID	Baselines are version‑specific and not automatically migrated across releases.
Evolution accepts a worse plan	New baseline marked ACCEPTED=YES but latency rises	Test workload during evolve did not reflect production data skew or concurrency.
SMB space pressure	SYSAUX growth alerts; high space usage in DBA_SQL_MANAGEMENT_CONFIG	Automatic capture or excessive fixed baselines consume space.

Conditions That Change the Design

• Heavy ad‑hoc workloads with literals: Each distinct literal creates a separate SQL_ID. Baselines become unmanageable; consider CURSOR_SHARING = FORCE or SQL Profiles with forced matching.
• Frequent partition pruning: Plans that rely on dynamic partition elimination may not reproduce reliably after statistics refreshes. Re‑validate baseline reproducibility after each stats gather.
• Adaptive optimization (adaptive plans, statistics feedback): Fixed baselines disable these features. Keep baselines FIXED = 'NO' if runtime adaptation offers measurable benefit.
• Multitenant (CDB/PDB): Baselines are scoped to a PDB. A baseline loaded in one PDB does not affect the same query in another PDB; repeat the process per tenant.

Limitations and Practical Verification

Baselines do not guarantee stability across:

• Major Oracle version upgrades
• DDL that invalidates dependent objects
• Changes to optimizer environment parameters such as OPTIMIZER_FEATURES_ENABLE or OPTIMIZER_MODE

Verification checklist after any change:

1. Query DBA_SQL_PLAN_BASELINES for the target SQL_HANDLE—confirm ENABLED=YES and ACCEPTED=YES.
2. Run the statement and capture its SQL_ID from V$SQL.
3. Compare the PLAN_HASH_VALUE in V$SQL with the baseline’s PLAN_HASH_VALUE in DBA_SQL_PLAN_BASELINES.
4. If they differ, check REPRODUCED and REPRODUCTION_STATUS for the reason.

Rollback Consideration

Loading or evolving a baseline changes only metadata; to revert a mistaken FIXED=YES or drop an unwanted baseline:

EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
  sql_handle => 'SYS_SQL_9tu5xg3z8v7k1',
  plan_name  => 'SQL_PLAN_9tu5xg3z8v7k1_abc123',
  attribute_name => 'FIXED',
  attribute_value => 'NO');

-- Or drop a baseline entirely
EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE(
  sql_handle => 'SYS_SQL_9tu5xg3z8v7k1',
  plan_name  => 'SQL_PLAN_9tu5xg3z8v7k1_new456');

These operations are instantaneous and do not move data.