How To Drop SQL Baselines In Oracle
1.Get the sql_handle and sql_baseline name of the sql_id:
SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id=’&SQL_ID’)
SQL_HANDLE PLAN_NAME
——————————————— —————————————————-
SQL_123b2be250f1ffca SQL_2cftbwa0h3zxu06dab6d6
2. Drop the baseline:
SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name=’SQL_2cftbwa0h3zxu06dab6d6′;
SQL_HANDLE PLAN_NAME
——————————————— ——————————————————————-
SQL_123b2be250f1ffca SQL_2cftbwa0h3zxu06dab6d6
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => ‘SQL_123b2be250f1ffca’,
plan_name => ‘SQL_2cftbwa0h3zxu06dab6d6′);
dbms_output.put_line(drop_result);
end;
/
PL/SQL procedure successfully completed.
SQL> SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name=’SQL_2cftbwa0h3zxu06dab6d6’;
An sql_handle can have multiple sql baselines tagged, So if you want to drop all the sql baselines of that handle, then drop the sql handle itself.no rows selected
declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => ‘SQL_123b2be250f1ffca’);
dbms_output.put_line(drop_result);
end;
/
