Create a SQL Plan Baseline in Oracle

What is a SQL Plan Baseline and When Should You Use It?

A SQL Plan Baseline ensures Oracle sticks to a known, stable execution plan—helping prevent performance regressions when the optimizer generates new plans.

📌 When to Create a Baseline:

You’ve found a reliable and fast plan, especially for critical or high-load queries.
The optimizer starts using unpredictable plans after changes like:
- Database upgrades
- Statistics gathering
- Schema modifications
You need consistent performance across environments (Prod, QA, Dev).
You’re planning a version or platform upgrade and want to lock in proven plans ahead of time.

📝 Recommendation:
Create a baseline before any major system change—if the current plan works well. It’s a smart way to avoid unexpected slowdowns later.

Steps to Create a SQL Plan Baseline

Step 1: Connect as SYSTEM

You need to be logged in as a privileged user (e.g., SYSTEM) to create and manage baselines.

SQL> SHOW USER;
USER is "SYSTEM"

Step 2: Identify Available Plans for the SQL_ID

This helps you see all execution plans used recently for a query, so you can pick the most efficient one.

SET LINESIZE 150
SET PAGESIZE 50
COL sql_id FORMAT A15
COL plan_hash_value FORMAT 9999999999
COL last_snap FORMAT 99999
--Get last snapshot ID for each plan of a given SQL_ID
SELECT 
    sql_id, 
    plan_hash_value, 
    MAX(snap_id) AS last_snap
FROM 
    dba_hist_sqlstat
WHERE 
    sql_id = 'f14nzf4gyq4y4'  -- 🔄 Replace with your target SQL_ID
GROUP BY 
    sql_id, 
    plan_hash_value
ORDER BY 
    last_snap DESC;

Sample Output :

SQL_ID         PLAN_HASH_VALUE    LAST_SNAP
-------------  -----------------  ----------
f14nzf4gyq4y4  2881287423         21662
f14nzf4gyq4y4  1734569820         21660

Note: Each plan_hash_value represents a different execution plan. In later steps, you’ll pick the one with best performance (lowest elapsed time per execution).

Step 3: Review Plan Performance (Day-wise)

This gives you plan-level performance history using AWR data. You can see elapsed time, CPU usage, and buffer reads for each plan used over time.

CLEAR COLUMNS BREAKS COMPUTES
SET LINESIZE 200
SET PAGESIZE 1000
COL sql_id FOR A15 HEAD "SQL|ID"
COL s_time FOR A12 HEAD "Snap|Time"
COL executions FOR 999999999 HEAD "Execs"
COL elapsed_t FOR 99999999.99 HEAD "Elapsed|Secs"
COL cpu_t FOR 99999999.99 HEAD "CPU|Secs"
COL user_io FOR 99999999.99 HEAD "User I/O|Secs"
COL buffer_gets FOR 999999999 HEAD "Buffer|Gets"
COL disk_r FOR 999999999 HEAD "Disk|Reads"
COL rows_processed FOR 999999999 HEAD "Rows|Processed"
COL sec_per_exec FOR 99999999.9999 HEAD "Seconds|per Exec"
COL plan_hash_value FOR A15 HEAD "Plan|Hash"
BREAK ON sql_id ON plan_hash_value SKIP 1
ACCEPT 1_sqlid PROMPT 'Enter SQL ID: '
ACCEPT 1_days DEFAULT 42 PROMPT 'Enter days in the past (default 42): '
SELECT 
    sql_id,
    TO_CHAR(plan_hash_value) plan_hash_value,
    TO_CHAR(begin_interval_time, 'DD-MON-YYYY') s_time,
    SUM(executions_delta) executions,
    ROUND(SUM(elapsed_time_delta) / 1e6, 2) elapsed_t,
    ROUND(SUM(cpu_time_delta) / 1e6, 2) cpu_t,
    ROUND(SUM(iowait_delta) / 1e6, 2) user_io,
    ROUND(SUM(buffer_gets_delta)) buffer_gets,
    ROUND(SUM(disk_reads_delta)) disk_r,
    ROUND(SUM(rows_processed_delta)) rows_processed,
    DECODE(SUM(executions_delta), 0, ROUND(SUM(elapsed_time_delta) / 1e6, 2),
        ROUND(SUM(elapsed_time_delta)/1e6/SUM(executions_delta), 4)) sec_per_exec
FROM 
    dba_hist_sqlstat a, dba_hist_snapshot b
WHERE 
    a.snap_id = b.snap_id
    AND a.instance_number = b.instance_number
    AND a.sql_id = '&&1_sqlid'
    AND TRUNC(begin_interval_time) >= TRUNC(SYSDATE - &&1_days)
