Project PerformanceShield – Restoring Optimal Execution Plan Using SQL Plan Management (SPM) from AWR

Project PerformanceShield – Restoring Optimal Execution Plan Using SQL Plan Management (SPM) from AWR

Author: Punit Kumar
Category: Oracle Performance Tuning | SQL Plan Management | AWR | SPM
Database Version: Oracle 19c

---

⚠️ Production Advisory
Before enforcing SQL Plan Baselines in production, validate the SQL in a lower environment (if possible) and confirm:

• Bind variable behavior / peeking sensitivity
• Statistics freshness (table/index/system stats)
• Adaptive plan behavior / SQL patches already applied
• That the historical plan truly matches the same SQL text and environment

---

📌 Problem Statement

A SQL query (SQL_ID: xxxxxxxxxxxxxx) started performing poorly due to a plan change.

📊 Performance Observation

Plan Type	Plan Hash Value	Avg Elapsed Time (sec)	Last Snapshot
Current Plan (Last Hour)	3405316313	78.261	2026-02-11 16:00:00
Best Plan (History)	1234567890	0.0	2026-02-05 15:00:43

Justification:
DEFAULT: We have identified a better plan in plan history.
A potentially better execution plan was found. Estimated benefit 100.0%.

---

🎯 Objective

Load the best historical plan from AWR into SQL Plan Management (SPM) and enable it as an accepted SQL Plan Baseline to restore stable performance.

---

🧠 Solution Overview

1. Create a SQL Tuning Set (STS)
2. Capture the desired plan from AWR into STS
3. Load the plan from STS into SPM (SQL Plan Baseline)
4. Enable the baseline
5. Drop STS (cleanup)

---

🛠️ Step-by-Step Implementation

Step 1 – Create SQL Tuning Set

EXEC DBMS_SQLTUNE.CREATE_SQLSET('sts_xxxxxxxxxxxxxx_1234567890');

Step 2 – Load Plan from AWR into SQL Tuning Set and then into SPM

SET SERVEROUTPUT ON

DECLARE
  baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
  x PLS_INTEGER;
BEGIN
  OPEN baseline_cursor FOR
  SELECT VALUE(p)
  FROM TABLE (
    DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
      245,
      412,
      'sql_id='||CHR(39)||'xxxxxxxxxxxxxx'||CHR(39)||
      ' and plan_hash_value=1234567890',
      NULL,NULL,NULL,NULL,NULL,NULL,'ALL'
    )
  ) p;

  DBMS_SQLTUNE.LOAD_SQLSET(
      'sts_xxxxxxxxxxxxxx_1234567890',
      baseline_cursor
  );

  x := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
        sqlset_name => 'sts_xxxxxxxxxxxxxx_1234567890',
        fixed       => 'NO',
        enabled     => 'YES'
       );

  DBMS_OUTPUT.PUT_LINE(TO_CHAR(x) || ' plan baselines loaded');
END;
/

Step 3 – Drop Temporary SQL Tuning Set (Cleanup)

EXEC DBMS_SQLTUNE.DROP_SQLSET('sts_xxxxxxxxxxxxxx_1234567890');

---

🔍 Validation

Confirm that the baseline is created and enabled:

SELECT sql_handle,
       plan_name,
       origin,
       enabled,
       accepted,
       fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%querystring%';

---

🔄 Backout Plan (If Required)

1️⃣ Identify SQL Handle and Plan Name

SELECT sql_handle,
       plan_name,
       origin,
       enabled,
       accepted,
       fixed
FROM   dba_sql_plan_baselines
WHERE  sql_text LIKE '%querystring%';

2️⃣ Drop the SQL Plan Baseline

SET SERVEROUTPUT ON
DECLARE
  rc NUMBER;
BEGIN
  rc := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
          sql_handle => '(sql_handle)',
          plan_name  => '(plan_name)'
        );
  DBMS_OUTPUT.PUT_LINE(rc);
END;
/

---

📌 Plan Restoration Flow

AWR (Historical Best Plan)
        ↓
SQL Tuning Set (STS)
        ↓
SQL Plan Management (SPM)
        ↓
Accepted SQL Plan Baseline
        ↓
Stable Performance

---

✅ Why Use SPM Instead of Adding Hints?

• No SQL code change required
• Controlled and supported method to enforce stable plans
• Can be enabled/disabled quickly
• Rollback is clean and safe
• Helps prevent future regression

---

📚 Technical References & Best Practice Notes

• Loading SQL Plans from AWR into SQL Plan Management (SPM)
  Oracle provides a supported approach to capture historical execution plans from AWR and load them as SQL Plan Baselines using STS.
  Reference: MOS Doc ID 789888.1


• SQL Tuning Health Check Script (SQLHC)
  SQLHC helps analyze execution plans, statistics, optimizer environment, and common misconfigurations impacting SQL performance.
  Reference: MOS Doc ID 1366133.1


• Oracle Database Performance Tuning Guide
  Refer to the official documentation section on SQL Plan Management (SPM) for concepts and lifecycle management.


• AWR Best Practices
  Always confirm that the historical plan represents the same SQL (and similar bind patterns) before enforcing it.

---

🏁 Conclusion

By loading the best historical plan (Plan HV: 1234567890) from AWR into SPM, we can restore stable performance and prevent regression. This approach is production-friendly because it avoids code changes and includes a clean backout option.

---

Author: Punit Kumar
Oracle EBS | Oracle 19c | Performance Tuning | SPM | AWR