From: Randolf Geist <>
Date: Tue, 11 Oct 2011 13:19:27 -0700 (PDT)
Message-ID: <>

On 11 Okt., 20:17, Mladen Gogala <> wrote:
> In, 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:
> On, this doesn't work. Any ideas? According to the blog, this
> should be possible. Is this a new feature of Oracle
> --

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

Original text of this message