Taming the Oracle Optimizer: Your Guide to Locking in a Good Execution Plan 🚀
Ever had a critical SQL query that ran perfectly fast yesterday, but is crawling today? You haven't changed the code, so what gives? The culprit is often the Oracle Optimizer changing its mind about the execution plan—the internal "road map" it uses to fetch your data.
When performance is unpredictable, you need to take control. SQL Plan Management (SPM) is Oracle's built-in feature that lets you find a "golden" execution plan and tell the database to use it every time. This guide will walk you through how to capture and enforce a good plan using SQL Plan Baselines.
## Before You Start: Quick Checks & Privileges
Before diving in, make sure your environment is ready.
Confirm SPM is enabled: The
optimizer_use_sql_plan_baselinesparameter must be set toTRUE. Run this check:SQLSHOW PARAMETER optimizer_use_sql_plan_baselines;Ensure you have the right privileges: You'll need specific permissions to manage baselines and query performance data. Your DBA can grant you these:
ADMINISTER SQL MANAGEMENT OBJECT: Required for using theDBMS_SPMpackage.SELECT_CATALOG_ROLE: Required for querying the Automatic Workload Repository (AWR) views likedba_hist_sqlstat.
## Step 1: Identify the SQL and the "Good" Plan
First, you need to find the specific query and the high-performing execution plan you want to stabilize. Every query has a unique SQL_ID, and each of its execution plans has a PLAN_HASH_VALUE (PHV).
If the good plan ran recently, you can find it in the cursor cache:
SQL-- 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, you'll need to look in the AWR history:
SQL-- 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 the SQL_ID and the PLAN_HASH_VALUE of your desired plan, you're ready to create a baseline.
## Step 2: Create the SQL Plan Baseline
You can load a baseline from either the live cursor cache or the historical AWR data.
### Option A: From the Cursor Cache (The Quickest Method)
Use this method if the good plan is still in memory. It's the fastest way to create a baseline.
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: From AWR (When the Plan is Not in Cache)
If the plan is no longer in the cache, you can pull it from AWR. First, identify a snapshot window when the good plan was running.
-- 1. 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 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;
/
## Step 3: Verify the Baseline and (Optionally) "Fix" It
After loading the plan, verify that the baseline was created. You'll need the SQL_HANDLE for future actions.
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, a new baseline is ENABLED and ACCEPTED. This means the optimizer will consider it. If you want to force the optimizer to only use this plan, you can set it to FIXED.
Best Practice: Avoid fixing a plan immediately. Let it run as
ENABLEDandACCEPTEDfirst. Only fix it once you are absolutely certain this plan is the best choice under all conditions.
-- Optionally "fix" the plan to pin it
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;
/
## Step 4: Test That the Baseline Is Used ✅
Now for the final check! Run your query again and inspect the execution plan details.
-- Show the executed plan and check the notes
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'BASIC +NOTE'));
In the output, you should see a "Note" section confirming that your baseline was used:
Note
-----
- SQL plan baseline "PLAN_NAME_HERE" used for this statement
You can also query gv$sql to see which baseline is attached to your session's cursor.
SELECT sql_id, sql_plan_baseline, plan_hash_value FROM gv$sql WHERE sql_id = '&SQL_ID';
## Step 5 (Optional): Migrate Baselines Between Databases
If you've identified and tested a great plan in your UAT or test environment, you can easily migrate it to production using staging tables.
In the source database, create a staging table and pack the baseline into it.
SQLBEGIN -- 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; /Move the
SPM_STAGEtable to the target database (using Data Pump or another method).In the target database, unpack the baseline from the staging table.
SQLBEGIN DBMS_SPM.UNPACK_STGTAB_BASELINE( table_name => 'SPM_STAGE', schema_name => 'APPS' ); END; /
## Step 6: Maintenance - How to Back Out
If a baseline is no longer needed or is causing issues, you can either disable it (keeping it for reference) or drop it completely.
Disable a baseline:
SQLDECLARE 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:
SQLDECLARE n PLS_INTEGER; BEGIN n := DBMS_SPM.DROP_SQL_PLAN_BASELINE( sql_handle => '&SQL_HANDLE', plan_name => '&PLAN_NAME' ); END; /
## An Alternative: When to Use a SQL Profile
Sometimes, an application generates SQL with varying literals (e.g., WHERE id = 101 vs. WHERE id = 205). SPM requires an exact text match, so it may not work here. In these cases, a SQL Profile is a better choice.
A SQL Profile doesn't lock a plan; instead, it attaches a set of hints to a query to "nudge" the optimizer toward the right plan shape.
Get the Outline Hints for the good plan:
SQLSELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ADVANCED')); -- Copy the hints from the "Outline Data" sectionImport a SQL Profile with those hints:
SQLDECLARE 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; /
While flexible, SQL Profiles offer less deterministic control than a fixed SPM baseline. Prefer SPM for strict plan stability.
## Practical Tips & Common Gotchas
Exact Text Match: SPM is picky about SQL text. If your application uses literals instead of bind variables, consider setting
CURSOR_SHARINGtoFORCEor using a SQL Profile withforce_match => TRUE.Statistics Drift: A baseline forces a plan's shape (join order, access methods), but the optimizer's row count estimates can still change if statistics become stale. Keep your stats fresh!
Bind Peeking: A plan that's great for one set of bind variables might be terrible for another. If a single fixed plan isn't safe, avoid fixing it and explore adaptive features.
Troubleshooting: If your baseline isn't being used, double-check that
optimizer_use_sql_plan_baselinesisTRUE, the SQL text matches perfectly, and you don't have conflicting baselines.
No comments:
Post a Comment