question

Leigh Riffel avatar image
Leigh Riffel asked

Bulk Delete SQL Plan Baselines

I have a development database in which I set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true with the hopes that further investigation would reveal a way to do automatic plan evolution without the tuning pack. Since I have found no way to do that I have turned off the setting and would like to delete the baselines.

administration
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leigh Riffel avatar image
Leigh Riffel answered

DBMS_SPM.DROP_SQL_PLAN_BASELINE will delete a single baseline, so I suppose I could create a loop on DBA_SQL_PLAN_BASELINES to run this procedure.

Here is the block of code I used:

set serveroutput on;
declare
   vResult Binary_Integer;
begin
   For vPlan In (SELECT distinct Plan_Name FROM dba_sql_plan_baselines) Loop
      --DBMS_OUTPUT.PUT_LINE('Deleting ' || vPLan.Plan_Name || ' - ' 
      --   || to_char(dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, 
      --         plan_name=>vPlan.Plan_Name))
      --   || '.');
      vResult := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, 
         plan_name=>vPlan.Plan_Name);

   End Loop;
end;
/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David McClanaahan avatar image
David McClanaahan answered

The DROP_SQL_PLAN_BASELINE function will drop all plans if the plan name is not specified.

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the input. The activity on this site never reached critical mass. Come on over to http://dba.stackexchange.com. It has only been around since January, but already has 50% more questions.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.