Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 12 Oct 2011 13:29:06 +0000 (UTC)
Message-ID: <pan.2011.10.12.13.29.06_at_gmail.com>

On Wed, 12 Oct 2011 00:52:34 -0700, dombrooks wrote:

> SQL> declare
> 2 sqltext clob;
> 3 spm_op pls_integer;
> 4 begin
> 5 sqltext := 'select * from t1';
> 6 spm_op :=
> 7 dbms_spm.load_plans_from_cursor_cache 8 (sql_id =>
> 'an6t9h9g5s3vh',
> 9 plan_hash_value => 646159151,
> 10 sql_text => sqltext);
> 11 end;
> 12 /
>
> PL/SQL procedure successfully completed.

Dom, can you please put the following line into the procedure and execute again:
dbms_output.put_line(spm_op||' plans loaded from the cursor cache.');

If you get 0 plans, the procedure has failed. I was checking the dba_sql_plan_baselines view and the plan wasn't loaded.

-- 
http://mgogala.byethost5.com

Received on Wed Oct 12 2011 - 08:29:06 CDT