Fixing Execution Plans

To Check if the execution plan of an Oracle query has changed and how to fix (stabilize or restore) it, you can follow a step-by-step process. This includes:

---

🔍 Step 1: Capture the Current Execution Plan

Use the DBMS_XPLAN package to see the current plan of a query.

Query:

EXPLAIN PLAN FOR

SELECT * FROM your_table WHERE column = 'value';

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Or for an already executed SQL:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

---

🗂️ Step 2: Check for Historical Execution Plans

Oracle stores previous execution plans in the AWR (if licensed) or you can use SQL Plan Baselines (if enabled).

Query from AWR (requires Oracle Tuning Pack):

SELECT

  h.sql_id,

  h.plan_hash_value,

  h.snap_id,

  to_char(s.begin_interval_time, 'DD-MON-YYYY HH24:MI') begin_time,

  h.executions_delta,

  h.elapsed_time_delta / 1000000 elapsed_seconds

FROM

  dba_hist_sqlstat h,

  dba_hist_snapshot s

WHERE

  h.snap_id = s.snap_id

  AND h.dbid = s.dbid

  AND h.instance_number = s.instance_number

  AND h.sql_id = 'your_sql_id'

ORDER BY

  h.snap_id;

You can compare plan_hash_values from different times to detect a change.

---

🛠️ Step 3: Check SQL Plan Baselines (if using)

If you're using SQL Plan Management (SPM):

SELECT sql_handle, plan_name, enabled, accepted, fixed, origin, plan_hash_value

FROM dba_sql_plan_baselines

WHERE sql_text LIKE '%your_query%';

---

🧪 Step 4: Compare Plans

If plan_hash_value has changed across time or between environments, the plan has changed. Use:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', old_plan_hash_value));

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('your_sql_id', new_plan_hash_value));

---

✅ Step 5: Fix (Stabilize) the Execution Plan

There are multiple methods to fix or stabilize the execution plan:

---

📌 Option 1: Create a SQL Plan Baseline

Capture and accept a good plan as a baseline.

-- Load plan for a SQL_ID into baseline

DECLARE

  l_sql_text CLOB;

BEGIN

  SELECT sql_fulltext INTO l_sql_text FROM v$sql WHERE sql_id = 'your_sql_id';

 

  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(

    sql_id => 'your_sql_id',

    plan_hash_value => your_good_plan_hash,

    sql_text => l_sql_text

  );

END;

/

Then, verify and fix the plan:

-- Fix the baseline

UPDATE dba_sql_plan_baselines

SET fixed = 'YES'

WHERE sql_handle = 'your_sql_handle' AND plan_name = 'your_plan_name';

---

📌 Option 2: Create a SQL Profile (Using SQL Tuning Advisor)

If using SQL Tuning Advisor (licensed), it can suggest better plans:

-- Run tuning task

DECLARE

  l_sql_tune_task_id VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK(

    sql_id => 'your_sql_id',

    scope => 'COMPREHENSIVE',

    time_limit => 60,

    task_name => 'tune_task1'

  );

 

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_task1');

END;

/

 

-- View recommendation

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_task1') FROM dual;

If a SQL Profile is recommended, accept it:

EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'tune_task1', name => 'profile1');

---

📌 Option 3: Use SQL Patch (for one-off fixes)

To force a plan change via hints:

BEGIN

  DBMS_SQLDIAG.CREATE_SQL_PATCH(

    sql_id => 'your_sql_id',

    hint_text => 'USE_NL(table1 table2)',

    name => 'force_nested_loop_patch'

  );

END;

---

📌 Option 4: Use Stored Outlines (Deprecated in 19c+, but still works)

ALTER SESSION SET USE_STORED_OUTLINES = TRUE;

-- Then run your query

Stored outlines are now replaced by SQL Plan Baselines.

---

🧼 Step 6: Clear Bad Baselines or Profiles (if needed)

If a bad plan was fixed and needs to be removed:

-- Drop SQL Profile

EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('profile1');

 

-- Drop SQL Plan Baseline

EXEC DBMS_SPM.DROP_SQL_PLAN_BASELINE(plan_name => 'your_plan_name');

---

📋 Summary of Key Queries

Purpose	Query
Show current plan	DBMS_XPLAN.DISPLAY_CURSOR
Show historical plan (AWR)	DBMS_XPLAN.DISPLAY_AWR
List SQL plan baselines	SELECT * FROM dba_sql_plan_baselines
Load a good plan into baseline	DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
Fix a plan in baseline	UPDATE dba_sql_plan_baselines SET fixed = 'YES'
Accept SQL Profile (tuning)	DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
Force hint with SQL patch	DBMS_SQLDIAG.CREATE_SQL_PATCH
Drop SQL Profile/Baseline	DBMS_SQLTUNE.DROP_SQL_PROFILE, DBMS_SPM.DROP_SQL_PLAN_BASELINE

---