How to Drop an Oracle SQL Plan Baseline (Step-by-Step Guide)

Introduction

Oracle SQL Plan Management (SPM) is one of the most powerful performance stability features introduced by Oracle. It prevents SQL statements from suddenly choosing inefficient execution plans after upgrades, statistics changes, or optimizer modifications.

However, there are situations where an existing SQL Plan Baseline is no longer required or is causing performance issues. In such cases, removing the baseline becomes necessary.

What is a SQL Plan Baseline?

A SQL Plan Baseline is a stored execution plan that Oracle considers acceptable for a SQL statement.

Instead of allowing the optimizer to choose any available execution plan, Oracle uses the accepted baseline to maintain consistent performance.

This protects applications from unexpected execution plan changes.

When Should You Drop a SQL Plan Baseline?

Dropping a baseline is recommended when:

• The execution plan has become inefficient.
• A better execution plan has been captured.
• The baseline was created for testing purposes.
• SQL tuning has changed the application's execution strategy.
• Old or unused baselines need cleanup.

Note: Never remove a SQL Plan Baseline directly in production without verifying that another accepted plan exists. After dropping a baseline, monitor the SQL statement to ensure the optimizer selects an optimal execution plan.

Step 1: Find the SQL Plan Baseline

Suppose you know the SQL ID.

SQL> select sql_id, sql_plan_baseline from v$sql where sql_id='79n2yxxku21st';

SQL_ID        SQL_PLAN_BASELINE
------------- --------------------------------------------------------------------------------------------------------------------------------
79n2yxxku21st SQL_PLAN_fyx86gn5gubub12827765

We have identified the associated baseline.

Step 2: Find the SQL Handle

The DBMS_SPM package requires both:

• SQL Handle
• Plan Name

Retrieve them using:

SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name='SQL_PLAN_fyx86gn5gubub12827765';

SQL_HANDLE                     PLAN_NAME
------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SQL_ef75069f4fud2f4b           SQL_PLAN_fyx86gn5gubub12827765

Now we have both required values.

Step 3: Drop the SQL Plan Baseline

Execute the following PL/SQL block:

SQL> declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_ef75069f4fud2f4b',
plan_name => 'SQL_PLAN_fyx86gn5gubub12827765');
dbms_output.put_line(drop_result);
end;
/  2    3    4    5    6    7    8    9

PL/SQL procedure successfully completed.

Step 4: Verify the Baseline Has Been Removed

SQL> select * from dba_sql_plan_baselines where plan_name='SQL_PLAN_fyx86gn5gubub12827765';

no rows selected

This confirms that the SQL Plan Baseline has been removed successfully.

How DBMS_SPM.DROP_SQL_PLAN_BASELINE Works

The procedure accepts two parameters:

Parameter	Description
SQL_HANDLE	Identifies the SQL statement
PLAN_NAME	Identifies the specific execution plan

Oracle deletes only the specified baseline while leaving other baselines for the same SQL intact.

Important Considerations

Before dropping a baseline, verify:

• Another accepted baseline exists.
• The SQL performs well without it.
• The change has been tested in a non-production environment.
• The application owner has approved the change if it impacts production.

Best Practices

• Review execution plans before deleting baselines.
• Keep at least one accepted baseline for critical SQL statements.
• Perform the operation during a maintenance window for production databases.
• Monitor SQL performance after removing the baseline.

Conclusion

SQL Plan Baselines are essential for maintaining execution plan stability in Oracle databases. However, as workloads evolve, certain baselines may become obsolete or negatively impact performance.

By identifying the SQL ID, retrieving the SQL Handle, and using DBMS_SPM.DROP_SQL_PLAN_BASELINE, you can safely remove outdated execution plans while maintaining control over SQL performance.

Best Practice: Always verify the existence of alternative accepted plans and monitor execution after the baseline is removed to ensure optimal performance.