Oracle Optimizer: A Practical Guide For a Good Execution Plan 

 

Have you ever seen a critical SQL query run lightning fast one day and unbelievably slow the next—without a single code change?

The usual suspect: the Oracle Optimizer choosing a different execution plan.

The optimizer constantly adapts based on new statistics, system load, and environment changes. But when performance inconsistency becomes a risk, you need stability—especially in production.

That’s where SQL Plan Management (SPM) comes in. SPM allows you to capture a known-good execution plan and ensure Oracle uses it reliably every time.

This guide walks you through identifying, capturing, verifying, enforcing, migrating, and maintaining SQL Plan Baselines so your queries stay predictable and fast.

 

1. Pre-Checks: Make Sure SPM Is Ready

Before capturing a SQL Plan Baseline, verify these prerequisites.Check if SPM is enabled

SHOW PARAMETER optimizer_use_sql_plan_baselines;

 Ensure required privileges are granted

• ADMINISTER SQL MANAGEMENT OBJECT — to run DBMS_SPM
• SELECT_CATALOG_ROLE — to query AWR views (e.g., DBA_HIST_SQLSTAT)

Ask your DBA to grant them if needed.

 

2. Identify the SQL and Its “Golden” Execution Plan

Every SQL statement has:

a SQL_ID

one or more plans identified by PLAN_HASH_VALUE (PHV)

If the good plan was used recently (in memory), check the cursor cache  — Find a recent plan in the cursor cache

SELECT
sql_id,
plan_hash_value,
parsing_schema_name,
executions
FROM
gv$sqlarea
WHERE
sql_id = ‘&SQL_ID’;

 

 If the good plan is older, search AWR history — Find a historical plan in AWR

SELECT
snap_id,
sql_id,
plan_hash_value,
elapsed_time_delta,
executions_delta
FROM
dba_hist_sqlstat
WHERE
sql_id = ‘&SQL_ID’
ORDER BY
elapsed_time_delta DESC; — Find the fastest executions

Once you have your SQL_ID and PLAN_HASH_VALUE, you’re ready to create the baseline.

 

3. Create the SQL Plan Baseline

You can load a baseline either from:

the cursor cache (preferred when available), or

AWR (when the plan is no longer in memory)

Option A — Load from the Cursor Cache (Fastest Method)

DECLARE
l_loaded NUMBER;
BEGIN
l_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => ‘&SQL_ID’,
plan_hash_value => &PLAN_HASH_VALUE,
enabled => ‘YES’
);

DBMS_OUTPUT.PUT_LINE(‘Baselines loaded: ‘ || l_loaded);
END;
/

Option B — Load from AWR (When the Plan Is Not in Cache)

1️⃣ Identify snapshot range. Find the begin and end snapshot IDs

SELECT
MIN(snap_id) begin_snap,
MAX(snap_id) end_snap
FROM
dba_hist_snapshot
WHERE
end_interval_time BETWEEN TO_DATE(‘&START_TIME’, ‘YYYY-MM-DD HH24:MI’)
AND TO_DATE(‘&END_TIME’, ‘YYYY-MM-DD HH24:MI’);

 

2️⃣ Load plan from AWR . Load the baseline from the AWR snapshot window

DECLARE
l_loaded NUMBER;
BEGIN
l_loaded := DBMS_SPM.LOAD_PLANS_FROM_AWR(
begin_snap => &BEGIN_SNAP,
end_snap => &END_SNAP,
sql_id => ‘&SQL_ID’,
plan_hash_value => &PLAN_HASH_VALUE,
enabled => ‘YES’
);

DBMS_OUTPUT.PUT_LINE(‘Baselines loaded: ‘ || l_loaded);
END;
/

4. Verify the Baseline and (Optionally) Fix It 🔍 Check the newly created baseline

SELECT
sql_handle,
plan_name,
enabled,
accepted,
fixed,
created
FROM
dba_sql_plan_baselines
WHERE
sql_text LIKE ‘%<unique fragment of the SQL>%’;

By default:

ENABLED = YES

