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

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

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

No need to add the dbms_output.

You can see from the dbms_xplan output in my demo that it worked - the
select wasn't using a FTS and after the load is reported as using the
baselined plan from the other statement.

If I do add the output then then I get "1 plans loaded from the cursor
cache'.

I've uploaded an end-to-end test + output there that works in
11.2.0.2.

Can you post something similar showing it not working?

From what you posted previously, and as explained in my post above,
what you were doing previously was wrong - you can't call
load_plans_from_cursor_cache with the sql_id of a target statement and
the plan_hash_value of a source statement. That's not how it works.

Cheers,
Dominic

On Wed, 12 Oct 2011 07:10:42 -0700, dombrooks wrote:

Dom, here is the full transcript of my session:

The first statement uses an index. SQL_ID=6tm6vjxx9wwp9


SQL> select count(*) from big_table where first_char='Z';

COUNT(*)
----------
2

Elapsed: 00:00:00.21
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 6tm6vjxx9wwp9, child number 0
-------------------------------------
select count(*) from big_table where first_char='Z'

Plan hash value: 889699537

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

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

:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("FIRST_CHAR"='Z')


19 rows selected.

Elapsed: 00:00:00.70


The second statement is hinted with the "full" hint. It uses the full
table scan. The plan hash value is: 599409829

SQL> select /*+ full(big_table) */ count(*) from big_table where
first_char='Z';

COUNT(*)
----------
2

Elapsed: 00:00:00.13
SQL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9h20pt9r130ck, child number 0
-------------------------------------
select /*+ full(big_table) */ count(*) from big_table where
first_char='Z'

Plan hash value: 599409829

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("FIRST_CHAR"='Z')


20 rows selected.

Elapsed: 00:00:00.08
SQL> set serveroutput on

Next, the goal of the exercise: use the SQL_ID and the text of the first
statement and the plan hash value of the second(hinted) statement. It
obviously doesn't work.

declare
v_sql_text CLOB :=
'select count(*) from big_table where first_char=''Z''';
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,
sql_text => v_sql_text);
dbms_output.put_line('Loaded '||plans_loaded||' plans.');
end;
14 /
Loaded 0 plans.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL>

The plan was not loaded. When I search dba_sql_plan_baselines for the
statements containing the string 'count(*) from big_table' I get this:


SQL_HANDLE PLAN_NAME
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SQL_de2fd4b7f0ff1d09 SQL_PLAN_dwbynqzsgy7895b032e31
select count(*) from big_table where first_char='Z'

SQL_e30e2e25202b6b42 SQL_PLAN_f63jf4nh2quu2c74815fa
select /*+ full(big_table) */ count(*) from big_table where first_char='Z'


Elapsed: 00:00:00.02

Plan for the first baseline looks like this:
LAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_de2fd4b7f0ff1d09
SQL text: select count(*) from big_table where first_char='Z'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_dwbynqzsgy7895b032e31 Plan id: 1526935089
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 889699537

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

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

:01 |

:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("FIRST_CHAR"='Z')

25 rows selected.

Elapsed: 00:00:00.27

If I delete the first baseline, result stays the same:
SQL> @2
Enter value for handle: SQL_de2fd4b7f0ff1d09
old 4: dltd:=dbms_spm.drop_sql_plan_baseline(sql_handle => '&handle',
new 4: dltd:=dbms_spm.drop_sql_plan_baseline(sql_handle =>
'SQL_de2fd4b7f0ff1d09',
Enter value for plan: SQL_PLAN_dwbynqzsgy7895b032e31
old 5: plan_name=>'&plan');
new 5:
plan_name=>'SQL_PLAN_dwbynqzsgy7895b032e31');
1 plans deleted.

PL/SQL procedure successfully completed.

SQL> declare
2 v_sql_text CLOB :=
3 'select count(*) from big_table where first_char=''Z''';
4 v_sql_id varchar2(13) := '6tm6vjxx9wwp9';
5 v_hash number := 599409829;
6 plans_loaded number;
7 begin
8 plans_loaded:=dbms_spm.load_plans_from_cursor_cach e(
9 sql_id => v_sql_id,
10 plan_hash_value=>v_hash,
11 sql_text => v_sql_text);
12 dbms_output.put_line('Loaded '||plans_loaded||' plans.');
13 end;
14 /
Loaded 0 plans.

