Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 12 Oct 2011 01:27:26 +0000 (UTC)
Message-ID: <pan.2011.10.12.01.27.26_at_gmail.com>

On Tue, 11 Oct 2011 23:14:07 +0000, Mladen Gogala wrote:

> On Tue, 11 Oct 2011 13:19:27 -0700, Randolf Geist wrote:
>

>> I don't think that you're following the exact procedure outlined - the
>> point is that you need to specify either a SQL_HANDLE of an already

>
> Randolf, I followed the exact procedure, to no avail. The procedure will
> simply not associate the plan generated by the different statement with
> the given statement id.

This definitely doesn't work. Fortunately, I found the trick that does work. You need to create baselines for both versions of the statement and then switch the plans in the baseline using alter_sql_plan_baseline. This works:

declare
plans number;
begin
plans:=dbms_spm.alter_sql_plan_baseline( sql_handle=>'SQL_de2fd4b7f0ff1d09',
plan_name=>'SQL_PLAN_dwbynqzsgy789c74815fa', attribute_name=>'plan_name',
attribute_value=>'SQL_PLAN_f63jf4nh2quu2c74815fa'); dbms_output.put_line('Plans changed:'||plans); end;
/

Basically, I just switched the plans for the baseline. The "load_plans_from_cursor_cache" function will only load the baseline and it accompanying plan. It will check whether the SQL_ID in the plan and the one from the V$SQL table are the same and will not cooperate if they are not the same. Once both baselines are loaded, it is possible to switch the plan name in the baseline.

-- 
http://mgogala.byethost5.com

Received on Tue Oct 11 2011 - 20:27:26 CDT