Assuming one has a SQL cursor on a database, for which exist many plans and one wants to get rid of all of them but one here is the quickest way I find to do that:
declare
n number;
begin
n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'gzwhs3zc68ju5', plan_hash_value=>156135624, fixed =>'NO', enabled=>'YES');
end;
/
- sql_id is an identifier of a SQL we want to create SQL plan baseline for
- plan_hash_value is a hash value for the plan wa want to use
- fixed is an attribute, which provides some priority for fixed plans over non-fixed and disables automatic SQL tuning to implement new findings at once (findings are stored as non-fixed plan baselines) - I would say this is reasonable to use it, though in the example it is set to NO
- enabled is self-explained