GROUP BY 
    sql_id, plan_hash_value, TO_CHAR(begin_interval_time, 'DD-MON-YYYY')
ORDER BY 
    TO_DATE(TO_CHAR(begin_interval_time, 'DD-MON-YYYY'), 'DD-MON-YYYY'),
    plan_hash_value;

Note: This helps you choose the best plan for baseline creation. Focus on plans with consistently low sec_per_exec.

Step 4: Create and Load a SQL Tuning Set (STS)

An STS is a container for SQL statements and their performance data—useful for tuning tasks. Here’s how to decide what to do in different scenarios:

4.1 Check if an STS Already Exists

SELECT owner, name
FROM dba_sqlset
WHERE owner = 'SYSTEM';

Note: If you already have an STS (e.g., STS_SYSTEM_3) with the SQL ID you want to baseline, you can reuse it—no need to recreate.

4.2 Drop the STS (Optional)

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET('STS_SYSTEM_3');  -- 🔄 Replace if needed
END;
/

When to drop:

If you’re sure the STS is old or you want a fresh start.
Avoid dropping if it’s shared or part of scheduled tasks—dropping an active STS can cause ORA-13757 errors

4.3 Create a New SQL Tuning Set

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name  => 'STS_SYSTEM_3',  -- 🔄 Choose a meaningful name
    sqlset_owner => 'SYSTEM'         -- 🔄 Typically your user/schema
  );
END;
/

Why create new:

If you are confused and not sure about existing SQL Tuning Set like can be dropped or not then it is always good to create new one for you to avoid any issue.

4.4 Load the plan into the tuning set

DECLARE
  cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
    SELECT VALUE(a)
    FROM TABLE(
      DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
        begin_snap     => 21661,  -- 🔄 Start snapshot ID
        end_snap       => 21663,  -- 🔄 End snapshot ID
        basic_filter   => 'sql_id = ''f14nzf4gyq4y4''',  -- 🔄 Target SQL_ID
        attribute_list => 'ALL'
      )
    ) a;

  DBMS_SQLTUNE.LOAD_SQLSET(
    sqlset_name     => 'STS_SYSTEM_3',  -- 🔄 Your SQL Tuning Set name
    populate_cursor => cur
  );
END;
/

Step 5: Create the SQL Plan Baseline

DECLARE
  custom_plan PLS_INTEGER;
BEGIN
  custom_plan := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name   => 'STS_SYSTEM_3',                -- 🔄 Name of the SQL Tuning Set
    basic_filter  => 'plan_hash_value = ''2881287423'''  -- 🔄 Target Plan Hash Value
  );
END;
/

Step 6: Verify Created Baseline

SET LINESIZE 180
SET PAGESIZE 100
SET UNDERLINE '-'
COLUMN signature      FORMAT A20   HEADING "Signature"
COLUMN sql_handle     FORMAT A30   HEADING "SQL Handle"
COLUMN plan_name      FORMAT A35   HEADING "Plan Name"
COLUMN created        FORMAT A20   HEADING "Created (DT)"
COLUMN enabled        FORMAT A6    HEADING "Enabled"
COLUMN accepted       FORMAT A8    HEADING "Accepted"
COLUMN fixed          FORMAT A5    HEADING "Fixed"
COLUMN reproduced     FORMAT A10   HEADING "Reproduced"
WITH tmp AS (
  SELECT exact_matching_signature signature
  FROM v$sql
  WHERE sql_id = 'f14nzf4gyq4y4' -- 🔄 Replace with your SQL_ID
)
SELECT
  TO_CHAR(a.signature)  AS signature,
  sql_handle,
  plan_name,
  TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS created,
  enabled,
  accepted,
  fixed,
  reproduced
FROM
  dba_sql_plan_baselines a, tmp
WHERE
  a.signature = tmp.signature
ORDER BY
  created;

Step 7: Rename the Baseline (Optional but Recommended)

DECLARE
  custom_plan PLS_INTEGER;
BEGIN
  custom_plan := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'SQL_abcdefgh1234567',          -- 🔄 Replace with actual SQL handle
    plan_name       => 'SQL_PLAN_abcdefgh1234567',     -- 🔄 Replace with current baseline plan name
    attribute_name  => 'PLAN_NAME',
    attribute_value => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4' -- 🔄 Replace with new custom plan name
  );
END;
/

Step 8: Fix the Baseline (Make it Always Used — Optional)

DECLARE
  custom_plan PLS_INTEGER;
BEGIN
  custom_plan := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'SQL_abcdefgh1234567',            -- 🔄 Replace with actual SQL handle
    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4',  -- 🔄 Replace with plan name to be fixed
    attribute_name  => 'FIXED',
    attribute_value => 'YES'
  );
END;
/