ACCEPTED = YES

This means the optimizer can choose it when appropriate.

💡 Best Practice:

Do not fix a plan immediately. Let it run for some time.
Fix it only when you are 100% confident it performs best under all conditions.

📌 Fix the plan (optional)

DECLARE
l_out PLS_INTEGER;
BEGIN
l_out := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => ‘&SQL_HANDLE’,
plan_name => ‘&PLAN_NAME’,
attribute_name => ‘fixed’,
attribute_value => ‘YES’
);
END;
/

 

5. Validate That the Baseline Is Being Used

Run the query again and check the execution plan:

— Show the executed plan and check the notes

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ‘BASIC +NOTE’));

You should see a line like:

Note
—–
– SQL plan baseline “PLAN_NAME_HERE” used for this statement

You can also check via:

SELECT sql_id, sql_plan_baseline, plan_hash_value
FROM gv$sql
WHERE sql_id = ‘&SQL_ID’;

 

6. Migrate Baselines Between Environments (Optional)

To move a baseline from UAT → PROD, use SPM staging tables.

📦 Pack baseline in source environment

BEGIN
— Create the staging table
DBMS_SPM.CREATE_STGTAB_BASELINE(table_name => ‘SPM_STAGE’, schema_name => ‘APPS’);

— Pack the desired baseline into the table
DBMS_SPM.PACK_STGTAB_BASELINE(
table_name => ‘SPM_STAGE’,
schema_name => ‘APPS’,
sql_handle => ‘&SQL_HANDLE’
);
END;
/

Export the SPM_STAGE table using Data Pump and import it into the target DB.

📦 Unpack in target environment
BEGIN
DBMS_SPM.UNPACK_STGTAB_BASELINE(
table_name => ‘SPM_STAGE’,
schema_name => ‘APPS’
);
END;
/

 

7. Baseline Maintenance — Disable or Drop

🚫 Disable a baseline without deleting it
DECLARE
n PLS_INTEGER;
BEGIN
n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => ‘&SQL_HANDLE’,
plan_name => ‘&PLAN_NAME’,
attribute_name => ‘enabled’,
attribute_value => ‘NO’
);
END;
/

❌ Drop a baseline permanently
DECLARE
n PLS_INTEGER;
BEGIN
n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => ‘&SQL_HANDLE’,
plan_name => ‘&PLAN_NAME’
);
END;
/

 

8. When to Use SQL Profiles Instead of Baselines

SPM requires exact SQL text matching, which may not work if your application uses varying literals.

In such cases, a SQL Profile may be more effective—it applies optimizer hints to guide plan selection while allowing literal variations.

📌 Get outline hints from the good plan

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘&SQL_ID’, NULL, ‘ADVANCED’));
— Copy the hints from the “Outline Data” section

📌 Import the SQL Profile
DECLARE
h SYS.SQLPROF_ATTR;
BEGIN
h := SYS.SQLPROF_ATTR(
‘USE_HASH_AGGREGATION(@SEL$1)’,
‘LEADING(@”SEL$1″ “T1″@”SEL$1” “T2″@”SEL$1”)’,
‘INDEX_RS_ASC(“T1″@”SEL$1” “T1_IDX”)’
— Paste all other outline hints here
);

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => q'[ PASTE THE EXACT SQL TEXT HERE ]’,
profile => h,
name => ‘PROF_FIX_MY_QUERY’,
force_match => TRUE, — Set TRUE to match queries with different literals
replace => TRUE
);
END;
/

SQL Profiles give flexibility but less determinism. Prefer SPM baselines for strict plan stability.

 

9. Practical Tips & Common Pitfalls

🔸 Exact SQL Text Matters

Even a space or case change can prevent baseline matching.

🔸 Watch for Statistics Drift

Old or stale statistics can cause cardinality shifts—even with a baseline.

🔸 Bind Peeking Still Happens

If bind sensitivity causes unstable plans, avoid fixing the baseline too early.

🔸 If the baseline isn’t being used, check:

optimizer_use_sql_plan_baselines = TRUE