In the world of database administration, the most terrifying phrase you can hear is "Plan Regression." This happens when a query that has run perfectly for years suddenly becomes slow because the Optimizer decided to pick a new, "better" execution plan that turned out to be a disaster.

SQL Plan Baselines (part of the SQL Plan Management or SPM feature) are Oracle’s way of preventing these sudden performance heart attacks. It is essentially a "Verified Guest List" for execution plans.

1. The Core Concept: Trust but Verify

Without Baselines, the Optimizer is free to change its mind whenever statistics are updated or a parameter changes. With Baselines, Oracle follows a two-step rule:

1. Selection: The Optimizer identifies all possible plans for a query.

2. Validation: It checks the SQL Plan Baseline. If the new plan isn't in the baseline, Oracle refuses to use it—even if it thinks it's faster—and sticks with a known, "Accepted" plan instead.

2. How the Baseline Lifecycle Works

A Plan Baseline isn't just a static lock; it's an evolving history of a query's performance.

• Capture: Oracle records the execution plans for your SQL statements. If a query runs more than once, its plan is added to the "Plan History."

• Selection: The first plan captured is marked as Accepted.

• Evolution: If the Optimizer finds a new plan (perhaps because you added an index), it adds it to the history but marks it as Non-Accepted.

• Verification: You (or an automated task) can "evolve" the baseline. Oracle will test the new plan against the old one. If the new plan is truly faster, it gets promoted to Accepted status.

3. Why Use Baselines?

• Stability: It guarantees that your mission-critical queries won't suddenly slow down on a Monday morning.

• Safe Upgrades: When you move from Oracle 19c to 23c, you can carry your Baselines with you to ensure your queries behave exactly the same way in the new version.

• Controlled Tuning: You can "force" a specific plan onto a query without changing the application code (no SQL hints required!).

4. Key Status Flags

When you query DBA_SQL_PLAN_BASELINES, you'll see three important flags:

5. How to Enable It

You can turn on automatic capture for your entire database (use with caution, as this can use a lot of SYSAUX space!):

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;

Or, you can manually load a "Good" plan from the cursor cache if you’ve found a query that is currently performing well:

DECLARE
  l_plans_loaded  PLS_INTEGER;
BEGIN
  l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'your_sql_id_here');
END;

6. Baselines vs. Profiles

People often confuse SQL Plan Baselines with SQL Profiles.

• SQL Profiles provide the Optimizer with "Corrective Statistics" (better info) but don't guarantee a specific plan.

• SQL Plan Baselines provide "Plan Stability" (specific instructions) and are much more rigid about what the Optimizer is allowed to do.

Peer Tip: Think of a Baseline as an Insurance Policy. You pay a little bit in overhead (storing the plans) to ensure you never have to deal with the "uninsured" catastrophe of a major production query failing during peak hours.