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 🙂
Learning is not a spectator sport 
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 🙂
Log a call with Support about truncating the 3 underlying tables. As long as they give their approval, you should be good to proceed
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
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.
I know it would spoil your nice use of accompanying illustration, but I’d point out that vanilla is not plain. In fact it’s quite an exotic spice, a pretty expensive one at around £1 a gramme for organic Madagascar pods, and of course the best flavour for icecream
harsh but fair 🙂