⚠️ Note: Only fix the plan if you’re sure it’s consistently optimal. Fixing prevents Oracle from evolving to better plans automatically.

Reference: Additional DBA Scripts (Not Part of Main Steps)

These scripts are for DBA reference and post-baseline maintenance. They’re not part of the core baseline creation steps but are extremely helpful when you need to clean up, manage, or monitor baselines later.

Drop a SQL Plan Baseline

When to Use: Use this if a baseline is no longer needed or created by mistake. This completely removes it from the system.

DECLARE
  result PLS_INTEGER;
BEGIN
  result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_abcdefgh1234567',           -- 🔄 Replace with actual SQL_HANDLE (from DBA_SQL_PLAN_BASELINES)
    plan_name  => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4'  -- 🔄 Replace with actual PLAN_NAME to drop
  );
  DBMS_OUTPUT.PUT_LINE('Baseline dropped. Result = ' || result);
END;
/

Unfix a SQL Plan Baseline

When to Use: If a baseline is fixed and you want Oracle to consider better plans in the future, unfix it with this.

DECLARE
  result PLS_INTEGER;
BEGIN
  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'SQL_abcdefgh1234567',           -- 🔄 Replace with actual SQL_HANDLE
    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4', -- 🔄 Replace with actual PLAN_NAME
    attribute_name  => 'FIXED',
    attribute_value => 'NO'                             -- 'NO' means plan is no longer fixed (Oracle can evolve to better ones)
  );
  DBMS_OUTPUT.PUT_LINE('Baseline unfixed. Result = ' || result);
END;
/

View All Baselines for a SQL ID

When to Use: Use this to see all plan baselines associated with a SQL_ID — including their status (enabled, fixed, accepted).

SET LINESIZE 150
SET PAGESIZE 100
SET UNDERLINE '-'
COLUMN sql_handle   FORMAT A30   HEADING 'SQL Handle'
COLUMN plan_name    FORMAT A35   HEADING 'Plan Name'
COLUMN enabled      FORMAT A7    HEADING 'Enabled'
COLUMN accepted     FORMAT A8    HEADING 'Accepted'
COLUMN fixed        FORMAT A6    HEADING 'Fixed'
COLUMN reproduced   FORMAT A10   HEADING 'Reproduced'
COLUMN created      FORMAT A20   HEADING 'Created (DT)'
SELECT 
  sql_handle, 
  plan_name, 
  enabled, 
  accepted, 
  fixed, 
  reproduced,
  TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS created
FROM 
  dba_sql_plan_baselines
WHERE 
  signature IN (
    SELECT exact_matching_signature 
    FROM v$sql 
    WHERE sql_id = 'f14nzf4gyq4y4'  -- 🔄 Replace with your SQL_ID
  );

Notes:

enabled: Indicates whether the plan is available for use.
accepted: Shows if the plan was accepted into the baseline.
fixed: Tells whether Oracle is forced to use this plan.
reproduced: If NO, it means the plan hasn’t yet been reproduced in the current system.
created: Useful to track how old or new the baseline is.

Clean Up SQL Tuning Set (STS)

When to Use: If you created a SQL Tuning Set temporarily (e.g., to load a plan), you can safely drop it afterward.

BEGIN
  DBMS_SQLTUNE.DROP_SQLSET('STS_SYSTEM_3');  -- Replace with your STS name
END;
/

Rename a SQL Plan Baseline (Optional)

When to Use: Useful for giving the plan a clearer, meaningful name (especially helpful when you have many baselines).

DECLARE
  result PLS_INTEGER;
BEGIN
  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'SQL_abcdefgh1234567',          -- 🔄 Replace with actual SQL handle
    plan_name       => 'SQL_PLAN_abcdefgh1234567',     -- 🔄 Replace with current/old plan name
    attribute_name  => 'PLAN_NAME',
    attribute_value => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4' -- 🔄 Replace with desired new plan name
  );
END;
/

View Baseline Usage Statistics

When to Use: Check how often a baseline is used, whether it’s enabled/fixed, and when it was last executed.

SET LINESIZE 150
SET PAGESIZE 50
COLUMN sql_handle     FORMAT A25  HEADING 'SQL Handle'
COLUMN plan_name      FORMAT A35  HEADING 'Plan Name'
COLUMN executions     FORMAT 999999 HEADING 'Executions'
COLUMN last_executed  FORMAT A20  HEADING 'Last Executed'
COLUMN enabled        FORMAT A7   HEADING 'Enabled'
COLUMN accepted       FORMAT A8   HEADING 'Accepted'
COLUMN fixed          FORMAT A5   HEADING 'Fixed'
COLUMN created        FORMAT A20  HEADING 'Created'
SELECT 
  sql_handle,
  plan_name,
  executions,
  TO_CHAR(last_executed, 'DD-MON-YYYY HH24:MI') AS last_executed,
  enabled,
  accepted,
  fixed,
  TO_CHAR(created, 'DD-MON-YYYY HH24:MI') AS created
