Step-by-Step Guide: Fixing SQL Plan Baseline in Oracle

Understanding SQL Plan Baseline vs SQL Profile

Feature	SQL Plan Baseline	SQL Profile
Purpose	Ensures Oracle chooses a consistent execution plan	Provides optimizer hints to improve SQL execution
Stored Where?	DBA_SQL_PLAN_BASELINES table	DBA_SQL_PROFILES table
Plan Selection	Maintains multiple accepted plans but can "fix" one	Provides hints but doesn’t force a plan
Usage	Used for plan stability across executions	Used to tune SQL performance by guiding optimizer
Manual Fixing?	Yes, DBA can mark a plan as fixed	No, optimizer still decides execution path
Performance Impact	Prevents plan regressions due to optimizer changes	Helps the optimizer make better choices
Creation	Auto-captured if `OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE` or manually added	Created using SQL Tuning Advisor (`DBMS_SQLTUNE.ACCEPT_SQL_PROFILE`)

Step 1: Find the Current Execution Plan for a SQL_ID

Before fixing a baseline, identify which execution plan is currently being used.

SELECT SQL_ID, PLAN_HASH_VALUE, CHILD_NUMBER, EXECUTIONS, ELAPSED_TIME/1000000 AS ELAPSED_SEC FROM V$SQL WHERE SQL_ID = 'your_sql_id';

Output Columns Explanation

SQL_ID → Identifies the SQL statement
PLAN_HASH_VALUE → The current execution plan in use
CHILD_NUMBER → Different versions of the execution plan
EXECUTIONS → Number of times the SQL has been executed
ELAPSED_SEC → Total execution time in seconds

Step 2: Check If a SQL Plan Baseline Already Exists

To check if a SQL Plan Baseline exists for this SQL:

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%your_sql_text%';

Output Interpretation

If no rows are returned → No SQL Plan Baseline exists
If FIXED = YES → Oracle is already using a fixed baseline
If multiple baselines exist → Oracle can choose the best one unless one is fixed

Also we can use below query to find the Plan details based on signature.

select sql_id,

to_char(exact_matching_signature),

to_char(force_matching_signature)

from v$sql

where sql_id ='your_sql_id';

EXACT_MATCHING_SIGNATURE → Unique identifier for queries with identical text and bind variables

FORCE_MATCHING_SIGNATURE → Matches SQL statements regardless of bind variables

Step 3: Create a SQL Plan Baseline (If Not Already Exists)

If no baseline exists, create one manually:


DECLARE
    v_plans PLS_INTEGER;
BEGIN
    v_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
        sql_id => 'your_sql_id'
    );
END;
/

✅ This captures the current plan into the SQL Plan Baseline.

Performance-How I Create SQL Baseline to fix my EBS query with an Execution Plan

Step 4: Mark a Specific Plan as Fixed

Once the baseline is created, fix a specific PLAN_HASH_VALUE:


BEGIN
    DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle => 'your_sql_handle',
        plan_name  => 'your_plan_name',
        attribute_name  => 'FIXED',
        attribute_value => 'YES'
    );
END;
/

✅ This forces Oracle to use only this fixed plan.

Example:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE
(sql_handle =>'SQL_26188652b1a3007e',
plan_name=>'SQL_PLAN_2c646aasu603ye3e16414',
attribute_name=>'FIXED',
attribute_value=>'YES');
end;
/

Step 5: Verify the Fixed Plan

To confirm that the plan is now fixed:

SELECT SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED, PLAN_HASH_VALUE FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%your_sql_text%';

Ensure:

FIXED = YES
ACCEPTED = YES
ENABLED = YES

Step 6: Verify If Oracle Is Using the Fixed Plan

After some executions, check if the fixed plan is in use:

SELECT SQL_ID, PLAN_HASH_VALUE, EXECUTIONS, ELAPSED_TIME/1000000 AS ELAPSED_SEC FROM V$SQL WHERE SQL_ID = 'your_sql_id';

If Flush the shared pool to remove the old plan:


ALTER SYSTEM FLUSH SHARED_POOL;

Then re-run your query and check again.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Step-by-Step Guide: Fixing SQL Plan Baseline in Oracle