Drop all baselines

Posted by

Just a quick tip today on doing a “reset” on your optimizer environment. SQL Plan Baselines are an awesome tool to ensure plan stability in your database, and generally, when you are upgrading from one release to the next, they are the perfect tool to make sure that you don’t get any performance regressions because you are locking the existing plans in place.

But sometimes we want the opposite during our development and/or test cycles, namely, we want to see what the database will do in a “vanilla” state. This can be especially relevant if you have inherited a system which is not performing well, and you are upgrading to take advantage of the various improvements that come to the optimizer with each new version.

So here’s a quick script that will remove any existing baselines, so your optimizer is working solely off the available system and object statistics.



declare
  l_cnt  pls_integer;
begin
  for i in ( select distinct plan_name from dba_sql_plan_baselines )
  loop
    l_cnt := 
        dbms_spm.drop_sql_plan_baseline (
            sql_handle => null,
            plan_name  => i.plan_name);
    
  end loop;  
end;
/

Obviously (as always) use with caution and care.

Footnote: This is different from disabling a baseline.  Check Nigel’s post for more details.

And the image? Well … it is vanilla 🙂

4 comments

  1. if you have 3 million baselines (wrongly setting optimizer_capture_sql_plan_baselines=true from day 1) – is there a quicker way of doing this other than row-by-row call to drop_sql_plan_baseline ? I won’t do this, but feel kind of tempted to DELETE directly from the dictionary tables 🙂

  2. Do you suggest we do the same with sql profiles?
    Or is there a way to verify the integrity of an sql profile especially as the data volume increases and the stats are updated

    1. To be honest, I’m not a fan of profiles because of their rigidity, ie, they don’t “react” to changes in data. But you could do something like dbms_sqltune.alter_sql_profile to disable it, but as a longer term strategy, if I have profiles that currently work as I want, I would then capture a sql plan baseline and drop the profile. That way I keep the same plan and move away from profiles toward sql plan management which is the better option.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.