x

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

avatar image

Leigh Riffel
154 11 10 15

(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

avatar image

Leigh Riffel
154 11 10 15

(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

avatar image

David McClanaahan
1

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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x131

asked: Apr 16, 2010 at 03:51 PM

Seen: 3628 times

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

Copyright 2016 Redgate Software. Privacy Policy