Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

> Next, the goal of the exercise: use the SQL_ID and the text of the first
> statement and the plan hash value of the second(hinted) statement. It
> obviously doesn't work.
>
> declare
> v_sql_text CLOB :=
>     'select count(*) from big_table where first_char=''Z''';
> 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,
>         sql_text => v_sql_text);
>     dbms_output.put_line('Loaded '||plans_loaded||' plans.');
> end;
>  14  /

There's a mistake here.
I believe your intention is to transfer the plan - 599409829 - of the full hinted statement - 9h20pt9r130ck - to the unhinted statement. So in the call to dbms_spm, you need:
1. the sql text of the target statement. nothing else. not the sql id. 2. the sql id of the source statement, the hinted one - 9h20pt9r130ck.
3. the plan hash of the source statement, the hinted one - 599409829

This is where you are going wrong, I believe.

As I tried to explain in a previous post, the internal mechanism takes the metadata and outline hints from v$sql_plan - i.e it does a look up on sql_id := :supplied_ sql_id and plan_hash_value = :supplied_hash_value. These have to be BOTH from the statement/plan you want to take from.

For the target statement, you don't need anything else but the sql text.
No sql id. Nothing.
Because the sql text is run through something like dbms_sqltune.sqltext_to_signature.
Signature is the key for baselines - no sql id, no hash value, etc.

Cheers,
Dominic Received on Wed Oct 12 2011 - 14:40:02 CDT