How to drop the sql baseline in Oracle

You can drop a SQL Plan Baseline in Oracle using the DBMS_SPM.DROP_SQL_PLAN_BASELINE function.

DBMS_SPM.DROP_SQL_PLAN_BASELINE (
   sql_handle     IN VARCHAR2 := NULL,
   plan_name      IN VARCHAR2 := NULL)
RETURN PLS_INTEGER;

Syntax

To drop all the plans of the sql statement

DECLARE 
l_result INTEGER; 
BEGIN 
l_result := DBMS_SPM.drop_sql_plan_baseline(sql_handle => '&SQL_HANDLE'); 
END; 
/

To drop a particular plan of the sql statement

DECLARE
l_result INTEGER;
BEGIN
l_result := DBMS_SPM.drop_sql_plan_baseline(sql_handle => '&SQL_HANDLE', plan_name => '&PLAN_NAME');
END;
/

How to use it

Identify the SQL Plan Baseline:

First, you need to find the SQL_HANDLE and PLAN_NAME of the SQL Plan Baseline you want to drop. This can be done by querying the DBA_SQL_PLAN_BASELINES view:

SELECT sql_handle, plan_name
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%YOUR_SQL_STATEMENT%';

Drop the SQL Plan Baseline:

If you want to drop for a particular plan

DECLARE 
l_result INTEGER; 
BEGIN 
l_result := DBMS_SPM.drop_sql_plan_baseline(sql_handle => '&SQL_HANDLE', plan_name => '&PLAN_NAME'); 
END; 
/

As always, be careful when dropping SQL Plan Baselines, as it can potentially affect the performance of the corresponding SQL statements. Always monitor the performance and test the changes in a non-production environment before applying them to production.

I hope you like this article on How to drop the sql baseline in Oracle. Please do provide the feedback to improve

Related Articles

SQL Tuning Set in Oracle: Check out what is SQL Tuning Set in Oracle, How to perform loading in the SQL Tuning set, How to drop a SQL Tuning Set
how to create sql baseline in Oracle 19c: Check out how to create sql baseline in oracle 19c using cursor cache, AWR, STS. How to check if the sql baseline got created
How to create sql baseline from cursor cache in Oracle : check out How to create sql baseline from cursor cache in Oracle , syntax, examples ,detailed steps
how to create sql baseline from Sql tuning set in Oracle: check out how to create sql baseline from Sql tuning set in Oracle, syntax , detailed steps