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.

more ▼

asked Apr 16, 2010 at 03:51 PM in Default

Leigh Riffel gravatar image

Leigh Riffel
154 8 10 12

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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; /
more ▼

answered Apr 16, 2010 at 03:56 PM

Leigh Riffel gravatar image

Leigh Riffel
154 8 10 12

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 20, 2011 at 04:46 PM

David McClanaahan gravatar image

David McClanaahan

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.
Apr 20, 2011 at 05:27 PM Leigh Riffel
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 16, 2010 at 03:51 PM

Seen: 2949 times

Last Updated: Apr 16, 2010 at 04:08 PM