Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

From: Randolf Geist <mahrah_at_web.de>
Date: Tue, 11 Oct 2011 13:19:27 -0700 (PDT)
Message-ID: <cd65f120-b625-4e8a-a0f2-8c19a8b2190b_at_db5g2000vbb.googlegroups.com>

On 11 Okt., 20:17, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> In 11.2.0.3, DBMS_SPM.LOAD_FROM_CURSOR_CACHE refuse to load if the SQL_ID
> of the statement for which I'm loading the plan and the plan that I'm
> trying to load are not the same:
>
> declare
> v_sql_text CLOB;
> # v_sql_id varchar2(13) := '0wnf3cqjjuw0b';
> v_sql_id varchar2(13) := '6tm6vjxx9wwp9';
> v_hash number := 599409829;
> plans_loaded number;
> begin
> plans_loaded:=dbms_spm.load_plans_from_cursor_cache(
> sql_id=>v_sql_id,
> plan_hash_value=>v_hash);
> dbms_output.put_line('Loaded '||plans_loaded||' plans.');
> end;
> /
>
> Basically, I'm trying to hint a SQL statement and loaded a hinted plan as
> described here:http://tinyurl.com/4y28t92
> On 11.2.0.3, this doesn't work. Any ideas? According to the blog, this
> should be possible. Is this a new feature of Oracle 11.2.0.3?
>
> --http://mgogala.byethost5.com

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 existing baseline or a SQL_TEXT of the unhinted SQL in addition to which you want to apply the plan of the hinted query. So the SQL_ID and PLAN_HASH_VALUE refer to the hinted query but the SQL_HANDLE or SQL_TEXT refer to the unhinted version of the query.

Look carefully at the provided examples.

Hope this helps,
Randolf Received on Tue Oct 11 2011 - 15:19:27 CDT