Database field guide · 13

SQL Plan Management

Baselines, evolution, and regression control

Use Oracle SQL Plan Management as a controlled acceptance process for execution plans, not a substitute for optimizer evidence.

Franck Pachot7 chaptersOracle · plan baselines · regression control

Build the mental model before choosing the mechanism.

This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.

A baseline is an allow-list

SQL Plan Management records accepted plan signatures for a SQL statement. The optimizer still costs alternatives, but only reproducible accepted plans are eligible when a baseline is active.

A baseline is attached to normalized SQL identity.
Accepted, enabled, and fixed are independent states.
A stored plan must still be reproducible in the current schema.

Capture from evidence

Plans can be loaded from the cursor cache, SQL tuning sets, or automatic capture. Capture should preserve the binds, environment, and performance evidence that made a plan worth keeping.

Do not baseline an unexplained transient success.
Keep origin and change-ticket metadata.
Confirm that the captured plan handles representative selectivity.

Evolve rather than freeze

New plans enter plan history without becoming accepted automatically. Evolution compares candidates and provides an explicit gate for improvement after statistics, index, or version changes.

Fixed plans take precedence over non-fixed plans.
Acceptance controls risk; it does not prove universal superiority.
Retire obsolete plans after a measured observation window.

Diagnose reproduction failure

A baseline can be enabled and accepted yet absent from the final plan because an index disappeared, a hint cannot be honored, or the statement no longer matches.

Read the DBMS_XPLAN note and outline data.
Check SQL handle, plan name, and origin.
Treat failed reproduction as a dependency problem.

Separate directives and baselines

SQL Plan Directives, cardinality feedback, profiles, patches, and baselines act at different layers. Directives improve estimates; baselines constrain plan choice; patches inject targeted hints.

Choose the mechanism that matches the failure.
Do not layer controls until causality is unknowable.
Document precedence and removal criteria.

Operate a regression workflow

A sound workflow detects a plan change, quantifies impact, installs a temporary guardrail, repairs the model or access path, and deliberately evolves the baseline.

Record plan hash and baseline identity with runtime data.
Test upgrades against a captured SQL workload.
Keep a reversible path for every plan control.

Field manual

Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.

List baseline state

Inspect all independent state flags before assuming a plan is enforced. FIXED=YES changes precedence; REPRODUCED=NO means the stored outline cannot currently build the plan.

SELECT sql_handle, plan_name, enabled, accepted, fixed, reproduced,
       origin, last_executed
FROM dba_sql_plan_baselines
ORDER BY last_modified DESC;

Capture a known cursor plan

Loading from cursor cache creates a baseline from an observed plan. Pin down SQL_ID and PLAN_HASH_VALUE so capture does not bless an unintended child cursor.

DECLARE n PLS_INTEGER; BEGIN
  n := dbms_spm.load_plans_from_cursor_cache(
    sql_id => :sql_id, plan_hash_value => :plan_hash_value, fixed => 'NO');
END;
/

Display stored hints

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE exposes the outline used to reproduce a baseline. Compare its note section with the actual cursor plan.

SELECT * FROM TABLE(dbms_xplan.display_sql_plan_baseline(
  sql_handle => :sql_handle, plan_name => :plan_name,
  format => 'BASIC +NOTE +OUTLINE'));

Evolve candidates explicitly

Evolution tests non-accepted plans and can accept verified candidates. Run it in a representative environment and retain the report as change evidence.

SET LONG 100000
SELECT dbms_spm.evolve_sql_plan_baseline(
  sql_handle => :sql_handle, verify => 'YES', commit => 'NO')
FROM dual;

Source articles

Optional deep dives with the complete experiments and product-version context behind this guide.