Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 12 Oct 2011 02:12:59 +0000 (UTC)
Message-ID: <pan.2011.10.12.02.12.59_at_gmail.com>

On Wed, 12 Oct 2011 01:27:26 +0000, Mladen Gogala wrote:

> Basically, I just switched the plans for the baseline. The
> "load_plans_from_cursor_cache" function will only load the baseline and
> it accompanying plan. It will check whether the SQL_ID in the plan and
> the one from the V$SQL table are the same and will not cooperate if they
> are not the same. Once both baselines are loaded, it is possible to
> switch the plan name in the baseline.

Nope, that doesn't work either:

SQL> _at_5
Enter value for handle: SQL_89ebac4f08473939 old 5: sql_handle=>'&handle',
new 5: sql_handle=>'SQL_89ebac4f08473939', Enter value for orig_name: SQL_PLAN_1650WMVPP09JNC74815FA old 6: plan_name=>'&orig_name',
new 6: plan_name=>'SQL_PLAN_1650WMVPP09JNC74815FA', Enter value for new_name: TEST_1_FULL
old 8: attribute_value=>'&new_name');
new 8: attribute_value=>'TEST_1_FULL'); declare
*
ERROR at line 1:

ORA-38142: SQL plan baseline named TEST_1_FULL already exists
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 4

Elapsed: 00:00:00.01
SQL> Basically, it is not possible to associate plan from one statement with another. I tested it and the article from oracle blog is incorrect. I am 100% certain.

-- 
http://mgogala.byethost5.com

Received on Tue Oct 11 2011 - 21:12:59 CDT