http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/
Roughly 10 years ago, Oracle introduced a way to lock down execution plans. This was a response to the inherent unpredictability of the early cost based optimizer. The Oracle marketing machine even gave the feature the name “Plan Stability”. It appeared to be a good approach, basically allowing a DBA to lock an existing plan for a given statement. The mechanism employed to accomplish this was to create hints that didn’t allow the optimizer much (if any) flexibility to come up with an alternative plan. These hints are stored in the database in a structure called an OUTLINE or sometimes a STORED OUTLINE. The optimizer could then apply these hints behind the scenes whenever a SQL statement that matched was executed. By the way, “matching” basically means that the text of the statement matches. Originally outlines had to match character for character just like the normal rules for sharing SQL statements, but for some reason, Oracle later decided that the matching algorithm should be somewhat relaxed as compared to Oracle’s standard. What that means is that in 10gR2 by default whitespace is collapsed and differences in case are ignored. So (at least as far as outlines are concerned) “select * from dual” is the same as “SELECT * FROM DuAl”. You’ll still get two different statements in the shared pool but they will use the same outline, if one exists.
With 9i, Oracle started to enhance this feature by adding the ability to edit the outlines themselves, but they never really completed the job. They pretty much quit doing anything with the feature after 9i. In fact, the script that creates the DBMS_OUTLN package ($ORACLE_HOME/rdbms/admin/dbmsol.sql) has not been updated since early in 2004 (with the exception of a tweak to keep it working in 11g). Anyway, I think it is a great feature with two primary uses.
- First, it can be used to freeze a plan for a statement. This is especially helpful in situations where bind variable peeking is causing Oracle to alternate between a couple of plans.
- Second, it can be very helpful when dealing with an application where the code can not be modified. Outlines provide a means of altering the execution plan for a statement without changing the code or making changes to the basic database configuration.
Lot’s of people have written about outlines, so I don’t want to just repeat information that is already available. But there doesn’t seem to be a single spot that pulls together all (at least what I consider to be all) the important stuff. Also, most of the stuff I have seen about outlines was written for 8i or 9i. As this is being written, 11gR1 has been out for over a year (although it has still not been widely adopted), and 10gR2 is far and away the most prevalent version in production. So, here we go.
Outlines can be created two ways.
- You can use the CREATE OUTLINE statement – which allows you to give your outline a name, but requires you to include the SQL statement as part of your CREATE OUTLINE statement. Therefore you can’t see what the execution plan is before creating the outline. Not very useful in my opinion.
- You can use the CREATE_OUTLINE procedure in the DBMS_OUTLN package – which doesn’t allow you to give your outline a name, but does let you specify a specific child cursor of a specific SQL statement in the shared pool. This means you can check the execution plan before creating the outline and that you can be sure the statement exactly matches what is being sent from the application.
Here’s an example:
CREATE OUTLINE myoutline FOR CATEGORY temp ON select * from dual; EXEC DBMS_OUTLN.CREATE_OUTLINE('82734682234',0,'DEFAULT'); |
There are a few issues:
- First, outlines aren’t used unless you set the USE_STORED_OUTLINES pseudo parameter. This can be set at the session or the system level. Setting this at the session level only makes sense to me for testing purposes. The value can be TRUE, FALSE, or a category name. (more about categories in a minute). The default value for 9i and 10g and 11g is FALSE. This means that even if an outline is created, it won’t be used. The really irritating thing about USE_STORED_OUTLINES is that it is not a real boy (er, parameter). So you can’t see what it’s set to by selecting from the v$parameter view or it’s underlying X$ views (where the hidden parameters are exposed). Fairlie Rego has a post on his blog about using oradebug to see whether it has been set or not here. More importantly, this quirk means that the USE_STORED_OUTLINES setting is not persisted across instance bounces. This prompted an official bug and enhancement request (see Metalink Note:560331.1). The official response was to suggest a database trigger to set the value when an instance is started. (see outline_startup_trigger.sql below for the recommended trigger)
- Second, the DBMS_OUTLN.CREATE_OUTLINE procedure uses the old HASH_VALUE identifier as opposed to the newer SQL_ID that was introduced in 10g. While most of the internal structures were updated to use SQL_ID, outlines never were. This is just a slight irritation as it means you have to find the hash value to use the DBMS_OUTLN.CREATE_OUTLINE procedure. (see create_outline.sql script below for a way to get around this)
- Third, the DBMS_OUTLN.CREATE_OUTLINE procedure is a bit buggy. It often results in an error (1330 which disconnects your session from Oracle – nice!). There is a metalink note describing this issue (Note:463288.1) which references a bug (Bug 5454975) that is supposed to be fixed in 10.2.0.4. Anyway, the bottom line is that you should execute the command to enable stored outlines at the session level (i.e. ALTER SESSION SET USE_STORED_OUTLINES=TRUE) before attempting to create an outline with the DBMS_OUTLN.CREATE_OUTLINE procedure (again see the create_outline.sql script below).
- Fourth, the DBMS_OUTLN.CREATE_OUTLINE procedure does not allow a name to be specified for an outline. Instead it creates a system generated name. This is another minor irritation as outlines can be renamed easily enough with the ALTER OUTLINE command (see the create_outline.sql script below yet again for a way to do this when creating the outline)
Just a quick note on categories: A category may be specified when an outline is created. The default category is DEFAULT. If USE_STORED_OUTLINES is set to TRUE, outlines in the DEFAULT category will be used. If USE_STORED_OUTLINES is set to some other text string, only outlines in the specified category will be used. Categories seem to me to be fairly useless, although the DBMS_OUTLN package does include a procedure to drop all outlines in a specific category and it also provides the ability to move outlines from one category to another. So you may find it useful to use a category other than DEFAULT, but I haven’t.
I have built myself a few scripts:
- check_use_outlines.sql – This scripts uses oradebug to see if USE_STORED_OUTLINES has been set.
- outlines.sql – This script lists all outlines and shows whether they are enabled and if they have been used.
- outlines2.sql – The same as above but it also joins to the v$sqlarea view to get the sql_id for the statement (if it is currently in the shared pool). Note that this is will only return the exact match on sql_text and that outlines have a “relaxed” sql_text matching so there may be additional statements that use the outline. Note that this script uses a function called outline_signature that you’ll need to create as well.
- find_sql_using_outline.sql – This script lists all statements in the shared pool that are using an outline.
- create_outline.sql – This script uses the DBMS_OUTLINE.CREATE_OUTLINE procedure to create an outline for an existing cursor. It prompts for a sql_id, child_no, and name. Since the create_outline procedure uses hash_value instead of sql_id, this script has to get the hash_value first. Also, since the create_outline procedure doesn’t allow a name to be specified for the outline, this script renames the outline after it is created. Note that this script will not work in 9i because sql_id was not introduced until 10g.
- outline_hints.sql – This script lists the hints associated with a given outline.
- outline_startup_trigger.sql – This scripts creates an after startup database trigger that enables outline usage as per Metalink Note:560331.1.
Here’s the example – it’s rather long but has quite a bit in it.
> !sql sqlplus / as sysdba SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 17 16:09:15 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production With the Partitioning, OLAP and Data Mining options SQL> @check_use_outlines Statement processed. qolprm sgauso_ [2000F98C, 2000F9B0) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 SQL> SQL> alter system set use_stored_outlines=true; System altered. SQL> @check_use_outlines Statement processed. qolprm sgauso_ [2000F98C, 2000F9B0) = 00000001 45440007 4C554146 00000054 00000000 00000000 00000000 00000000 00000000 SQL> SQL> @find_sql Enter value for sql_text: %skew% Enter value for sql_id: SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO INVALIDATIONS SQL_TEXT ------------- ------ ---------- ---------- ------------- ------------ ------------- ------------------------------------------------------------ 37j3r0s8gzkuf 0 1528838287 1 .03 443 0 select count(*) from kso.skew where pk_col < 13 7cbu7dgt0vh6y 0 1528838287 1 .00 226 0 select count(*) from kso.skew where pk_col < 10 SQL> @create_outline Session altered. Enter value for sql_id: 7cbu7dgt0vh6y Enter value for child_number: 0 Enter value for outline_name: skew_10 Outline SKEW_10 created. PL/SQL procedure successfully completed. SQL> @outlines Enter value for category: Enter value for name: CATEGORY NAME USED ENABLED HINTS SQL_TEXT --------------- ------------------------------ ------ -------- ------ ---------------------------------------------------------------------- DEFAULT SKEW_10 UNUSED ENABLED 5 select count(*) from kso.skew where pk_col < 10 SQL> select count(*) from kso.skew where pk_col < 10; COUNT(*) ---------- 9 SQL> @outlines Enter value for category: Enter value for name: CATEGORY NAME USED ENABLED HINTS SQL_TEXT --------------- ------------------------------ ------ -------- ------ ---------------------------------------------------------------------- DEFAULT SKEW_10 USED DISABLED 5 select count(*) from kso.skew where pk_col < 10 SQL> @outlines2 Enter value for category: Enter value for name: CATEGORY NAME USED ENABLED HINTS SQL_ID SQL_TEXT --------------- ------------------------------ ------ -------- ------ --------------- ------------------------------------------------------------ DEFAULT SKEW_10 USED DISABLED 5 7cbu7dgt0vh6y select count(*) from kso.skew where pk_col < 10 SQL> @find_sql_using_outline Enter value for sql_text: Enter value for sql_id: SQL_ID CHILD EXECS AVG_ETIME OUTLINE_CATEGORY OUTLINE_NAME PLAN_HASH_VALUE SQL_TEXT ------------- ------ ---------- ------------- ---------------- ------------ --------------- ------------------------------------------------------- 7cbu7dgt0vh6y 0 1 .03 DEFAULT SKEW_10 1528838287 select count(*) from kso.skew where pk_col < 10 SQL> @outline_hints Enter value for name: SKEW_10 NAME HINT ------------------------------ ---------------------------------------------------------------------- SKEW_10 INDEX(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."PK_COL")) SKEW_10 OUTLINE_LEAF(@"SEL$1") SKEW_10 ALL_ROWS SKEW_10 OPTIMIZER_FEATURES_ENABLE('10.2.0.3') SKEW_10 IGNORE_OPTIM_EMBEDDED_HINTS 6 rows selected. SQL> alter outline skew_10 disable; Outline altered. SQL> @outlines Enter value for category: Enter value for name: CATEGORY NAME USED ENABLED HINTS SQL_TEXT --------------- ------------------------------ ------ -------- ------ ---------------------------------------------------------------------- DEFAULT SKEW_10 USED DISABLED 5 select count(*) from kso.skew where pk_col < 10 SQL> @find_sql Enter value for sql_text: %skew where pk_col < 10% Enter value for sql_id: no rows selected SQL> drop outline skew_10; Outline dropped. SQL> @outlines Enter value for category: Enter value for name: no rows selected SQL> !cat outline_startup_trigger.sql -- As recommended in Metalink Note:560331.1 -- -- This is necessary since the setting for use_stored_outlines -- does not persist across instance bounces. create or replace trigger enable_outlines after startup on database begin execute immediate('alter system set use_stored_outlines=true'); end; / show errors SQL> @outline_startup_trigger.sql Trigger created. SQL> show errors No errors. |
And a few last bits:
I think outlines are a very powerful tool and although it is clear the Oracle does not intend to continue enhancing this feature, it still can be pretty handy. I for one am glad to see that, at least for the time being, it will continue to work.
Keep in mind that outlines are not absolutely guaranteed to freeze a plan. Since they are implemented with hints, things can happen that make a hint invalid (dropping an index for example). But they are as close as one can come without changing the optimizer_mode to RULE.
Outlines have not been superseded by SQL Profiles. SQL Profiles are similar to outlines in that they implement hints behind the scenes. SQL Profiles are created by the SQL Tuning Advisor and can be a great help in figuring out why a plan has gone wrong. However, SQL Profiles that are create dby the SQL Tuning Advisor often provide less stability than outlines and they do tend to sour over time. By that I mean that many of the hints are aimed at improving the CBO’s estimates, rather than at freezing plans. They often accomplish this by applying scaling factors at various stages via the OPT_ESTIMATE hint. If for example the SQL Tuning Advisor determines that CBO is underestimating the number of rows that pass a certain filter by an order of magnitude, it will simply multiply that estimate by 10. If the stats later improve and the CBO does a better job in its estimating, the SQL Profile still multiplies that stage’s estimate by 10. By the way, there is nothing wrong with using a SQL Profile to get the plan you want and then creating an outline to lock it in. Keep in mind though that Outlines are not absolutely guaranteed to keep a plan from changing.
Outlines still appear to work in 11gR1. The fact that the dbmsol.sql file, which creates the DBMS_OUTLN package, has been updated for 11gR1 indicates that this is intentional. Adaptive Cursor Sharing and SQL Plan Management will probably make this feature less useful (i.e. some of the reasons we need to use them in 10g should go away), but it’s good to know that they still work.
Note: I do intend to do another post on tricks that can be used to get the plan you want, so that you can lock it down with an outline.
And here’s a few additional references:
Metalink:
Note 445126.1 – How to manually store an outline using V$SQL and DBMS_OUTLN.CREATE_OUTLINE
Note 728647.1 – How to Transfer Stored Outlines from One Database to Another (9i and above)
Note 726802.1 – Editing Stored Outlines in Oracle10g and Oracle11g
Note:730062.1 – How to Edit a Stored Outline to Use the Plan from Another Stored Outline
Here’s a good early article on outlines by Jonathan Lewis.
Tom Kyte’s Book “Expert One-on-One Oracle” has a great chapter on Outlines, although it is quite dated now, going only through 8.1.7. However, since outlines haven’t really been updated significantly since then (with the exception of dbms_outln.create_outline procedure) it is still a very good source of information. Also Randolf Geist has a very good post here on using SQL Profiles to lock plans here.
As always, your comments are welcome.
20 Comments