PL/SQL procedure successfully completed.

If I change the plan hash value, to the value belonging to the same
statement, the baseline is created without any issues:
SQL> declare
2 v_sql_text CLOB :=
3 'select count(*) from big_table where first_char=''Z''';
4 v_sql_id varchar2(13) := '6tm6vjxx9wwp9';
5 v_hash number := 889699537;
6 plans_loaded number;
7 begin
8 plans_loaded:=dbms_spm.load_plans_from_cursor_cach e(
9 sql_id => v_sql_id,
10 plan_hash_value=>v_hash,
11 sql_text => v_sql_text);
12 dbms_output.put_line('Loaded '||plans_loaded||' plans.');
13 end;
14 /
Loaded 1 plans.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

The version is 11.2.0.3 on Linux x86. If there is a mistake, I don't see
it. I exercised this 3 times yesterday and the results are consistent.
This is a definite change in behavior. Oracle 11XE doesn't have baselines
so I couldn't test on my 2nd machine.


--
http://mgogala.byethost5.com

There's a mistake here.
I believe your intention is to transfer the plan - 599409829 - of the
full hinted statement - 9h20pt9r130ck - to the unhinted statement.
So in the call to dbms_spm, you need:
1. the sql text of the target statement. nothing else. not the sql id.
2. the sql id of the source statement, the hinted one -
9h20pt9r130ck.
3. the plan hash of the source statement, the hinted one - 599409829

This is where you are going wrong, I believe.

As I tried to explain in a previous post, the internal mechanism takes
the metadata and outline hints from v$sql_plan - i.e it does a look up
on sql_id := :supplied_ sql_id and plan_hash_value
= :supplied_hash_value. These have to be BOTH from the statement/plan
you want to take from.

For the target statement, you don't need anything else but the sql
text.
No sql id. Nothing.
Because the sql text is run through something like
dbms_sqltune.sqltext_to_signature.
Signature is the key for baselines - no sql id, no hash value, etc.


Cheers,
Dominic

P.S. I provided a complete end-to-end demo in my post above that
worked.
Wouldn't the easiest thing to have done be to run that verbatim and
show that it did not work?

On Wed, 12 Oct 2011 12:40:02 -0700, dombrooks wrote:


It works. Thanks for helping me out with that one. I was using SQL_ID and
text for the target statement and plan for the source (hinted) statement.


SQL> declare
2 v_sql_text CLOB :=
3 'select count(*) from big_table where first_char=''Z''';
4 v_sql_id varchar2(13) := '9h20pt9r130ck';
5 v_hash number := 599409829;
6 plans_loaded number;
7 begin
8 plans_loaded:=dbms_spm.load_plans_from_cursor_cach e(
9 sql_id => v_sql_id,
10 plan_hash_value=>v_hash,
11 sql_text => v_sql_text);
12 dbms_output.put_line('Loaded '||plans_loaded||' plans.');
13 end;
14 /
Loaded 1 plans.

PL/SQL procedure successfully completed.

Here is what the dbms_xplan,display_sql_plan_baseline says:


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_de2fd4b7f0ff1d09
SQL text: select count(*) from big_table where first_char='Z'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_dwbynqzsgy789c74815fa Plan id: 3343390202
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 599409829

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("FIRST_CHAR"='Z')

25 rows selected.

Elapsed: 00:00:00.08
SQL>

Subsequent execution confirms that the right baseline is used:
SQL> select count(*) from big_table where first_char='Z';

COUNT(*)
----------
2

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 599409829

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("FIRST_CHAR"='Z')

Note
-----
- SQL plan baseline "SQL_PLAN_dwbynqzsgy789c74815fa" used for this
statement

SQL>


Thanks again for straightening me up. OK, now I know the trick.

--
http://mgogala.byethost5.com

On Wed, 12 Oct 2011 12:46:13 -0700, dombrooks wrote:

Some people never learn. Thanks for helping me out. The mistake was
conceptual.



--
http://mgogala.byethost5.com

On Wed, 12 Oct 2011 20:53:08 +0000, Mladen Gogala wrote:

BTW, this opens a world of tuning possibilities. One can hint an
execution plan, without actually changing the source code and wait for
Oracle to come up with a better plan once in the future. This is huge!



--
http://mgogala.byethost5.com