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.