From: dombrooks <>
Date: Wed, 12 Oct 2011 03:31:58 -0700 (PDT)
Message-ID: <>

Hi Mladen,

As Randolf pointed out and as my demo illustrated, it does look like you're making a mistake in your usage here.

Broadly speaking, the mechanism behind
dbms_spm.load_plans_from_cursor_cache is to a) get the information - some metadata plus the outline data from v $sql_plan.other_xml - from the shared pool about that sql_id and that plan_hash_value - this has to be the id AND plan of the SOURCE statement and it has to be in the cache
b) get the exact matching signature of the TARGET statement from the sqltext argument.
c) insert the spm mappings between the plan/metadata for the SOURCE statement against the TARGET signature.

So, if you try to use the sql_id of the TARGET statement and the plan hash value of SOURCE statement, there isn't anything in v$sql_plan which can be extracted.

Dominic Received on Wed Oct 12 2011 - 05:31:58 CDT

Original text of this message