Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE
Date: Wed, 12 Oct 2011 03:31:58 -0700 (PDT)
Message-ID: <9efb754a-ae91-4779-9576-825e77f1bb66_at_n15g2000vbn.googlegroups.com>
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.
Cheers,
Dominic
Received on Wed Oct 12 2011 - 05:31:58 CDT