In 11.2.0.3, DBMS_SPM.LOAD_FROM_CURSOR_CACHE refuse to load if the SQL_ID
of the statement for which I'm loading the plan and the plan that I'm
trying to load are not the same:

declare
v_sql_text CLOB;
# v_sql_id varchar2(13) := '0wnf3cqjjuw0b';
v_sql_id varchar2(13) := '6tm6vjxx9wwp9';
v_hash number := 599409829;
plans_loaded number;
begin
plans_loaded:=dbms_spm.load_plans_from_cursor_cach e(
sql_id=>v_sql_id,
plan_hash_value=>v_hash);
dbms_output.put_line('Loaded '||plans_loaded||' plans.');
end;
/

Basically, I'm trying to hint a SQL statement and loaded a hinted plan as
described here: http://tinyurl.com/4y28t92
On 11.2.0.3, this doesn't work. Any ideas? According to the blog, this
should be possible. Is this a new feature of Oracle 11.2.0.3?

--
http://mgogala.byethost5.com

On Tue, 11 Oct 2011 18:17:42 +0000, Mladen Gogala wrote:

If I switch the values, everything works. There is no error returned, it
simply returns 0 for the number of loaded plans.



--
http://mgogala.byethost5.com

On 11 Okt., 20:17, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
I don't think that you're following the exact procedure outlined - the
point is that you need to specify either a SQL_HANDLE of an already
existing baseline or a SQL_TEXT of the unhinted SQL in addition to
which you want to apply the plan of the hinted query. So the SQL_ID
and PLAN_HASH_VALUE refer to the hinted query but the SQL_HANDLE or
SQL_TEXT refer to the unhinted version of the query.

Look carefully at the provided examples.

Hope this helps,
Randolf

On Tue, 11 Oct 2011 13:19:27 -0700, Randolf Geist wrote:

Did you try it? Does it work?



--
http://mgogala.byethost5.com

On Tue, 11 Oct 2011 13:19:27 -0700, Randolf Geist wrote:

Randolf, I followed the exact procedure, to no avail. The procedure will
simply not associate the plan generated by the different statement with
the given statement id.



--
http://mgogala.byethost5.com

On Tue, 11 Oct 2011 23:14:07 +0000, Mladen Gogala wrote:

This definitely doesn't work. Fortunately, I found the trick that does
work. You need to create baselines for both versions of the statement and
then switch the plans in the baseline using alter_sql_plan_baseline. This
works:

declare
plans number;
begin
plans:=dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SQL_de2fd4b7f0ff1d09',
plan_name=>'SQL_PLAN_dwbynqzsgy789c74815fa',
attribute_name=>'plan_name',
attribute_value=>'SQL_PLAN_f63jf4nh2quu2c74815fa') ;
dbms_output.put_line('Plans changed:'||plans);
end;
/


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.


--
http://mgogala.byethost5.com

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


Nope, that doesn't work either:

SQL> @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

On Wed, 12 Oct 2011 02:12:59 +0000, Mladen Gogala wrote:

I believe that this is deliberate. Such a possibility would make
DBMS_SQLTUNE completely obsolete.



--
http://mgogala.byethost5.com

I don't have 11.2.0.3 yet, but can confirm that the approach works in
11.2.0.2.

See demo below.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>
SQL> create table t1
2 (col1 number
3 ,constraint pk_t1 primary key (col1));

Table created.

SQL>
SQL> select /*+ index(t1 pk_t1) */
2 *
3 from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID an6t9h9g5s3vh, child number 0
-------------------------------------
select /*+ index(t1 pk_t1) */ * from t1

Plan hash value: 646159151

--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=4)


17 rows selected.

SQL>
SQL> select * from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=4)


17 rows selected.

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.

SQL>
SQL> select * from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 27uhu2q2xuu7r, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no
data found

NOTE: cannot fetch plan for SQL_ID: 27uhu2q2xuu7r, CHILD_NUMBER: 1
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache
(check v$sql_plan)


8 rows selected.

SQL> select * from t1;

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 27uhu2q2xuu7r, child number 1
-------------------------------------
select * from t1

Plan hash value: 646159151

--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------

Note
-----
- SQL plan baseline SQL_PLAN_6x6k5dhdcczn6690169bf used for this
statement


17 rows selected.

SQL>

On Oct 11, 6:31*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
All I can see is that what you've posted so far doesn't correspond to
the provided examples, because your posted call to
LOAD_PLANS_FROM_CURSOR_CACHE misses the additional parameter explained
previously. How about providing a complete walkthrough of what you've
tried so far?

See Dom's example how it is supposed to work in versions below
11.2.0.3. I haven't tried it yet in 11.2.0.3.

Hope this helps,
Randolf