Re: DBMS_SPM.LOAD_FROM_CURSOR_CACHE
Date: Wed, 12 Oct 2011 16:19:19 +0000 (UTC)
Message-ID: <pan.2011.10.12.16.19.19_at_gmail.com>
On Wed, 12 Oct 2011 07:10:42 -0700, dombrooks wrote:
> 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
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
| Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time
|
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | INDEX RANGE SCAN| BIG_TABLE_FCHAR | 2 | 2 | 1
(0)| 00:00
: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
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 66 (100)| | | 1 | SORT AGGREGATE | | 1 | 1 | | | |* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 | 2 | 66 (8)|00:00:01 |
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_cache(
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
| Id | Operation | Name | Rows | Bytes | Cost (% CPU)| Time
|
| 0 | SELECT STATEMENT | | 1 | 1 | 1
(0)| 00:00
:01 |
| 1 | SORT AGGREGATE | | 1 | 1 | | |
|* 2 | INDEX RANGE SCAN| BIG_TABLE_FCHAR | 2 | 2 | 1
(0)| 00:00
: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> _at_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_cache(
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_cache(
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.comReceived on Wed Oct 12 2011 - 11:19:19 CDT