Stored outline is still the only option available on standard edition databases for achieving plan stability. On 11gR2 there's dbms_spm.MIGRATE_STORED_OUTLINE to migrate stored outlines to plan baselines but that doesn't work on standard edition.
This post list steps to change the execution plan of a SQL query using stored outlines. The environment is Standard Edition 11gR1 (11.1.0.7.13) running on RHEL 5 (64bit). The cursor_sharing is set to
similar. How cursor_sharing affect stored outline is available on
132547.1
1. Grant the user creating stored outlines following privileges
grant create any outline to asanga;
grant alter any outline to asanga;
2. Following SQL is considered the original SQL of which execution plan will be changed
SQL> select count(*) from big_table where p_id=15151;
Currently the above SQL's plan uses an index and this will be changed to use a full table scan (no consideration is given to the suitability of this change, it's merely used to illustrate how to change the execution plan using stored outlines). Generate the desired execution plan, in this case this is done using hints
SQL> select /*+ full(big_table) */ count(*) from big_table where p_id=151;
This generates two sql id as the sql text is different
SQL> select sql_id,hash_value,child_number,sql_text from v$sql where sql_text like '%big_table%';
SQL_ID HASH_VALUE CHILD_NUMBER SQL_TEXT
------------- ---------- ------------ ---------------------------------------------------------------------------
80151fvm1d9af 3860243790 0 select count(*) from big_table where p_id=:"SYS_B_0"
81920u5c0ufyb 1477262283 0 select /*+ full(big_table) */ count(*) from big_table where p_id=:"SYS_B_0"
4. Stored outlines will be created using these two plans. DBMS_OUTLN.CREATE_OUTLINE will be used as it allows to create stored outlines from existing cursors (
445126.1)
SQL> alter session set create_stored_outlines = TRUE;
SQL> exec dbms_outln.CREATE_OUTLINE(3860243790,0);
PL/SQL procedure successfully completed.
SQL> exec dbms_outln.CREATE_OUTLINE(1477262283,0);
PL/SQL procedure successfully completed.
SQL> alter session set create_stored_outlines = false;
6. This would have generated two stored outlines. Which could be viewed using user_otulines view
SQL> select name,sql_text from user_outlines;
NAME SQL_TEXT
------------------------------ ---------------------------------------------------------------------------
SYS_OUTLINE_12121716093232802 select count(*) from big_table where p_id=:"SYS_B_0"
SYS_OUTLINE_12121716094181604 select /*+ full(big_table) */ count(*) from big_table where p_id=:"SYS_B_0"
5. Give easily identifiable names to stored outline so it is easier to identify which outline is associated with which plan. In this case SYS_OUTLINE_12121716093232802 is associated with plan using the index and SYS_OUTLINE_12121716094181604 is associated with plan using full table scan.
SQL> alter outline SYS_OUTLINE_12121716093232802 rename to with_index;
Outline altered.
SQL> alter outline SYS_OUTLINE_12121716094181604 rename to full_table;
Outline altered.
SQL> select name,sql_text from user_outlines;
NAME SQL_TEXT
------------------------------ ---------------------------------------------------------------------------
WITH_INDEX select count(*) from big_table where p_id=:"SYS_B_0"
FULL_TABLE select /*+ full(big_table) */ count(*) from big_table where p_id=:"SYS_B_0"7. Enable use of stored outlines on the system.
SQL> alter system set use_stored_outlines=true
This could be done for session as well. Stored outlines are not enable by default. To enable stored outlines during system start up add a on database trigger and include the statement above inside the trigger.
create or replace trigger enable_outlines_trig
after startup on database
begin
sys.dbms_system.ksdwrt(2, 'AP-ORA-01 going to enable outline');
execute immediate('alter system set use_stored_outlines=true');
sys.dbms_system.ksdwrt(2, 'AP-ORA-01 outline enabled');
end;
/Above trigger will add entries to alert.log. Refer
560331.1 for more information.
8. Once the use of stored outline is enable it's time to check if they get used when SQL get executed. user_outlines has a column called used which will say used or noused but in this case display_cursor will be used to see if an outline is used with the SQL.
SQL> select count(*) from big_table where p_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS OUTLINE'));
SQL_ID 80151fvm1d9af, child number 0
-------------------------------------
select count(*) from big_table where p_id=:"SYS_B_0"
Plan hash value: 1795900200
-----------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| P_ID_IDX | 2 |
-----------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "BIG_TABLE"@"SEL$1" ("BIG_TABLE"."P_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P_ID"=:SYS_B_0)
Note
-----
- outline "WITH_INDEX" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelThis shows that outline associated with index plan is being used when the original sql is run.
SQL> select /*+ full(big_table) */ count(*) from big_table where p_id=2;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS OUTLINE'));
SQL_ID 81920u5c0ufyb, child number 0
-------------------------------------
select /*+ full(big_table) */ count(*) from big_table where
p_id=:"SYS_B_0"
Plan hash value: 599409829
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 |
-------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P_ID"=:SYS_B_0)
Note
-----
- outline "FULL_TABLE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelThis shows that when the SQL run with full table hint the full_table outline get used. Next step is to make the original SQL execute with plan generated for SQL with the hint.
9. Create two private outlines from the two outlines created above
SQL> create private outline myindex from with_index;
Outline created.
SQL> create private outline myfulltable from full_table;
Outline created.
SQL> select ol_name from ol$;
MYINDEX
MYFULLTABLE
These private outlines will be modified such that MYFULLTABLE outline will be associated with the MYINDEX outline's hints. Set the hint count of MYINDEX to match the hint count of MYFULLTABLE
SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='MYFULLTABLE') where ol_name='MYINDEX';
1 row updated.
Delete the MYFULLTABLE private outline record
SQL> delete from ol$ where ol_name='MYFULLTABLE';
1 row deleted.
Rename the MYINDEX outline as MYFULLTABLE
SQL> update ol$ set ol_name='MYFULLTABLE' where ol_name='MYINDEX';
1 row updated.
At the end of this there will be only one private outline and it will be associated with the original SQL text
SQL> select ol_name,sql_text from ol$;
MYFULLTABLE select count(*) from big_table where p_id=:"SYS_B_0"
Execute a commit as ol$* tables are temporary tables and refresh the private outline.
SQL> commit;
Commit complete.
SQL> execute dbms_outln_edit.refresh_private_outline('MYFULLTABLE');
PL/SQL procedure successfully completed.10. Before the private outline is made permanent it would be tested by making session to use private outlines instead of public outlines.
SQL> alter session set use_private_outlines=true;
Session altered.
SQL> select count(*) from big_table where p_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS OUTLINE'));
SQL_ID 02971r98szzu3, child number 0
-------------------------------------
select count(*) from big_table where p_id=:"SYS_B_0"
Plan hash value: 599409829
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 |
-------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P_ID"=:SYS_B_0)
Note
-----
- outline "MYFULLTABLE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelFrom the output it could be seen that private outline is being used and execution plan has changed to full table scan and the SQL text is the original SQL text.
11. Update public outline with the private outline and turn-off the use of private outlines
SQL> create or replace outline full_table from private MYFULLTABLE;
Outline created.
SQL> alter session set use_private_outlines=false;
12. At this stage there's only one public outline available (instead of two started with) and that will be associated with the full table scan plan.
SQL> select name,sql_text from user_outlines;
NAME SQL_TEXT
------------ ----------------------------------------------------
FULL_TABLE select count(*) from big_table where p_id=:"SYS_B_0"
SQL> SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name ='FULL_TABLE';
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 FIRST_ROWS(1000)
1 1 0 OPT_PARAM('optimizer_index_caching' 90)
1 1 0 OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
1 1 0 DB_VERSION('11.1.0.7')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
1 1 0 OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
1 1 0 OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
1 1 0 OPT_PARAM('optimizer_index_cost_adj' 25)
1 1 1 FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")13. Execute the original SQL text and confirm the modified outline is used
SQL> select count(*) from big_table where p_id=151;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS OUTLINE'));
SQL_ID 80151fvm1d9af, child number 0
-------------------------------------
select count(*) from big_table where p_id=:"SYS_B_0"
Plan hash value: 599409829
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 |
-------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P_ID"=:SYS_B_0)
Note
-----
- outline "FULL_TABLE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelThis concludes how to change the execution plan using stored outlines.
However using stored outlines on JDBC application has an additional challenge. This has to do with how literals are replaced with bind variables when using SQL*plus and JDBC. Running the original SQL text through JDBC application doesn't result in the stored outline being used.
SQL> select sql_id from v$sql where sql_text like '%big_table%';
4378mr12fsdrp
SQL> select * from table(dbms_xplan.display_cursor('4378mr12fsdrp',0,'ALLSTATS OUTLINE'));
SQL_ID 4378mr12fsdrp, child number 0
-------------------------------------
select count(*) from big_table where p_id=:1
Plan hash value: 1795900200
-----------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | INDEX RANGE SCAN| P_ID_IDX | 2 |
-----------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "BIG_TABLE"@"SEL$1" ("BIG_TABLE"."P_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("P_ID"=:1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelThere's no mention of stored outline being used in the note section and plan is using a index range scan instead of full table scan. More over a new sql id is also generated.
SQL> select sql_id,sql_text from v$sql where sql_text like '%big_table%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------
4378mr12fsdrp select count(*) from big_table where p_id=:1
80151fvm1d9af select count(*) from big_table where p_id=:"SYS_B_0"
Comparing the two sql text is could be seen that only difference is the text used for bind variables. In JDBC :1,:2,:3 and etc is used for bind variables while on SQL plus with cursor_sharing set to similar or force a text is used SYS_B_0,SYS_B_1 and etc. This makes the two SQL non-identical thus resulting in a new SQL id and outline not being used.
In order to outline to be used create a new outline for JDBC SQL text and associate all hints in the existing outline with it.
SQL> alter session set create_stored_outlines = TRUE;
Session altered.
SQL> exec dbms_outln.CREATE_OUTLINE(1156331253,0);
PL/SQL procedure successfully completed.
SQL> alter session set create_stored_outlines = false;
Session altered.
SQL> select name,sql_text from user_outlines;
NAME SQL_TEXT
------------------------------ ------------------------------------------------------
SYS_OUTLINE_12121716491461306 select count(*) from big_table where p_id=:1
FULL_TABLE select count(*) from big_table where p_id=:"SYS_B_0"
SQL> alter outline SYS_OUTLINE_12121716491461306 rename to jdbc_outline;
Outline altered.
SQL> create or replace private outline myfulltable from full_table;
Outline created.
SQL> create or replace private outline myjdbc from jdbc_outline;
Outline created.
SQL> select ol_name from ol$;
OL_NAME
------------------------------
MYFULLTABLE
MYJDBC
SQL> SELECT ol_name,sql_text from ol$;
OL_NAME SQL_TEXT
------------------------------ ------------------------------------------------------
MYFULLTABLE select count(*) from big_table where p_id=:"SYS_B_0"
MYJDBC select count(*) from big_table where p_id=:1
SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='MYFULLTABLE') where ol_name='MYJDBC';
1 row updated.
SQL> delete from ol$ where ol_name='MYFULLTABLE';
1 row deleted.
SQL> update ol$ set ol_name='MYFULLTABLE' where ol_name='MYJDBC';
1 row updated.
SQL> commit;
SQL> SELECT ol_name,sql_text from ol$;
OL_NAME SQL_TEXT
------------------------------ ----------------------------------------------
MYFULLTABLE select count(*) from big_table where p_id=:1
SQL> execute dbms_outln_edit.refresh_private_outline('MYFULLTABLE');
PL/SQL procedure successfully completed.
SQL> create or replace outline jdbc_outline from private MYFULLTABLE;
Outline created.
SQL> alter session set use_private_outlines=false;
Session altered.
SQL> select name,sql_text from user_outlines;
NAME SQL_TEXT
------------------------------ -----------------------------------------------------
JDBC_OUTLINE select count(*) from big_table where p_id=:1
FULL_TABLE select count(*) from big_table where p_id=:"SYS_B_0"
After the stored outline change executing the original SQL text through JDBC shows the newly created outline being used and execution plan using full table scan.
SQL> select * from table(dbms_xplan.display_cursor('4378mr12fsdrp',0,'ALLSTATS OUTLINE'));
SQL_ID 4378mr12fsdrp, child number 0
-------------------------------------
select count(*) from big_table where p_id=:1
Plan hash value: 599409829
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 |
-------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P_ID"=:1)
Note
-----
- outline "JDBC_OUTLINE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelUseful metalink notes
Using Stored Outlines
[ID 132547.1]
Stored Outline Not Working
[ID 802044.1]
Stored Outline Quick Reference
[ID 67536.1]
Session Parameter: USE_STORED_OUTLINES
[ID 68642.1]
How To Match a SQL Statement to a Stored Outline
[ID 743312.1]
Editing Stored Outlines in Oracle10g and Oracle11g
[ID 726802.1]
How to Enable USE_STORED_OUTLINES Permanently
[ID 560331.1]
How To retreive the Outline Data from a stored execution plan
[ID 736529.1]
HOW TO: Create a Stored Outline Based Upon an Existing Cursor
[ID 445126.1]
How to generate an outline with a good plan loaded into shared_pool
[ID 463288.1]
How to Edit a Stored Outline to Use the Plan from Another Stored Outline
[ID 730062.1]
How to Transfer Stored Outlines from One Database to Another (9i and above)
[ID 728647.1]
