Understanding Oracle SQL Plan Baselines for Performance Stability

 Understanding Oracle SQL Plan Baselines for Performance Stability

Introduction

As Oracle DBAs, we often encounter unexpected SQL performance regressions after statistics collection, database upgrades, or plan changes. One of the lesser-used but powerful tools to combat such issues is the SQL Plan Baseline feature. This blog post aims to demystify SQL Plan Baselines—what they are, why they matter, and how to implement them in real-world scenarios.

What is a SQL Plan Baseline?

A SQL Plan Baseline is a set of accepted execution plans stored in the data dictionary that Oracle uses to ensure consistent query performance. If a SQL statement's newly generated plan differs from the baseline and isn't verified yet, Oracle will prefer the known "accepted" plan—thus avoiding sudden regressions.

Why Use SQL Plan Baselines?

• Prevents performance regressions after statistics gathering or DB upgrades.

• Maintains execution plan consistency for critical SQL statements.

• Allows safe testing of new plans before full adoption.

How SQL Plan Baselines Work

1. A SQL statement is executed.

2. If no baseline exists, Oracle uses the best cost-based plan and stores it as a "candidate".

3. A DBA can manually verify and accept the candidate plan.

4. Once accepted, Oracle uses the plan for future executions unless a better one is verified and promoted.

   Step-by-Step: How to Create a SQL Plan Baseline

   1. Capture the Plan Manually

   -- Enable plan baseline capture at session level
   ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;
   
   -- Run your SQL
   SELECT /*+ gather_plan_statistics */ * FROM employees WHERE department_id = 10;
    

   2. Load Plans into Baseline from Cursor Cache 

   DECLARE
     l_plans_loaded PLS_INTEGER;
   BEGIN
     l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
       sql_id => 'abcd1234abcd1234'
     );
   END;
   /
   3. Verify Baseline Exists

   SELECT sql_handle, plan_name, enabled, accepted
   FROM dba_sql_plan_baselines
   WHERE sql_text LIKE '%employees%';
    

   Advanced Usage

Evolving Plans: You can allow Oracle to test better plans automatically using plan evolution.

Transporting Baselines: Use DBMS_SPM.CREATE_STGTAB_BASELINE to export/import across databases.

Disabling Bad Plans: If a baseline plan is no longer good, you can disable it without removing it.

Best Practices

• Regularly monitor baselines for outdated or rejected plans.

• Avoid accepting plans without testing in QA.

• Combine with SQL Plan Management (SPM) policies in critical production environments.

  Conclusion

  SQL Plan Baselines are a powerful tool for ensuring SQL performance stability across changes. They are often overlooked but can make a significant difference in environments where query performance is business-critical. Start with critical queries and evolve your baseline strategy to cover more ground as your database evolves.