Drop the SQL PLAN BASELINE

Drop the SQL_BASELINES

This post explains to Drop the SQL plan baselines of the SQL.

set lines 200 pages 2000
column sql_handle for a50
col plan_name for a40
col SQLTEXT_Trimmed for a60
select  sql_handle,plan_name,created, substr(sql_text,0,50) SQLTEXT_Trimmed,accepted, fixed, enabled 
from dba_sql_plan_baselines order by created desc;

From above command you can find the details of the baseline like name of the Baselines , SQL PLAN handle and PLAN name. Note: these plan name and SQL_HANDLE will be different from the SQLID and PLAN_HASH_VLAUE. To find out the sql_handle you can query the details from V$SQL for specific SQLID.

Now till now we have 2 things handy -

1.  If we have the SQLID , then from V$SQL we have the SQL_PLAN_HANDLE name.
2. From the SQL_PLAN_HANDLE, there can be multiple PLAN_NAMES mentioned in the DBA_SQL_PLAN_BASELINES view.

Now we have the package available in oracle to drop the SQL PLAN BASELINE -

 declare 
 drop_result pls_integer;
 begin 
 drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE( 
 sql_handle => '&SQL_HANDLE_NAME',  
 plan_name => '&SQL_BASELINE_PLAN_NAME'); 
 dbms_output.put_line(drop_result);    
 end; 
/