{\rtf1\ansi\ansicpg1252\uc1 \deff0\deflang1033\deflangfe1033{\fonttbl{\f0\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f1\fswiss\fcharset0\fprq2{\*\panose 020b0604020202020204}Arial;}
{\f2\fmodern\fcharset0\fprq1{\*\panose 02070309020205020404}Courier New;}{\f3\froman\fcharset2\fprq2{\*\panose 05050102010706020507}Symbol;}{\f4\froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times;}
{\f5\fswiss\fcharset0\fprq2{\*\panose 020b0604020202020204}Helvetica;}{\f6\fmodern\fcharset0\fprq1{\*\panose 00000000000000000000}Courier{\*\falt Courier New};}{\f49\froman\fcharset238\fprq2 Times New Roman CE;}
{\f50\froman\fcharset204\fprq2 Times New Roman Cyr;}{\f52\froman\fcharset161\fprq2 Times New Roman Greek;}{\f53\froman\fcharset162\fprq2 Times New Roman Tur;}{\f54\froman\fcharset186\fprq2 Times New Roman Baltic;}{\f55\fswiss\fcharset238\fprq2 Arial CE;}
{\f56\fswiss\fcharset204\fprq2 Arial Cyr;}{\f58\fswiss\fcharset161\fprq2 Arial Greek;}{\f59\fswiss\fcharset162\fprq2 Arial Tur;}{\f60\fswiss\fcharset186\fprq2 Arial Baltic;}{\f61\fmodern\fcharset238\fprq1 Courier New CE;}
{\f62\fmodern\fcharset204\fprq1 Courier New Cyr;}{\f64\fmodern\fcharset161\fprq1 Courier New Greek;}{\f65\fmodern\fcharset162\fprq1 Courier New Tur;}{\f66\fmodern\fcharset186\fprq1 Courier New Baltic;}{\f73\froman\fcharset238\fprq2 Times CE;}
{\f74\froman\fcharset204\fprq2 Times Cyr;}{\f76\froman\fcharset161\fprq2 Times Greek;}{\f77\froman\fcharset162\fprq2 Times Tur;}{\f78\froman\fcharset186\fprq2 Times Baltic;}{\f79\fswiss\fcharset238\fprq2 Helvetica CE;}
{\f80\fswiss\fcharset204\fprq2 Helvetica Cyr;}{\f82\fswiss\fcharset161\fprq2 Helvetica Greek;}{\f83\fswiss\fcharset162\fprq2 Helvetica Tur;}{\f84\fswiss\fcharset186\fprq2 Helvetica Baltic;}}{\colortbl;\red0\green0\blue0;\red0\green0\blue255;
\red0\green255\blue255;\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255\green255\blue0;\red255\green255\blue255;\red0\green0\blue128;\red0\green128\blue128;\red0\green128\blue0;\red128\green0\blue128;\red128\green0\blue0;
\red128\green128\blue0;\red128\green128\blue128;\red192\green192\blue192;}{\stylesheet{\widctlpar\adjustright \fs20\cgrid \snext0 Normal;}{\s1\li720\sb240\sa60\keepn\widctlpar\adjustright \b\f1\fs28\kerning28\cgrid \sbasedon0 \snext0 heading 1;}{
\s2\li720\sb360\sa240\widctlpar\adjustright \b\f5\cgrid \sbasedon0 \snext0 heading 2;}{\s3\sb240\sa60\keepn\widctlpar\adjustright \f1\cgrid \sbasedon0 \snext0 heading 3;}{\*\cs10 \additive Default Paragraph Font;}{\s15\widctlpar\adjustright 
\fs18\lang1024\cgrid \snext15 Table_Copy;}{\s16\sb60\sa60\widctlpar\adjustright \b\f1\fs16\cf1\lang1024\cgrid \snext16 Table_head;}{\s17\fi-360\li720\widctlpar\tx720{\*\pn \pnlvlbody\ilvl11\ls2047\pnrnot0\pnf4\pnstart1\pnindent-360\pnhang{\pntxtb ?}}
\ls2047\ilvl11\adjustright \f4\fs22\lang1024\cgrid \snext17 Hanging bullet;}{\s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid \snext18 Code_listing;}{\s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid \sbasedon0 \snext19 
Edit;}{\s20\li720\sa240\widctlpar\adjustright \f6\fs16\lang1024\cgrid \sbasedon18 \snext20 Code_listing_solo_line;}{\s21\fi-360\li360\ri2160\sa240\sl360\slmult1\widctlpar\tx360{\*\pn \pnlvlbody\ilvl11\ls2047\pnrnot0\pnf6\pnstart1\pnindent360\pnhang
{\pntxtb ?}}\ls2047\ilvl11\adjustright \f5\fs20\cf1\cgrid \sbasedon22 \snext21 Bullet_hang;}{\s22\sa120\widctlpar\adjustright \fs20\cgrid \sbasedon0 \snext22 Body Text;}{\s23\fi-360\li360\nowidctlpar\tx360{\*\pn \pnlvlbody\ilvl11\ls2047\pnrnot0
\pnf6\pnstart1\pnindent360\pnhang{\pntxtb ?}}\ls2047\ilvl11\adjustright \f1\fs20\cgrid \snext23 Hanging_bullet;}{\s24\fi-360\li1080\sa240\nowidctlpar\tx360{\*\pn \pnlvlbody\ilvl11\ls2047\pnrnot0\pnf6\pnstart1\pnindent360\pnhang{\pntxtb ?}}
\ls2047\ilvl11\adjustright \f1\fs20\cgrid \sbasedon23 \snext24 Hanging_bullet_2;}{\s25\widctlpar\tqc\tx4320\tqr\tx8640\adjustright \fs20\cgrid \sbasedon0 \snext25 header;}{\s26\li720\widctlpar\adjustright \fs20\cgrid \sbasedon0 \snext26 Normal Indent;}{
\s27\fi-360\li720\sa240\nowidctlpar{\*\pn \pnlvlbody\ilvl11\ls2047\pnrnot0\pndec\pnstart1\pnindent360\pnhang{\pntxta ?}}\ls2047\ilvl11\adjustright \f1\fs20\cgrid \sbasedon23 \snext27 Numba;}{\s28\li720\ri2160\sb360\sa360\sl360\slmult1\widctlpar\box
\brdrsh\brdrs\brdrw15\brsp20 \adjustright \f5\fs20\cgrid \sbasedon22 \snext22 Toolset;}{\s29\widctlpar\adjustright \f2\fs20\cgrid \sbasedon0 \snext29 Plain Text;}{\s30\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \b\i\f1\fs20\cgrid 
\sbasedon19 \snext30 Callout_subtitle;}{\*\cs31 \additive \ul\cf2 \sbasedon10 Hyperlink;}}{\*\listtable{\list\listtemplateid-1567478448\listsimple{\listlevel\levelnfc0\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext
\'02\'00.;}{\levelnumbers\'01;}\fi-360\li1492\jclisttab\tx1492 }{\listname ;}\listid-132}{\list\listtemplateid-776156922\listsimple{\listlevel\levelnfc0\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}
\fi-360\li1209\jclisttab\tx1209 }{\listname ;}\listid-131}{\list\listtemplateid831030762\listsimple{\listlevel\levelnfc0\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\fi-360\li926\jclisttab\tx926 }
{\listname ;}\listid-130}{\list\listtemplateid-1468346208\listsimple{\listlevel\levelnfc0\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\fi-360\li643\jclisttab\tx643 }{\listname ;}\listid-129}
{\list\listtemplateid-578499980\listsimple{\listlevel\levelnfc23\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0 \fi-360\li1492\jclisttab\tx1492 }{\listname ;}\listid-128}
{\list\listtemplateid-1000946868\listsimple{\listlevel\levelnfc23\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0 \fi-360\li1209\jclisttab\tx1209 }{\listname ;}\listid-127}
{\list\listtemplateid-1758967546\listsimple{\listlevel\levelnfc23\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0 \fi-360\li926\jclisttab\tx926 }{\listname ;}\listid-126}
{\list\listtemplateid-765054544\listsimple{\listlevel\levelnfc23\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0 \fi-360\li643\jclisttab\tx643 }{\listname ;}\listid-125}
{\list\listtemplateid1795572492\listsimple{\listlevel\levelnfc0\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'02\'00.;}{\levelnumbers\'01;}\fi-360\li360\jclisttab\tx360 }{\listname ;}\listid-120}{\list\listtemplateid-1072253880
\listsimple{\listlevel\levelnfc23\leveljc0\levelfollow0\levelstartat1\levelspace0\levelindent0{\leveltext\'01\u-3913 ?;}{\levelnumbers;}\f3\fbias0 \fi-360\li360\jclisttab\tx360 }{\listname ;}\listid-119}{\list\listtemplateid-848401142\listsimple
{\listlevel\levelnfc255\leveljc0\levelfollow0\levelstartat1\levelold\levelspace0\levelindent360{\leveltext\'01\u-3929 ?;}{\levelnumbers;}\f1\fbias0 \fi-360\li1080 }{\listname ;}\listid1036081640}{\list\listtemplateid-848401142\listsimple{\listlevel
\levelnfc255\leveljc0\levelfollow0\levelstartat1\levelold\levelspace0\levelindent360{\leveltext\'01\u-3929 ?;}{\levelnumbers;}\f1\fbias0 \fi-360\li1080 }{\listname ;}\listid1279530914}{\list\listtemplateid-848401142\listsimple{\listlevel\levelnfc255
\leveljc0\levelfollow0\levelstartat1\levelold\levelspace0\levelindent360{\leveltext\'01\u-3929 ?;}{\levelnumbers;}\f1\fbias0 \fi-360\li1080 }{\listname ;}\listid1539513208}}{\*\listoverridetable{\listoverride\listid-119\listoverridecount0\ls1}
{\listoverride\listid-125\listoverridecount0\ls2}{\listoverride\listid-126\listoverridecount0\ls3}{\listoverride\listid-127\listoverridecount0\ls4}{\listoverride\listid-128\listoverridecount0\ls5}{\listoverride\listid-120\listoverridecount0\ls6}
{\listoverride\listid-129\listoverridecount0\ls7}{\listoverride\listid-130\listoverridecount0\ls8}{\listoverride\listid-131\listoverridecount0\ls9}{\listoverride\listid-132\listoverridecount0\ls10}{\listoverride\listid1036081640\listoverridecount0\ls11}
{\listoverride\listid1539513208\listoverridecount0\ls12}{\listoverride\listid1279530914\listoverridecount0\ls13}}{\info{\title Step-by-Step: Template}{\author Jonathan Lewis}{\operator Jonathan Lewis}{\creatim\yr2002\mo7\dy1\hr22\min21}
{\revtim\yr2002\mo7\dy1\hr22\min23}{\printim\yr2000\mo2\dy27\hr13\min4}{\version3}{\edmins3}{\nofpages10}{\nofwords2859}{\nofchars16300}{\*\company Oracle Corporation}{\nofcharsws20017}{\vern89}}
\widowctrl\ftnbj\aenddoc\lytprtmet\hyphcaps0\formshade\viewkind1\viewscale116\viewzk2\pgbrdrhead\pgbrdrfoot \fet0\sectd \linex0\headery709\footery709\colsx709\endnhere\sectdefaultcl {\*\pnseclvl1\pnucrm\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl2
\pnucltr\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl3\pndec\pnstart1\pnindent720\pnhang{\pntxta .}}{\*\pnseclvl4\pnlcltr\pnstart1\pnindent720\pnhang{\pntxta )}}{\*\pnseclvl5\pndec\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl6
\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl7\pnlcrm\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl8\pnlcltr\pnstart1\pnindent720\pnhang{\pntxtb (}{\pntxta )}}{\*\pnseclvl9\pnlcrm\pnstart1\pnindent720\pnhang
{\pntxtb (}{\pntxta )}}\pard\plain \s1\li720\sb240\sa60\keepn\widctlpar\outlinelevel0\adjustright \b\f1\fs28\kerning28\cgrid {Plan Stability in Oracle 8i/9i
\par }\pard\plain \s30\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \b\i\f1\fs20\cgrid {Find out how you can 
use "stored outlines" to improve the performance of an application even when you can't touch the source code, change the indexing, or fiddle with the configuration..
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {by Jonathan Lewis (jonathan@jlcomp.demon.co.uk)
\par }\pard\plain \s28\li720\ri2160\sb360\sa360\sl360\slmult1\widctlpar\box\brdrsh\brdrs\brdrw15\brsp20 \adjustright \f5\fs20\cgrid {\b Toolbox}{: For the purposes of experimentati
on, this article restricts itself to simple SQL and PL/SQL code running from an SQL*Plus session. The reader will need to have some privileges that a typical end-user would not normally be given, but otherwise need only be familiar with basic SQL. The art
icle starts with Oracle 8i, but moves on to Oracle 9I, where several enhancements have appeared in the generation and handling of }{\b\i stored outlines}{.
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {The back door to the Black Box.
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {If you are a DBA responsible for a 3}{\super rd}{ party application running on an Oracle 
database, you are sure to have experienced the frustration of finding a few extremely slow and costly SQL statements in the }{\b\i library_cache}{ that would be really easy to tune - if only you could add a few hints to the source code.
\par Starting from Oracle 8.1 you no longer need to rewrite the SQL to add the hints - you can make hints happen without touching the code. This feature is known as }{\b\i Stored Outlines}{, or }{\b\i Plan Stability}{
, and the concept is simple: you store information in the database that says: }{\i "if you see an SQL statement that looks like XXX then insert these hints in the following places"}{.
\par This actually gives you three possible benefits. First of all, you can optimize that handful of expensive statements. Secondly, if there are other statements that Oracle
 takes a long time to optimize (rather than execute), you can save time and reduce contention in the optimization stage. Finally it gives you an option for using the new }{\b\i cursor_sharing }{
parameter without paying the penalty of losing optimum execution paths.
\par There are a few issues to work around in Oracle 8 (largely eliminated in Oracle 9), but in general it is very easy to take advantage of this feature; and this article describes some of the things you can do.
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {Background / Overview
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {To demonstrate how to make best use of }{\b\i stored outlines}{
, we will start with a stored procedure with untouchable source code that (in theory) is running some extremely inefficient SQL.
\par We will see how we can trap the SQL and details of its current execution path in the database, find some hints to improve the performance of the SQL, then make Oracle use our hints whenever that SQL statement is run in the future.
\par In this demonstration, we will create a user, create a table in that user's schema, and create a procedure to access that table - but just for fun we will use the }{\b\i wrap}{
 utility on the procedure so that we can't reverse-engineer the code. We will then set ourselves the task of tuning the SQL executed by that procedure.
\par The demonstration will assume that the }{\b\i stored outline}{ infrastructure was installed automatically at database creation time.
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {Preliminary Setup
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {
Create a user with the privileges: create session, create table, create procedure, create any outline, and alter session. Connect as this user and run the following script to create a table:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {create table so_demo (
\par \tab n1\tab number,
\par \tab n2\tab number,
\par \tab v1\tab varchar2(10)
\par )
\par ;
\par 
\par insert into so_demo values (1,1,'One');
\par 
\par create index sd_i1 on so_demo(n1);
\par create index sd_i2 on so_demo(n2);
\par 
\par analyze table so_demo compute statistics;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Now you need the code to create a procedure to access this table. Create a script called }{\b\i c_proc.sql}{ containing the following:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {create or replace procedure get_value (
\par \tab i_n1\tab in\tab number,
\par \tab i_n2\tab in\tab number,
\par \tab io_v1\tab out\tab varchar2
\par )
\par as
\par begin
\par \tab select\tab v1
\par \tab into\tab io_v1
\par \tab from\tab so_demo
\par \tab where\tab n1 = i_n1
\par \tab and\tab n2 = i_n2
\par \tab ;
\par end;
\par /
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {You could simply execute this script to build the procedure of course but, just for effect, go to the operating system prompt and issue the command:
\par }\pard\plain \s20\li720\sa240\widctlpar\adjustright \f6\fs16\lang1024\cgrid {wrap iname=c_proc.sql
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {The response should be:
\par }\pard\plain \s20\li720\sa240\widctlpar\adjustright \f6\fs16\lang1024\cgrid {Processing c_proc.sql to c_proc.plb
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Instead of executing the }{\b\i c_proc.sql}{ script to generate the procedure, execute the incomprehensible }{\b\i c_proc.plb}{
 script and you will find that there is no trace of our target SQL statement anywhere in the }{\b\i user_source}{ view. 
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {What does the application want to do?
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Now that we have our pretend application we can run it, perhaps with }{\b\i sql_trace }{
switched on, to see what happens. It won't be a great surprise to discover that the SQL performs a full tablescan to get the required data.
\par In this little test, a full tablescan is probably the most efficient thing to do - but let us assume that we have proved that we get the best performance when Oracle uses an execution path that combines our single column indexes using the }{\b\i 
and-equal }{option. How can we make this happen without hinting the code ? 
\par With }{\b\i stored outlines}{, the answer is simple. There are actually several ways to achieve what I am about to do, so don't take this example as the definitive strategy.  Oracle is always improving features to
 make life easier, and the mechanism described here will no doubt become obsolete in a future release.
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {What do you want the application to do ?
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {There are three stages to making Oracle do what we want:
\par {\pntext\pard\plain\s24 \f1\fs20\lang1033\cgrid \loch\af1\dbch\af0\hich\f1 \u-3929\'3f\tab}}\pard\plain \s24\fi-360\li1080\sa240\nowidctlpar\tx360{\*\pn \pnlvlblt\ilvl0\ls11\pnrnot0\pnf1\pnstart1\pnindent360\pnhang{\pntxtb ?}}\ls11\adjustright 
\f1\fs20\cgrid {Start a new session and re-run the procedure, first telling Oracle that we want it to trap each incoming SQL statement, along with information about the path that the SQL took. These "paths" are our first example of }{\b\i stored outlines}
{.
\par {\pntext\pard\plain\s24 \f1\fs20\lang1033\cgrid \loch\af1\dbch\af0\hich\f1 \u-3929\'3f\tab}}\pard \s24\fi-360\li1080\sa240\nowidctlpar\tx360{\*\pn \pnlvlblt\ilvl0\ls12\pnrnot0\pnf1\pnstart1\pnindent360\pnhang{\pntxtb ?}}\ls12\adjustright {Create better }{
\b\i stored outlines }{for any problem SQL statements, and "exchange" the bad }{\b\i stored outlines }{with the good ones.
\par {\pntext\pard\plain\s24 \f1\fs20\lang1033\cgrid \loch\af1\dbch\af0\hich\f1 \u-3929\'3f\tab}}\pard \s24\fi-360\li1080\sa240\nowidctlpar\tx360{\*\pn \pnlvlblt\ilvl0\ls13\pnrnot0\pnf1\pnstart1\pnindent360\pnhang{\pntxtb ?}}\ls13\adjustright {
Start a new session and tell Oracle to start using the new }{\b\i stored outlines }{instead of using normal optimization methods when next it sees matching SQL; then run the procedure again. 
\par }\pard \s24\li720\sa240\nowidctlpar\tx360\adjustright {We have to keep stopping and starting new sessions to ensure that existing }{\b\i cursors }{are not kept open by the pl/sql cache. }{\b\i Stored outlines}{
 are only generated and/or applied when a cursor is parsed, so we have to make sure that pre-existing similar cursors are closed.
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {So start a session, and issue the command:
\par }\pard\plain \s20\li720\sa240\widctlpar\adjustright \f6\fs16\lang1024\cgrid {alter session set create_stored_outlines = demo;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Then run a little anonymous block to execute the procedure, for example:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {declare
\par \tab m_value\tab varchar2(10);
\par begin
\par \tab get_value(1, 1, m_value);
\par end;
\par /
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Then stop collecting execution paths (otherwise the next few bits of SQL that you run will also end up in the }{\b\i stored outline}{
 tables, making things harder to follow).
\par }\pard\plain \s20\li720\sa240\widctlpar\adjustright \f6\fs16\lang1024\cgrid {alter session set create_stored_outlines = false;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {To see the results of our activity, we can query the views that allow us to see details of the }{\b\i outlines }{that Oracle has created and stored for us:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {select name, category, used, sql_text
\par from user_outines
\par where category = 'DEMO';
\par 
\par NAME                           CATEGORY                       USED
\par ------------------------------ ------------------------------ ---------
\par SQL_TEXT
\par --------------------------------------------------------------------------------
\par SYS_OUTLINE_020503165427311    DEMO                           UNUSED
\par SELECT V1   FROM SO_DEMO  WHERE N1 = :b1  AND N2 = :b2
\par 
\par 
\par 
\par select\tab name, stage, hint
\par from\tab user_outline_hints
\par where\tab name = ' SYS_OUTLINE_020503165427311';
\par 
\par 
\par NAME                                STAGE HINT
\par ------------------------------ ---------- --------------------------------
\par SYS_OUTLINE_020503165427311             3 NO_EXPAND
\par SYS_OUTLINE_020503165427311             3 ORDERED
\par SYS_OUTLINE_020503165427311             3 NO_FACT(SO_DEMO)
\par SYS_OUTLINE_020503165427311             3 FULL(SO_DEMO)
\par SYS_OUTLINE_020503165427311             2 NOREWRITE
\par SYS_OUTLINE_020503165427311             1 NOREWRITE
\par 
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {We can see that there is a }{\b\i category }{named }{\b\i demo}{ that has only one }{\b\i stored outline}{, and looking at the }{\b\i sql_text }{for that }{\b\i 
outline}{ we can see something that is similar to, but not quite identical to, the SQL that exists in our original PL/SQL source. This is an important point as Oracle will only spot an opportunity to use a }{\b\i stored outline}{ if the stored }{\b\i 
sql_text}{ is a very close match to the SQL it is about to execute.  In fact under Oracle 8i the SQL has to be an }{\b\i exact }{match, and this was initially a big issue when experimenting with }{\b\i stored outlines}{. 
\par You can see from the listing that }{\b\i stored outlines }{are just a set of }{\b\i hints }{that describe the actions Oracle took (or will take) when it runs the SQL. 
This plan uses a full tablescan - and doesn't Oracle use a lot of hints to ensure the execution of something as simple as a full tablescan.  
\par Notice that a }{\b\i stored outline}{ always belongs to a category; in this case the }{\b\i demo}{ category, which we specified in our initial }{\b\i alter session }{command. If our original command had simply specified }{\b\i true}{ rather than }{\b\i 
demo}{ we would have found our }{\b\i stored outline}{ in a category named }{\b\i default}{.
\par }{\b\i Stored outlines }{also have names, and the names have to be unique across the entire database. No two }{\b\i outlines}{ can have the same name, even if different users generated them. In fact }{\b\i outlines}{
 do not have owners they only have creators. If you create a }{\b\i stored outline}{ which h
appens to match a piece of SQL that I subsequently execute, then Oracle will apply your list of hints to my text - even if those hints are meaningless in the context of my schema.  (This gives us a couple of completely different options for faking }{\b\i 
stored outlines}{ but that's another article).  You may note that when Oracle is automatically generating }{\b\i stored outlines}{, the names have a simple format that includes a timestamp to the nearest millisecond.
\par Moving on with the process of "tuning" our problem SQL, we decide that if we can inject the hint }{\b\i /*+ and_equal(so_demo, sd_i1, sd_i2) */}{ Oracle will use the execution path we want, so we now explicitly create a }{\b\i stored outline}{ as follows:

\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {create or replace outline so_fix
\par for category demo on
\par select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
\par from\tab so_demo
\par where\tab n1 = 1 
\par and\tab n2 = 2;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {This creates an explicitly named }{\b\i stored outline}{ called }{\b\i so_fix}{ in our }{\b\i demo }{category.  We can see what the }{\b\i stored outline}{
 looks like by repeating our queries against }{\b\i user_outlines}{ and }{\b\i user_outline_hints}{, with the predicate }{\b\i name = 'SO_FIX'}{. 
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {NAME                           CATEGORY                      USED
\par ------------------------------ ------------------------------ ---------
\par SQL_TEXT
\par --------------------------------------------------------------------------------
\par SO_FIX                         DEMO                           UNUSED
\par select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
\par from so_demo
\par where n1 = 1
\par and n2 = 2
\par 
\par }\pard \s18\widctlpar\adjustright {
\par }\pard \s18\li720\widctlpar\adjustright {NAME                                STAGE HINT
\par ------------------------------ ---------- --------------------------------
\par SO_FIX                                  3 NO_EXPAND
\par SO_FIX                                  3 ORDERED
\par SO_FIX                                  3 NO_FACT(SO_DEMO)
\par SO_FIX                                  3 AND_EQUAL(SO_DEMO SD_I1 SD_I2)
\par SO_FIX                                  2 NOREWRITE
\par SO_FIX                                  1 NOREWRITE
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Note, in particular that the line }{\b\i FULL(SO_DEMO)}{ has been replaced with a line }{\b\i AND_EQUAL(SO_DEMO SD_I1 SD_I2)}{, which is what we wanted to see.
\par And now we have to "swap" the two }{\b\i stored outlines}{ over. We want Oracle to use our new hint list whenever it sees the original text; and to do this, we have to cheat. The views }{\b\i user_outlines}{ and }{\b\i user_outline_hints}{
 are generated from two tables (}{\b\i ol$}{ and }{\b\i ol$hints}{ respectively) owned by the schema }{\b\i outln}{, and we are going to have to modify these tables directly; which means connecting to the database as }{\b\i outln}{
, or using an account with the privilege to update the tables. 
\par Fortunately, the }{\b\i outln }{tables do not have any enabled referential integrity constraints. Conveniently the relationship between the }{\b\i ol$}{ (}{\b\i outlines}{) table and the }{\b\i ol$hints}{ (}{\b\i hints}{
) table is defined by the name of the }{\b\i outline}{ (stored in column }{\b\i ol_name}{). So, checking names extremely carefully, we can exchange hints between }{\b\i stored outlines}{ by swapping names on the }{\b\i ol$hints}{ table, as follows:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {update outln.ol$hints
\par set ol_name = 
\par \tab decode(
\par \tab \tab ol_name,
\par \tab \tab \tab 'SO_FIX','SYS_OUTLINE_020503165427311',
\par \tab \tab \tab 'SYS_OUTLINE_020503165427311','SO_FIX'
\par \tab )
\par where ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX')
\par ;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {You may feel a little unc
omfortable with hacking something which is so close to the Oracle kernel, especially given the comments in the manuals - but this update is actually sanctioned in Metalink Note: 92202.1 Dated 5th June 2000. However, the note fails to mention that you may 
also need to do a second update to ensure that the numbers of }{\b\i hints }{associated with each }{\b\i stored outline}{ stays consistent. If you fail to do this, you may find that some of your }{\b\i stored outlines}{
 get damaged or destroyed on an export/import cycle.
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {update outln.ol$ ol1
\par set hintcount = (
\par \tab select\tab hintcount 
\par \tab from\tab ol$ ol2
\par \tab where\tab ol2.ol_name in ('SYS_OUTLINE_020503165427311',' SO_FIX')
\par \tab and\tab ol2.ol_name != ol1.ol_name
\par \tab )
\par where
\par \tab ol1.ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX')
\par ;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Once the }{\b\i exchange }{is complete you can connect to a new session, tell it to start using }{\b\i stored outlines}{, re-run the procedure and exit; again using }{
\b\i sql_trace }{to check what Oracle actually does with the SQL. The mechanism to tell Oracle to use the (hacked) }{\b\i stored outline }{is the command:
\par }\pard\plain \s20\li720\sa240\widctlpar\adjustright \f6\fs16\lang1024\cgrid {alter session set use_stored_outline = demo;
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Examining the trace file, you should find that the SQL now uses the }{\b\i and_equal }{path. (If you use }{\b\i tkprof }{
to process and explain the trace file you could well find that the output shows two contradictory paths. The first, correct, path should show the }{\b\i and_equal }{that took place, and the second path will probably show a full tablescan because the }{
\b\i stored outline }{may not be invoked as }{\b\i tkprof }{runs }{\b\i explain plan }{against the traced SQL).
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {From Development to Production.
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Now that we have managed to create a single outline, we need to transfer it into the production environment. There are numerous little features of }{\b\i 
stored outlines }{that help us. For example, we could rename the }{\b\i stored outline}{, export it from development, import it to the production system, check that it still works properly on production in a 'test' }{\b\i category}{
, and then move it into the production }{\b\i category}{. Useful commands are:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {alter outline SYS_OUTLINE_020503165427311 }{\b rename }{to AND_EQUAL_SAMPLE;
\par alter outline AND_EQUAL_SAMPLE }{\b change category}{ to PROD_CAT;
\par 
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {And to deal with exporting the outline from a development system to the production system, we can take advantage of the ability to add a }{\b\i where clause }{
to an export parameter files, so we might have an export parameter file:
\par }\pard\plain \s18\li720\widctlpar\adjustright \f6\fs16\lang1024\cgrid {userid=outln/outln
\par tables=(ol$, ol$hints, ol$nodes)\tab # ol$nodes exists in v9 only
\par file=so.dmp
\par consistent=y\tab \tab \tab \tab # very important
\par rows=yes
\par query='where ol_name  = ''AND_EQUAL_SAMPLE'''
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {Oracle 9 enhancements.
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {There are many other details to consider when getting to grips with }{\b\i stored outlines}{
, and in Oracle 8 there are some irritating and limiting features to what they can do and how they work. Fortunately, many of the issues are addressed in Oracle 9.
\par The most trivial and obvious deficiency is that a }{\b\i stored outline}{
 in Oracle 8 can only be used if the stored text matches the incoming text exactly. In Oracle 9, there is a 'normalization' effect that relaxes this matching requirement; the texts are converted to cap
itals and have white space stripped before comparison.  This increases the chance that marginally different pieces of SQL will be able to use the same }{\b\i stored outline}{.
\par There are also some issues with more complex execution paths involving multiple query blocks - these have been addressed in Oracle 9 by the introduction of a third table in the }{\b\i outln}{ schema called }{\b\i ol$nodes}{
. This helps Oracle to break down the list of hints in }{\b\i ol$hints}{ and cross-reference them with the correct sub-sections of the incoming SQL. 
This is, of course, a good thing. However, it may have some side effects on the strategy of swapping hints from one }{\b\i stored outline }{to another, as the }{\b\i ol$hints }{
table has also acquired various details of text length and offsets. When upgrading to Oracle 9, it will become necessary to use alternative methods for manufacturing }{\b\i stored outlines}{
, such as secondary schemas with specially crafted data sets,  or missing indexes, or stored views with embedded hints being used to substitute for tables named in the text.
\par Another feature of Oracle 9 is that there is more support for manufacturing }{\b\i stored outlines }{including the initial release of a package to allow you to edit }{\b\i stored outlines }{
by direct access.  More significantly though, there is an option to allow you to w
ork on plans stored in a production system with an improved degree of safety. Although no-one likes to experiment on production, sometimes the production system is the only place which has the correct data distribution and volume to allow you to determine
 the optimum path for a piece of SQL.  Under Oracle 9, you can create a private copy of the }{\b\i outln}{ tables, and extract "public" }{\b\i stored outlines}{
 into them for "private" experimentation, without running the risk of accidentally making one of your private }{\b\i stored outlines }{
visible to the end-user code. Personally I would consider this a last resort, but I could imagine that on occasion it might become a necessity. At a less dangerous level, if you have a full-scale UAT or development system, it is a feature that c
an be used to allow independence of testing 
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {Caveats
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {This article gives you enough information to start experimenting with }{\b\i stored outlines}{
; but there are a few points you must be aware of before you start applying the technology to a production system.  
\par First - on Oracle 8i, the default password for }{\b\i outln }{(the schema that owns the tables used to hold }{\b\i stored outlines}{) has a well-known password, and the account has a very dangerous privilege. You }{\b\i must }{
change the password on this account. On Oracle 9i, you should find that this account is locked.
\par Secondly - the tables used to hold }{\b\i stored outlines}{ are created in the }{\b\i system}{ tablespace. For a production system you could find that you are using a lot of space in the }{\b\i system }{tablespace when you start creating }{\b\i stored
 outlines}{. It is a good idea to move these tables, preferably to their own tablespace. Unfortunately, one of the tables includes a }{\b\i long }{column, so you will probably have to use }{\b\i exp/imp}{ to move the tables to a new tablespace.
\par Third - whilst }{\b\i stored outlines }{are extremely useful for solving critical performance problems, there is a cost involved. If }{\b\i stored outlines }{are activated then Oracle checks whether a relevant }{\b\i stored outline }{
exists every time a new statement is parsed. If there are large numbers of statements without a }{\b\i stored outline}{, then this overhead has to be balanced against the benefit you get on the few statements that do have }{\b\i stored outlines}{
. However, this is only likely to be an issue on a system that has other, more serious, performance problems.
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {Conclusion
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {\b\i Stored outlines}{ can be of enormous benefit. When you can't modify the source code or the indexing strategy, a }{\b\i stored outline}{
 may be the only way to make a 3}{\super rd}{ party application operate efficiently. 
\par Pushing the idea to the limit, if you still have to face the problem of switching a system from }{\b\i rule based}{ to }{\b\i cost based optimization}{, then }{\b\i stored outlines}{ may be your most cost-effective and risk-free option.
\par If you need to get the best out of }{\b\i stored outlines}{, then Oracle 9 has several enhancements that allow it to cover more classes of SQL, reduce the overheads, and allow you greater flexibility in testing, manipulating and installing }{\b\i 
stored outlines}{.
\par }\pard\plain \s2\li720\sb360\sa240\widctlpar\outlinelevel1\adjustright \b\f5\cgrid {Author's notes.
\par }\pard\plain \s19\li720\ri720\sb240\sl360\slmult1\widctlpar\adjustright \f1\fs20\cgrid {Jonathan Lewis (jonathan@jlcomp.demon.co.uk) is a freelance consultant with more t
han seventeen years experience in the design and trouble-shooting of Oracle database systems. He is author of the book "Optimising Oracle 8i" published by Addison-Wesley, designer and presenter of the seminar "Optimising Oracle - Performance by Design", a
nd runs the "Co-operative Oracle Users' FAQ" website at http://www.jlcomp.demon.co.uk/ind_faq.html.
\par 
\par }}