- The most important information that you need to be aware of if you plan to use SQL Profiles is that you need an Enterprise Edition + Diagnostic Pack + Tuning Pack license. If you don't have these licenses you are not allowed to use SQL Profiles, or the other way around Oracle can claim you need to pay these licenses if you're going to use SQL Profiles.
Therefore if you're already on Oracle 11g you might want to use SQL Baselines instead for the same purpose -
Update August 2012: SQL Baselines are only available with Enterprise Edition.
Jonathan Lewis for example recently wrote a short note on how to apply a baseline from a hinted statement to a non-hinted, which is what you usually want to achieve when dealing with third-party applications where you can't modify the source code.
More details about SQL Baselines can be found in the documentation, Kerry Osborne for example has some more examples and quirks he found summarized in his post about SQL Baselines.
If you're not yet on 11g and therefore can't use SQL Baselines you can still use Stored Outlines instead of SQL Profiles if you don't have the licences mentioned - as shown below the DBMS_OUTLN.CREATE_OUTLINE procedure unfortunately doesn't always work as expected. Therefore you can try to "hack" a Stored Outline as for example demonstrated by Charles Hooper here. His post also contains references to other sources on My Oracle Support and by Jonathan Lewis that describe that technique in more detail.
- I'm a bit puzzled that this post is such popular. I spend a significant amount of my time on performance related issues but I rarely resort to the techniques described here and the other mentioned posts about "Plan Stability". So I'm a bit curious and would like to encourage readers to leave a comment here why they think they need to use "Plan Stability" - there are usually a lot of others options I would evaluate first before thinking about using some kind of Plan Stability.
Original post: If you have the need for plan stability - that is telling the database to use a particular execution plan no matter what the optimizer thinks otherwise - then you might be in the situation that the "good" execution plan is already available in the shared pool or in the AWR, so it would be handy if you could simply tell Oracle to use that particular execution plan to create a Stored Outline.
Note that in 11g this is all possible using the new SQL Plan Management framework (SPM), but that is not available in 10g, so we need to think differently.
In 10g the DBMS_OUTLN package has been enhanced with the CREATE_OUTLINE procedure to create an outline from an existing child cursor in the shared pool.
Please note that in releases prior to 10.2.0.4 there was a severe bug that caused your session to crash when using DBMS_OUTLN.CREATE_OUTLINE (Bug 5454975 which has been fixed in 10.2.0.4). The workaround is to enable the creation of stored outlines by issuing "alter session set create_stored_outlines = true;" before using DBMS_OUTLN.CREATE_OUTLINE. For more information see the Metalink Notes 463288.1 and 445126.1.
Note that from 10g on the hints required to create an outline are stored as part of the plan table in the OTHER_XML column as part of the XML detail information.
You can use the ADVANCED or OUTLINE option of the DBMS_XPLAN.DISPLAY* functions to display that OUTLINE information. For more information see e.g. here.
So let's try DBMS_OUTLN.CREATE_OUTLINE in 10.2.0.4:
SQL>
SQL> drop table t_fetch_first_rows purge;
Table dropped.
SQL>
SQL> create table t_fetch_first_rows (
2 id number not null,
3 name varchar2(30) not null,
4 type varchar2(30) not null,
5 measure number
6 );
Table created.
SQL>
SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id);
Index created.
SQL>
SQL> -- create an empty table
SQL> -- and gather statistics on it
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> -- now put in some data
SQL> insert /*+ append */ into t_fetch_first_rows (
2 id,
3 name,
4 type,
5 measure)
6 select object_id, object_name, object_type, object_id as measure
7 from all_objects, (select level as id from dual connect by level <= 1000) dup
8 where object_type in ('VIEW', 'SCHEDULE')
9 and rownum <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- This is going to use
SQL> -- the wrong plan
SQL> -- that we - only for demonstration purposes -
SQL> -- attempt to keep now
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );
SUM(MEASURE) COUNT(*)
------------ ----------
900000 1000
SQL>
SQL> -- uses index
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID c2trqja6wh561, child number 0
-------------------------------------
select sum(measure), count(*) from ( select * from t_fetch_first_rows where type
= 'VIEW' order by id )
Plan hash value: 1903859112
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 43 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 43 | 0 (0)|
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)|
------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2"
("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='VIEW')
41 rows selected.
SQL>
SQL> -- now gather statistics again
SQL> -- on table with data
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> -- now the EXPLAIN PLAN tells us
SQL> -- full table scan
SQL> select * from table(dbms_xplan.display(null, null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2")
OUTLINE(@"SEL$2")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
MERGE(@"SEL$73523A42")
OUTLINE_LEAF(@"SEL$51F12574")
ALL_ROWS
OPT_PARAM('query_rewrite_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
33 rows selected.
SQL>
SQL> -- These are the hints
SQL> -- stored in the child cursor
SQL> -- in the shared pool
SQL> -- It clearly shows an index access
SQL> select
2 substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 hash_value = 2378699969
12 and child_number = 0
13 and other_xml is not null
14 )
15 ) d;
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRS
11 rows selected.
SQL>
SQL> -- Create the outline based on that cursor
SQL> exec dbms_outln.create_outline(2378699969, 0, 'TEST')
PL/SQL procedure successfully completed.
SQL>
SQL> -- Oops, where is my index scan gone?
SQL> select substr(hint, 1, 100) as hint from user_outline_hints;
HINT
--------------------------------------------------------------------------------
FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2")
OUTLINE(@"SEL$2")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
MERGE(@"SEL$73523A42")
OUTLINE_LEAF(@"SEL$51F12574")
ALL_ROWS
OPT_PARAM('query_rewrite_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
11 rows selected.
SQL>
SQL> -- Use the outline
SQL> alter session set use_stored_outlines = TEST;
Session altered.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> -- Uses outline (see Note section)
SQL> -- but full table scan
SQL> -- So that didn't work as expected
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
Note
-----
- outline "SYS_OUTLINE_09032900314557403" used for this statement
18 rows selected.
SQL>
SQL> alter session set use_stored_outlines = false;
Session altered.
SQL>
SQL> -- drop the outline
SQL> declare
2 outline_name varchar2(30);
3 begin
4 select
5 name
6 into
7 outline_name
8 from
9 user_outlines
10 where
11 category = 'TEST';
12
13 execute immediate 'drop outline ' || outline_name;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- This is the plan
SQL> -- we get based on the present statistics
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
14 rows selected.
SQL>
SQL> spool off
So that didn't work as expected. Although we were able to create an outline from the child cursor, it obviously didn't use the plan associated with the child cursor. Tracing the session didn't reveal why the CREATE_OUTLINE didn't use the outline information available from the shared pool.
Running the same test case in a slightly different order so that the outline is created before the statistics change corroborates the theory that the DBMS_OUTLN.CREATE_OUTLINE procedure might take the SQL from the cursor and internally execute an CREATE OUTLINE ... ON ..., and for whatever reason doesn't use the already available outline information.
SQL>
SQL> drop table t_fetch_first_rows purge;
Table dropped.
SQL>
SQL> create table t_fetch_first_rows (
2 id number not null,
3 name varchar2(30) not null,
4 type varchar2(30) not null,
5 measure number
6 );
Table created.
SQL>
SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id);
Index created.
SQL>
SQL> -- create an empty table
SQL> -- and gather statistics on it
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> -- now put in some data
SQL> insert /*+ append */ into t_fetch_first_rows (
2 id,
3 name,
4 type,
5 measure)
6 select object_id, object_name, object_type, object_id as measure
7 from all_objects, (select level as id from dual connect by level <= 1000) dup
8 where object_type in ('VIEW', 'SCHEDULE')
9 and rownum <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- This is going to use
SQL> -- the wrong plan
SQL> -- that we - only for demonstration purposes -
SQL> -- attempt to keep now
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );
SUM(MEASURE) COUNT(*)
------------ ----------
900000 1000
SQL>
SQL> -- uses index
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID c2trqja6wh561, child number 0
-------------------------------------
select sum(measure), count(*) from ( select * from t_fetch_first_rows where type
= 'VIEW' order by id )
Plan hash value: 1903859112
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 43 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 43 | 0 (0)|
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)|
------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2"
("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='VIEW')
41 rows selected.
SQL>
SQL> -- Create the outline based on that cursor
SQL> exec dbms_outln.create_outline(2378699969, 0, 'TEST')
PL/SQL procedure successfully completed.
SQL>
SQL> -- Now we have the index scan in the outline
SQL> select substr(hint, 1, 100) as hint from user_outline_hints;
HINT
--------------------------------------------------------------------------------
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS".
OUTLINE(@"SEL$2")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
MERGE(@"SEL$73523A42")
OUTLINE_LEAF(@"SEL$51F12574")
ALL_ROWS
OPT_PARAM('query_rewrite_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
11 rows selected.
SQL>
SQL> -- now gather statistics again
SQL> -- on table with data
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> -- now the EXPLAIN PLAN tells us
SQL> -- full table scan
SQL> select * from table(dbms_xplan.display(null, null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2")
OUTLINE(@"SEL$2")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
MERGE(@"SEL$73523A42")
OUTLINE_LEAF(@"SEL$51F12574")
ALL_ROWS
OPT_PARAM('query_rewrite_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
33 rows selected.
SQL>
SQL> -- These are the hints
SQL> -- stored in the child cursor
SQL> -- in the shared pool
SQL> -- It clearly shows an index access
SQL> select
2 substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 hash_value = 2378699969
12 and child_number = 0
13 and other_xml is not null
14 )
15 ) d;
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRS
11 rows selected.
SQL>
SQL> -- Use the outline
SQL> alter session set use_stored_outlines = TEST;
Session altered.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> -- Uses outline (see Note section)
SQL> -- this time correctly
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1903859112
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1000 | 11000 | 9 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1000 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='VIEW')
Note
-----
- outline "SYS_OUTLINE_09032900320095604" used for this statement
19 rows selected.
SQL>
SQL> alter session set use_stored_outlines = false;
Session altered.
SQL>
SQL> -- drop the outline
SQL> declare
2 outline_name varchar2(30);
3 begin
4 select
5 name
6 into
7 outline_name
8 from
9 user_outlines
10 where
11 category = 'TEST';
12
13 execute immediate 'drop outline ' || outline_name;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- This is the plan
SQL> -- we get based on the present statistics
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
14 rows selected.
SQL>
SQL> spool off
So that worked, but still the question remains why DBMS_OUTLN.CREATE_OUTLINE doesn't use the available outline information in the shared pool.
Now let's turn to a different approach to achieve the same. 10g introduced SQL profiles that are primarily used to amend information that is not available to the cost based optimizer, e.g. in case of correlated column values the SQL Tuning Advisor of 10g can suggest to accept a SQL profile that scales the cardinality estimate so that the cardinality estimate is in the right ballpark.
A good explanation of SQL profiles can be found in Christian Antognini's publications.
But since SQL profiles internally consist of a set of hints, it could be possible to use SQL profiles instead of Stored Outlines to achieve the same.
There are two interesting aspects regarding this approach:
- We could use different sources to get the outline, e.g. instead of the shared pool we could get the hints from the AWR tables.
- SQL profiles support a "FORCE_MATCH" option that works similar to the CURSOR_SHARING literal replacement logic, i.e. SQL profiles can be forced to apply to multiple SQL statements that differ only by the literals used (i.e. no usage of bind variables).
So we are faced with two challenges in this regard:
1. Get the outline information, i.e. the full set of hints to provide plan stability 2. Create a SQL profile that consists of these hints
Get the outline information
There are two ways how the outline information could be obtained:
a) Use the DBMS_XPLAN.DISPLAY* functions with the ADVANCED or OUTLINE option and parse the this output to get the set of hints
b) Directly query the underlying tables/views to get the XML stored in the OTHER_XML column and extract the hints from that XML
a) Use the DBMS_XPLAN.DISPLAY* functions
Let me digress a little bit. Looking at the (already parsed a bit) output we get from the official DBMS_XPLAN function:
SQL>
SQL> with a as (
2 select
3 rownum as r_no
4 , a.*
5 from
6 table(
7 dbms_xplan.display_cursor(
8 'c2trqja6wh561'
9 , 0
10 , 'OUTLINE'
11 )
12 ) a
13 ),
14 b as (
15 select
16 min(r_no) as start_r_no
17 from
18 a
19 where
20 a.plan_table_output = 'Outline Data'
21 ),
22 c as (
23 select
24 min(r_no) as end_r_no
25 from
26 a
27 , b
28 where
29 a.r_no > b.start_r_no
30 and a.plan_table_output = ' */'
31 ),
32 d as (
33 select
34 instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
35 from
36 a
37 , b
38 where
39 r_no = b.start_r_no + 4
40 )
41 select
42 substr(a.plan_table_output, d.start_col) as outline_hints
43 from
44 a
45 , b
46 , c
47 , d
48 where
49 a.r_no >= b.start_r_no + 4
50 and a.r_no <= c.end_r_no - 1
51 order by
52 a.r_no;
OUTLINE_HINTS
--------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2"
("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
END_OUTLINE_DATA
14 rows selected.
SQL>
You'll notice that the INDEX_RS_ASC hint is split across two lines, so we can't simply use that query output to construct the hints because these hints would be potentially illegal and therefore we need to merge/concatenate these split lines.
This is a variation of the well known "columns-to-rows" aka. STRAGG/CONCAT issue and there are multiple ways how to deal with that using plain SQL.
For more information about this particular issue, see e.g. the SQL snippets site.
Here are two ways how to achieve that concatenation using hierarchical queries or the SQL MODEL clause introduced in 10g:
SQL>
SQL> with a as (
2 select
3 rownum as r_no
4 , a.*
5 from
6 table(
7 dbms_xplan.display_cursor(
8 'c2trqja6wh561'
9 , 0
10 , 'OUTLINE'
11 )
12 ) a
13 ),
14 b as (
15 select
16 min(r_no) as start_r_no
17 from
18 a
19 where
20 a.plan_table_output = 'Outline Data'
21 ),
22 c as (
23 select
24 min(r_no) as end_r_no
25 from
26 a
27 , b
28 where
29 a.r_no > b.start_r_no
30 and a.plan_table_output = ' */'
31 ),
32 d as (
33 select
34 instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
35 from
36 a
37 , b
38 where
39 r_no = b.start_r_no + 4
40 ),
41 e as (
42 select a.r_no
43 , substr(a.plan_table_output, d.start_col) as outline_hints
44 from
45 a
46 , b
47 , c
48 , d
49 where
50 a.r_no >= b.start_r_no + 4
51 and a.r_no <= c.end_r_no - 1
52 order by
53 a.r_no
54 ),
55 f as (
56 select
57 case substr(e.outline_hints, 1, 1)
58 when ' '
59 then r_no - 1
60 else null
61 end as par_id,
62 e.*
63 from
64 e
65 )
66 select
67 replace(aggr,'|', '') as aggr
68 from (
69 select
70 par_id
71 , sys_connect_by_path(trim(outline_hints), '|') as aggr
72 , level as lvl
73 from
74 f
75 where
76 connect_by_isleaf = 1
77 start with
78 par_id is null
79 connect by
80 prior r_no = par_id
81 order siblings by
82 r_no
83 );
AGGR
-------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2"("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
END_OUTLINE_DATA
13 rows selected.
SQL>
And here using the MODEL clause:
SQL>
SQL> with a as (
2 select
3 rownum as r_no
4 , a.*
5 from
6 table(
7 dbms_xplan.display_cursor(
8 'c2trqja6wh561'
9 , 0
10 , 'OUTLINE'
11 )
12 ) a
13 ),
14 b as (
15 select
16 min(r_no) as start_r_no
17 from
18 a
19 where
20 a.plan_table_output = 'Outline Data'
21 ),
22 c as (
23 select
24 min(r_no) as end_r_no
25 from
26 a
27 , b
28 where
29 a.r_no > b.start_r_no
30 and a.plan_table_output = ' */'
31 ),
32 d as (
33 select
34 instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
35 from
36 a
37 , b
38 where
39 r_no = b.start_r_no + 4
40 ),
41 e as (
42 select
43 a.r_no
44 , substr(a.plan_table_output, d.start_col) as outline_hints
45 from
46 a
47 , b
48 , c
49 , d
50 where
51 a.r_no >= b.start_r_no + 4
52 and a.r_no <= c.end_r_no - 1
53 ),
54 f as (
55 select
56 case substr(e.outline_hints, 1, 1)
57 when ' '
58 then null
59 else r_no
60 end as grp_id
61 , e.*
62 from
63 e
64 ),
65 g as (
66 select
67 case
68 when grp_id is null
69 then last_value(grp_id ignore nulls) over (order by r_no)
70 else null
71 end as par_id
72 , f.*
73 from
74 f
75 )
76 select
77 aggr
78 from
79 g
80 model
81 return updated rows
82 partition by (
83 nvl(grp_id, par_id) as grp
84 )
85 dimension by (
86 row_number() over (
87 partition by
88 nvl(grp_id, par_id)
89 order by
90 r_no
91 ) as rn
92 )
93 measures (
94 cast(outline_hints as varchar2(4000)) as aggr
95 , r_no
96 )
97 rules
98 iterate (1000)
99 until presentv(aggr[ITERATION_NUMBER+3],1,2)=2 (
100 aggr[1] = aggr[1] ||
101 trim(aggr[ITERATION_NUMBER+2])
102 )
103 order by r_no
104 ;
AGGR
----------------------------------------------------------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
END_OUTLINE_DATA
13 rows selected.
SQL>
b) Directly query the underlying tables/views to get the XML
The other option would be to query the respective tables/views directly to obtain the hints from the XML stored in the OTHER_XML column of execution plans.
Here we can use the powerful XML functions of Oracle 10g:
SQL>
SQL> select
2 extractvalue(value(d), '/hint') as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 sql_id = 'c2trqja6wh561'
12 and child_number = 0
13 and other_xml is not null
14 )
15 ) d;
OUTLINE_HINTS
-------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
11 rows selected.
SQL>
Instead of V$SQL_PLAN/V$SQL we could e.g. use DBA_HIST_SQL_PLAN/DBA_HIST_SQLTEXT to obtain the outline information from the AWR.
Create a SQL profile that consists of these hints
Now the second challenge is how to generate a SQL profile once we have identified the hints to use.
Here comes the DBMS_SQLTUNE package into the picture. It offers an (not officially documented) procedure IMPORT_SQL_PROFILE that is obviously used by the import facilities to create SQL profiles.
-- NAME: import_sql_profile - import a SQL profile
-- PURPOSE: This procedure is only used by import.
-- INPUTS: (see accept_sql_profile)
-- REQUIRES: "CREATE ANY SQL PROFILE" privilege
--
PROCEDURE import_sql_profile(
sql_text IN CLOB,
profile IN sqlprof_attr,
name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL,
category IN VARCHAR2 := NULL,
validate IN BOOLEAN := TRUE,
replace IN BOOLEAN := FALSE,
force_match IN BOOLEAN := FALSE);
It simply takes a collection of varchar2(500) strings that make up the profile.
So we can combine the two things into a procedure that generates us a SQL profile from either the shared pool or the AWR. Here's one for the shared pool. It takes four parameters: The SQL_ID, the child_number, the SQL profile category and whether to force a match or not.
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
v$sql_plan
where
sql_id = '&&1'
and child_number = &&2
and other_xml is not null
)
) d;
select
sql_fulltext
into
cl_sql_text
from
v$sql
where
sql_id = '&&1'
and child_number = &&2;
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => '&&3'
, name => 'PROFILE_&&1'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&4
);
end;
/
Here's the one for the AWR. It takes as parameter the SQL_ID, the PLAN_HASH_VALUE and like the first one the SQL profile category and the FORCE_MATCH option.
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select
extractvalue(value(d), '/hint') as outline_hints
bulk collect
into
ar_profile_hints
from
xmltable('/*/outline_data/hint'
passing (
select
xmltype(other_xml) as xmlval
from
dba_hist_sql_plan
where
sql_id = '&&1'
and plan_hash_value = &&2
and other_xml is not null
)
) d;
select
sql_text
into
cl_sql_text
from
dba_hist_sqltext
where
sql_id = '&&1';
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => '&&3'
, name => 'PROFILE_&&1'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => &&4
);
end;
/
So let's try all the stuff in one shot:
SQL>
SQL> drop table t_fetch_first_rows purge;
Table dropped.
SQL>
SQL> create table t_fetch_first_rows (
2 id number not null,
3 name varchar2(30) not null,
4 type varchar2(30) not null,
5 measure number
6 );
Table created.
SQL>
SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id);
Index created.
SQL>
SQL> -- create an empty table
SQL> -- and gather statistics on it
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> -- now put in some data
SQL> insert /*+ append */ into t_fetch_first_rows (
2 id,
3 name,
4 type,
5 measure)
6 select object_id, object_name, object_type, object_id as measure
7 from all_objects, (select level as id from dual connect by level <= 1000) dup
8 where object_type in ('VIEW', 'SCHEDULE')
9 and rownum <= 1000;
1000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- This is going to use
SQL> -- the wrong plan
SQL> -- that we - only for demonstration purposes -
SQL> -- attempt to keep now
SQL> select sum(measure), count(*) from (
2 select * from t_fetch_first_rows
3 where type = 'VIEW'
4 order by id
5 );
SUM(MEASURE) COUNT(*)
------------ ----------
900000 1000
SQL>
SQL> -- uses index
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID c2trqja6wh561, child number 0
-------------------------------------
select sum(measure), count(*) from ( select * from t_fetch_first_rows where type
= 'VIEW' order by id )
Plan hash value: 1903859112
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | SORT AGGREGATE | | 1 | 43 | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 43 | 0 (0)|
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)|
------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2"
("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='VIEW')
41 rows selected.
SQL>
SQL> -- now gather statistics again
SQL> -- on table with data
SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true)
PL/SQL procedure successfully completed.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> -- now the EXPLAIN PLAN tells us
SQL> -- full table scan
SQL> select * from table(dbms_xplan.display(null, null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2")
OUTLINE(@"SEL$2")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
MERGE(@"SEL$73523A42")
OUTLINE_LEAF(@"SEL$51F12574")
ALL_ROWS
OPT_PARAM('query_rewrite_enabled' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
33 rows selected.
SQL>
SQL> -- These are the hints
SQL> -- stored in the child cursor
SQL> -- in the shared pool
SQL> -- It clearly shows an index access
SQL> select
2 substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 sql_id = 'c2trqja6wh561'
12 and child_number = 0
13 and other_xml is not null
14 )
15 ) d;
OUTLINE_HINTS
----------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
OPT_PARAM('query_rewrite_enabled' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$51F12574")
MERGE(@"SEL$73523A42")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$73523A42")
ELIMINATE_OBY(@"SEL$2")
OUTLINE(@"SEL$2")
INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRS
11 rows selected.
SQL>
SQL> -- Create the SQL profile based on that cursor
SQL> @create_profile_from_shared_pool c2trqja6wh561 0 TEST true
SQL> declare
2 ar_profile_hints sys.sqlprof_attr;
3 cl_sql_text clob;
4 begin
5 select
6 extractvalue(value(d), '/hint') as outline_hints
7 bulk collect
8 into
9 ar_profile_hints
10 from
11 xmltable('/*/outline_data/hint'
12 passing (
13 select
14 xmltype(other_xml) as xmlval
15 from
16 v$sql_plan
17 where
18 sql_id = '&&1'
19 and child_number = &&2
20 and other_xml is not null
21 )
22 ) d;
23
24 select
25 sql_text
26 into
27 cl_sql_text
28 from
29 -- replace with dba_hist_sqltext
30 -- if required for AWR based
31 -- execution
32 v$sql
33 -- sys.dba_hist_sqltext
34 where
35 sql_id = '&&1'
36 and child_number = &&2;
37 -- plan_hash_value = &&2;
38
39 dbms_sqltune.import_sql_profile(
40 sql_text => cl_sql_text
41 , profile => ar_profile_hints
42 , category => '&&3'
43 , name => 'PROFILE_&&1'
44 -- use force_match => true
45 -- to use CURSOR_SHARING=SIMILAR
46 -- behaviour, i.e. match even with
47 -- differing literals
48 , force_match => &&4
49 );
50 end;
51 /
old 18: sql_id = '&&1'
new 18: sql_id = 'c2trqja6wh561'
old 19: and child_number = &&2
new 19: and child_number = 0
old 35: sql_id = '&&1'
new 35: sql_id = 'c2trqja6wh561'
old 36: and child_number = &&2;
new 36: and child_number = 0;
old 37: -- plan_hash_value = &&2;
new 37: -- plan_hash_value = 0;
old 42: , category => '&&3'
new 42: , category => 'TEST'
old 43: , name => 'PROFILE_&&1'
new 43: , name => 'PROFILE_c2trqja6wh561'
old 48: , force_match => &&4
new 48: , force_match => true
PL/SQL procedure successfully completed.
SQL>
SQL> alter session set sqltune_category = 'TEST';
Session altered.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> -- Uses SQL profile (see Note section)
SQL> -- and uses index
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1903859112
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1000 | 11000 | 9 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1000 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='VIEW')
Note
-----
- SQL profile "PROFILE_c2trqja6wh561" used for this statement
19 rows selected.
SQL>
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW2'
6 order by id
7 );
Explained.
SQL>
SQL> -- Very cool: Still uses SQL profile (see Note section)
SQL> -- although no exact text match
SQL> -- this is not possible using Stored Outlines
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1903859112
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 11 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TYPE"='VIEW2')
Note
-----
- SQL profile "PROFILE_c2trqja6wh561" used for this statement
19 rows selected.
SQL>
SQL> alter session set sqltune_category = 'DEFAULT';
Session altered.
SQL>
SQL> -- drop the SQL profile
SQL> exec dbms_sqltune.drop_sql_profile('PROFILE_c2trqja6wh561')
PL/SQL procedure successfully completed.
SQL>
SQL> -- This is the plan
SQL> -- we get based on the present statistics
SQL> explain plan
2 for
3 select sum(measure), count(*) from (
4 select * from t_fetch_first_rows
5 where type = 'VIEW'
6 order by id
7 );
Explained.
SQL>
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2125410158
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TYPE"='VIEW')
14 rows selected.
SQL>
You can see two things here:
1. The SQL profile created forces the plan we wanted, so it seems to work as expected
2. The FORCE_MATCH option of the SQL profiles allows to use this profile even for SQLs that are not an exact text match of the original statement. This is something that is as far as I know not possible using Stored Outlines.
So if you have the need to fix the execution plan, and you have that plan already in the shared pool or the AWR, using above procedures allow you to generate a SQL profile which seems to do exactly what we want.
Given the fact that the SQL profile even allows to share the plan for SQLs that differ only by literals I definitely favor the SQL profiles over the Stored Outlines approach.
Very interesting, there is a lot to derive. Thanks.
ReplyDeleteRandolf,
ReplyDeleteVery interesting post. Thanks for bringing it to my attention. I have added a link to it from my post on outlines and updated my post slightly based on your comments.
Kerry
Randolf,
ReplyDeleteThis is Awsome information. I appreciate your posts on OTN a couple of weeks back, and I am so glad you are sharing this information. I was just about to go through this same procedure myself. Just today, I used display_awr to get the old plan back, and then create a sql_profile from the hints.
The only additional information I would give is to utilize sys.WRH$_SQLSTAT. By querying this view, and joining to dba_hist_snaphot, you can view historic information about how the query performance for each plan used.. I usually select out the hash_plan_value,executions_delta,gets_delta/executions_delta,elapsed_time_delta/executions_delta
for a sql_id.
I then look at the elapsed time, and the different hash_plan_values to find the correct plan to go back to.. You can then use display_awr to show the hints you want for that specific plan.
This makes it very useful to go back and force an old plan that was right quickly, and concretely.
Bryan,
ReplyDeletethanks for your comment and the description of your approach how to identify a "good" plan.
Using the provided information about elapsed time etc. obviously can be used to separate the "good" plans from the bad ones, yes.
Since the post is already quite lengthy I think it's out of scope for this one, but may be something to write about in a separate post.
Thanks for sharing this information.
Best Regards,
Randolf
Randolf,
ReplyDeleteThis really is an excellent post!
I have been using your scripts to create profiles quite a bit lately. The ability to go back to a plan in AWR quickly and easily is really useful. I actually wrote a little wrapper for both of your scripts (documented here SQL Profiles as you know) I did find small problem with the one that creates the profile based on the text in the shared pool though. I got a message saying that the SQL statement already had a profile on it when trying to create one based on a SQL statement in the shared pool. Turns out your script had used sql_text (which is a clob with the complete SQL text in DBA_HIST_SQLTEXT but is a truncated (1000 characters) version in in v$sql. So I changed your script to use the sql_fulltext column, which is a clob and has the whole string.
Kerry,
ReplyDeletethanks a lot for pointing this out... I've changed the code snippet accordingly.
It's interesting that you seem to have to "lock down" so many plans. I don't come across this issue that often, but it's good to know that this blog post is useful.
Regards,
Randolf
Yeah, it's all on the same system actually. When I say a lot I mean like 10 of them. They are all pretty much variations of the same query. (that's why I noticed the truncated SQL Text - several of them have an exact match on the first 1000 characters). Anyway, it's a long story, but basically the Profiles are a bandaid until we can fix the underlying cause. Nevertheless, it's been very helpful because the swing between the plans is pretty huge - one plan does a few hundred lio's, the other does s few hundred thousand lio's.
ReplyDeleteHi
ReplyDeleteI am not able to find the below script in your document "@create_profile_from_shared_pool"
Kindly send the script. and please give your suggestions ,
waiting for your reply
Thanking you
Regards
S.Ramachandran
The script is part of the blog post. It's the one following the text: "So we can combine the two things into a procedure..."
ReplyDeleteRegards,
Randolf
Hi Randolf,
ReplyDeleteVery useful post and I have a question related to it.
Today I have tried to create a outline using a dbms_outln.create_outline and hit issue with a plan stability. When I have created outline using dbms_outln package for that same query it was different plan then I created outline using create_stored_outlines = true.
Did you ever got similar problem ?
regards,
Marcin
Marcin,
ReplyDeleteI'm not sure I understand what exactly your point is.
If you read through the post you'll notice that in the beginning of the post I describe my finding that DBMS_OUTLN.CREATE_OUTLINE obviously didn't use the plan associated with the referenced child cursor from the shared pool, but probably re-optimized the SQL statement (parent cursor) associated with the child cursor and therefore generated a different plan to use for the outline in my case, because the underlying statistics had changed in meanwhile.
May you've hit a similar issue, I don't know?
It would be interesting to know, because I haven't investigated further why exactly DBMS_OUTLN.CREATE_OUTLINE behaved so counter-intuitively and didn't use the most obvious information available: The hints associated with the child cursor in the shared pool.
Regards,
Randolf
Hi Randolf,
ReplyDeleteSorry for my not clear post.
Exactly I miss this very important statement from you post about creating outline using dbms_outln. I hit this same issue - different outline is created in case of using of dbms_outln - it looks like dbms_outln generate a new plan for a query.
If you are interested in I can send you a whole test case via mail.
regards,
Marcin,
ReplyDeleteI'm not sure if this is a misunderstanding, but as already mentioned I describe in the beginning of the post exactly this issue, so what is it that you are exactly missing from the post?
Do you mean that I should put more emphasis on this issue, or e.g. write a separate post about this particular finding?
Regards,
Randolf
Randolf,
ReplyDeleteYou describe that issue, but when I read your post I just missed it (I have to read more carefull)
and this is why I asked my question.
sorry for mix you up
A separate post about it is a good idea.
regards,
Marcin
Hi,
ReplyDeleteCan outline hints ( consisting of execution steps) be used directly as hints in SQL rather than generate profiles and use these ?
This can be helpful in extracting RBO's outlines and then forcing execution steps without profiles or outlines .The Query optimizer should have some additional hint that instructs it to use following outline hints in exec path.
Thx
Anil Bishnoie
> Can outline hints ( consisting of
ReplyDelete> execution steps) be used directly
> as hints in SQL rather than
> generate profiles and use these ?
Anil,
as far as I understand an Outline consists exactly of those set of hints. You should be able to apply them directly and 1:1 to the corresponding SQL statement to get the desired execution plan if no structural changes to the database have been applied that make some of the hints invalid.
> The Query optimizer should have
> some additional hint that
> instructs it to use following
> outline hints in exec path.
In principle the "normal" hints contained in the Outline should be sufficient to force the desired execution plan, however there are actually some "special" hints contained in the Outline hint set. Those are the OUTLINE and OUTLINE_LEAF hints. I think Jonathan Lewis in some of his notes/blog posts had a good explanation what he thinks they are used for.
Randolf
Hi Randolf,
ReplyDeleteThis trick is so useful that I decided to write a post about it on my humble french Oracle blog:
http://ahmedaangour.blogspot.com/2011/01/forcer-un-plan-dexecution-via-un-sql.html
Of course I mentioned your name and I provided a link to your post. Hope it does not bother you.
Hi Ahmed,
ReplyDeletethanks for letting me know.
No, of course I don't have any obligations - it seems to be a "hot" topic anyway since this post is regularly among the most popular posts of this blog, although it is already quite old.
Randolf
Plan instability is the bane of my Oracle existence. Far too often I can look in sql history and find a better plan than the one Oracle is using now. Of course this is a very small percentage of overall queries, and I generally only work on queries where there are complaints about performance, but still I see too many of them. Now that I know how to apply a profile from one query to another, I can either apply a profile from the historically good plan, or else if there's no history of a better plan, rewrite the query so that it performs well, run the optimized version in the problem database, and create a profile for the production query from the tuned query. What could be simpler than that first case - take the historically good plan, create a profile from its outline hints, fix your performance problem? And in the second case, if it's a third party application, and I don't want to start a statistics research/gathering exercise that might need to be repeated as statistics age, why wouldn't I want to take the outline hints from an optimized query and create a profile from them? I'm seeing good results from both cases. Seems like a great technique to me. I have trouble understanding why someone wouldn't use these techniques. We're already paying for those packs, why not use them? It's just too bad that the profiles aren't really permanent in their effects - I've seen Oracle stop using profiles that don't include cardinality or scale hints. I think I understand why Oracle doesn't supply a 100% guaranteed technique for freezing an execution plan, even though there queries that will need only a single plan for the life of a system are a pretty common class of query - the reason is that their responsibility is to their shareholders, not to their customers. But maybe I'm wrong and 11g plan baselines are really guaranteed to stick. Anyway thanks for your articles about sql profiles and for the pointer to Jonathan Lewis's baseline transplant article, I'm sure I'll want to look into using that method in 11g. A recap of why use sql profiles: when Oracle shows you that it failed to apply its previous good solution to a problem, and when it's easy to remind Oracle about that solution via application of a sql profile from a historically good plan, or when you can easily transplant hints from an optimized query into a profile, and in both cases the performance issues are restricted to a single query, and if you're already paying for the licenses, why wouldn't you want to use profiles?
ReplyDeleteSlight correction - plan instability USED TO BE the bane of my Oracle existence, but since I learned to apply profiles from history or from optimized queries, plan instability is often more of an opportunity to provide a simple, quick, localized solution than it is "the bane."
ReplyDeletePaul,
ReplyDeletethanks for stepping by and taking the chance to provide some feedback I've asked for in the updated post.
Of course it would be interesting to understand the root cause(s) of the bad plans that you seem to encounter sometimes. Are bind variables involved - is this 10g or 11g?
Anyway your comment is much appreciated.
Randolf
Almost all of this work has been done in 10g. Often I've been busy to the point where I just go with the sql profile fix because it's a quick fix that allows me to get back to whatever else I was working on. If I'm busy and I see a plan regression that can be fixed with a sql profile, I'll do that every time because a) the company needs to have the ability to apply such fixes and won't learn them unless I use them and advertise them and b) it's fast and usually gets the job done. Root cause doesn't matter much if you're too busy to do the drill down.
ReplyDeleteThat's true for a subset of problems, the plan regression problems, and sometimes for the queries that need to be rewritten to keep the optimizer from getting the plan wrong. In some the optimizer just flat gets it wrong - join order, or timing of predicate application , or both - and I often can rewrite the query so that it performs well. I call it "immunizing your query against the optimizer mistakes" by just being really explicit with ordering and by using no_merge inline views to keep Oracle from not using what I'm telling it about join orders and how the tables should be joined and filtered. Sometimes I've tried to use the outline hints for the rewritten queries to create profiles for other queries. If it works, good, I rush back to whatever else I was working on. Root cause? We can address that later. When you work for a company where for some applications there's not much thought given to application tuning until after rollout, well maybe you can imagine that I don't have time to go after root cause on every plan regression.
Hi Randolph,
ReplyDeleteThank you in general for your help to the Oracle community and in particular for this post.
I came here seeking a solution to force plan stability. I will describe the problem as you have suggested in your post.
There is a SQL statement which gets parsed once and executed multiple times with different bind variable values. This SQL resides in a package.
If the SQL statement gets hard-parsed with a NULL value of the bind variable, it picks up a plan that is very inefficient for the subsequent genuine bind values.
With condition 'col1 = NULL', CBO thinks it's not going to return any records and chooses a plan accordingly. However for the subsequent bind variables this plan proves disastrous.
Apart from plan stability (profiles or outlines), the only other way I can think of is changing the code to put a cardinality hint.
Of course, code change will be required since NULL is not a valid value (and so a bug in the code).
Hope this makes sense.
Many thanks,
CJ.
What I wanted to ask in the earlier post was - is there any other way to optimize in this scenario?
ReplyDeleteThanks,
CJ
CJ,
ReplyDeleteyou haven't mentioned a version in your comment, but in 11g your problem should be picked up by Adaptive Cursor Sharing, however only after things went wrong at least once. In pre-11g there is probably not really a good way to handle this from the database side only, so "application awareness" and corresponding code changes are in order.
For more information about ACS and what you can do from application side, you might want to read this post
Randolf
The version is 10.2.0.5.
ReplyDeleteThanks for the reply.
Regards,
CJ.
Hi Randolf,
ReplyDeleteSQL Plan Baselines seem to be part of SQL Plan Management, which is EE only.
Regards,
Martin
Hi Martin,
ReplyDeletethanks for the heads-up - I meant to update this post for quite a while now to correct that.
Randolf
Still helpful, thanks Randolf.
ReplyDeleteHi Randolf,
ReplyDeleteThanks for this post.
I have a situation where I have to bring a good plan from one database to another.
So, I should be able to just export the good plan hints, import the table into the other database and use this table to bulk collect into ar_profile_hints
and import the profile correct?
Or will there be a need of some additional steps?
Thanks.
--MS
As mentioned in the beginning of the updated post, I would recommend using SQL Plan Baselines instead of SQL Profiles for general plan stability.
ReplyDeleteOnly use SQL Profiles for that purpose if you need special features like the "FORCE_MATCH" option that isn't available for SQL Plan Baselines - and note that you need a Diagnostics + Tuning Pack license to use SQL Profiles - which you don't need for SQL Plan Baselines.
If you want to use SQL Profiles somehow getting the list of required hints to your remote system should be sufficient to get the job done.
An official procedure for doing so was provided by Carlos Sierra some time ago:
See MOS document 457531.1 and 1487302.1.
See also Kerry Osborne's blog:
http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/
http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/#comment-476207
This script generates another script that can be run on any database to create a SQL Profile containing the required hints to reproduce the plan.
Exporting/Importing SQL Plan Baselines is a documented procedure, see for example here:
http://avdeo.com/2011/08/07/oracle-sql-plan-management-%E2%80%93-part-3/
Exporting/Importing SQL Profiles is also officially supported:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sqltun.htm#ARPLS68380
So you see there are many different ways you can accomplish that task, depending on what you exactly need to do.
Randolf
Randolf , thank you very much for this very insightful article.. However I had a query regarding Baselines ... What about a query that runs for both a very small amount of data and a very large amount of data.If the baseline is set for large amount of data then when we run for a small amount of data it will take minutes instead of seconds. The other way around (set the baseline for small and then run large) could make the large query go from hours to days. Is this possible and if it is so , is there anyway that this can be avoided ?
ReplyDeleteRemember that one of the features of SQL Plan Baselines is that you can have more than one plan associated with a SQL statement. So nothing stops you from adding two enabled plans as Baselines. Provided the optimizer then chooses the right one automatically of course.
ReplyDeleteIt also depends on your exact circumstances - are we talking about the same SQL statement using binds, are we talking about the same SQL statement and the underlying tables do have vastly differing data volumes and so on.
But as I said, a Baseline can have multiple plans associated, so maybe that is already what you're looking for.
Randolf
Hi,
ReplyDeleteI think you explain clearly why using an outline is a bad idea. Data changes, a schema will grow between now and 2 years. The plan should adapt.
I find dbms_outline to be useful tool for very specific situations when all hopes are lost.
This happens to be the unfortunate case for me at the moment:
1) Migration from 11g to 12c.
2) Discovery of an important sub 0 second query now performing at 23 seconds.
3) Investigation shows that 12c struggles with subqueries using UNION all.
4) Compounded problem when baselines appear to fail for the same reason (We are trying to "stop the bleeding").
5) I'm left with producing an outline from 11c (ALTER SESSION SET optimizer_features_enable='11.2.0.4.0'; ) and now applying it.
6) Now I'm working with oracle on a root cause and gaining directives on what must be avoided in the query and what we need to look into changing in our code.
7) When changes are applied: Remove outline.
It feels a bit like a game of chess when my moves are dictated by my opponent and I'm not winning.
Hi Nico,
ReplyDeletethanks for your comment.
What I'm interested in and don't understand from your comment is why SQL Plan Baselines failed and you're left with the Outline - after all I can't see what functionality the Outline offers that the Baseline doesn't offer - hence I could only think of a bug, but I would like to hear more about this.
In principle you should be able to produce a Baseline from the plan you get with OPTIMIZER_FEATURES_ENABLE = 11.2.0.4 as for example described in the linked article by Jonathan Lewis.
Randolf