FROM 
  dba_sql_plan_baselines
WHERE 
  sql_handle = 'SQL_abcdefgh1234567';  -- 🔄 Replace with actual SQL handle

Notes:

Use this to monitor plan usage, especially after implementing a baseline.
If executions is 0, the plan hasn’t been used yet.
last_executed shows the most recent usage timestamp.
Replace 'SQL_abcdefgh1234567' with the actual sql_handle from your environment (e.g., from DBA_SQL_PLAN_BASELINES or DBA_HIST_SQLSTAT joins).

Evolve Baselines Automatically (Accept Better Plans)

When to Use: Use this to let Oracle evaluate unaccepted plans and accept them if they perform better.

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  report CLOB;
BEGIN
  report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle => 'SQL_abcdefgh1234567',  -- 🔄 Replace with your actual SQL handle
    plan_name  => NULL,                   -- NULL = evolve all unaccepted plans for this handle
    time_limit => 60,                     -- Time limit (in seconds) for plan evolution
    verify     => 'YES',                  -- Compare plans based on performance
    commit     => 'YES'                   -- Automatically accept better-performing plans
  );
  -- Print evolution report
  DBMS_OUTPUT.PUT_LINE(report);
END;
/

Notes:

Replace 'SQL_abcdefgh1234567' with your actual SQL handle (from DBA_SQL_PLAN_BASELINES).
If plan_name is NULL, all unaccepted plans under that sql_handle will be evaluated.
Useful after loading plans from AWR or SQL tuning set to determine if they should be accepted.
verify => 'YES' ensures Oracle compares plan performance before accepting.
commit => 'YES' applies accepted plans automatically — set to 'NO' if you want to review manually first.

Fix a Plan (Make It Always Preferred)

When to Use: After verifying that a plan is consistently good, you can fix it so Oracle always uses it.

DECLARE
  result PLS_INTEGER;
BEGIN
  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'SQL_abcdefgh1234567',           -- 🔄 Replace with actual SQL handle
    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4', -- 🔄 Replace with actual plan name
    attribute_name  => 'FIXED',
    attribute_value => 'YES'
  );
END;
/

Notes:

🔄 Replace placeholders:
- 'SQL_abcdefgh1234567': Get from DBA_SQL_PLAN_BASELINES.SQL_HANDLE
- 'SQL_PLAN_MANUAL_f14nzf4gyq4y4': Use the actual plan name you want to fix
Use FIXED => 'YES' only after you’re confident the plan is stable and optimal.
A fixed plan can prevent Oracle from adapting to better plans unless you evolve or unfix it later.

Add a Description (Good Practice for Documentation)

When to Use: Optional, but helps others understand why a baseline was created or modified.

DECLARE
  result PLS_INTEGER;
BEGIN
  result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
    sql_handle      => 'SQL_abcdefgh1234567',               -- 🔄 Replace with actual SQL handle
    plan_name       => 'SQL_PLAN_MANUAL_f14nzf4gyq4y4',     -- 🔄 Replace with actual plan name
    attribute_name  => 'DESCRIPTION',
    attribute_value => 'Manual baseline for report query - 18-JUN-2025'  -- 🔄 Customize description as needed
  );
END;
/

Create a SQL Plan Baseline in Oracle

What is a SQL Plan Baseline and When Should You Use It?

Steps to Create a SQL Plan Baseline

Step 1: Connect as SYSTEM

Step 2: Identify Available Plans for the SQL_ID

Sample Output :

Step 3: Review Plan Performance (Day-wise)

Step 4: Create and Load a SQL Tuning Set (STS)

4.1 Check if an STS Already Exists

4.2 Drop the STS (Optional)

4.3 Create a New SQL Tuning Set

4.4 Load the plan into the tuning set

Step 5: Create the SQL Plan Baseline

Step 6: Verify Created Baseline

Step 7: Rename the Baseline (Optional but Recommended)

Step 8: Fix the Baseline (Make it Always Used — Optional)

Reference: Additional DBA Scripts (Not Part of Main Steps)

Drop a SQL Plan Baseline

Unfix a SQL Plan Baseline

View All Baselines for a SQL ID

Notes:

Clean Up SQL Tuning Set (STS)

Rename a SQL Plan Baseline (Optional)

View Baseline Usage Statistics

Notes:

Evolve Baselines Automatically (Accept Better Plans)

Notes:

Fix a Plan (Make It Always Preferred)

Notes:

Add a Description (Good Practice for Documentation)

Step 4: Create and Load a SQL Tuning Set (STS)