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 ...

Toolbox: For the purposes of experimentation, 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 article starts with Oracle 8i, but moves on to Oracle 9I, where several enhancements have appeared in the generation and handling of stored outlines.

The Back Door to the Black Box

If you are a DBA responsible for a 3rd-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 library_cache that would be really easy to tune -- if only you could add a few hints to the source code.

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 Stored Outlines, or Plan Stability, and the concept is simple: you store information in the database that says: "if you see an SQL statement that looks like XXX then insert these hints in the following places>"

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 cursor_sharing parameter without paying the penalty of losing optimum execution paths.

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.

Background / Overview

To demonstrate how to make best use of stored outlines, we will start with a stored procedure with untouchable source code that (in theory) is running some extremely inefficient SQL.

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.

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 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.

The demonstration will assume that the stored outline infrastructure was installed automatically at database creation time.

Preliminary Setup

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:

create table so_demo (
	n1	number,
	n2	number,
	v1	varchar2(10)
)
;

insert into so_demo values (1,1,'One');

create index sd_i1 on so_demo(n1);
create index sd_i2 on so_demo(n2);

analyze table so_demo compute statistics;

Now you need the code to create a procedure to access this table. Create a script called c_proc.sql containing the following:

create or replace procedure get_value (
	i_n1	in	number,
	i_n2	in	number,
	io_v1	out	varchar2
)
as
begin
	select	v1
	into	io_v1
	from	so_demo
	where	n1 = i_n1
	and	n2 = i_n2
	;
end;
/

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:

wrap iname=c_proc.sql

The response should be:

Processing c_proc.sql to c_proc.plb

Instead of executing the c_proc.sql script to generate the procedure, execute the incomprehensible c_proc.plb script and you will find that there is no trace of our target SQL statement anywhere in the user_source view.

What Does the Application Want to Do?

Now that we have our pretend application we can run it, perhaps with 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.

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 and-equal option. How can we make this happen without hinting the code?

With 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.

What Do You Want the Application to Do?

There are three stages to making Oracle do what we want:

• 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 stored outlines.
• Create better stored outlines for any problem SQL statements, and “exchange” the bad stored outlines with the good ones.
• Start a new session and tell Oracle to start using the new stored outlines instead of using normal optimization methods when next it sees matching SQL; then run the procedure again.

We have to keep stopping and starting new sessions to ensure that existing cursors are not kept open by the pl/sql cache. 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.

So start a session, and issue the command:

alter session set create_stored_outlines = demo;

Then run a little anonymous block to execute the procedure, for example:

declare
        m_value varchar2(10);
begin
        get_value(1, 1, m_value);
end;
/         

Then stop collecting execution paths (otherwise the next few bits of SQL that you run will also end up in the stored outline tables, making things harder to follow).

alter session set create_stored_outlines = false;

To see the results of our activity, we can query the views that allow us to see details of the outlines that Oracle has created and stored for us:

select name, category, used, sql_text
from user_outines
where category = 'DEMO';

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ -------
SQL_TEXT
------------------------------------------------------------------------------
SYS_OUTLINE_020503165427311    DEMO                           UNUSED
SELECT V1   FROM SO_DEMO  WHERE N1 = :b1  AND N2 = :b2

select	name, stage, hint
from	user_outline_hints
where	name = ' SYS_OUTLINE_020503165427311';


NAME                                STAGE HINT
------------------------------ ---------- ------------------------------
SYS_OUTLINE_020503165427311             3 NO_EXPAND
SYS_OUTLINE_020503165427311             3 ORDERED
SYS_OUTLINE_020503165427311             3 NO_FACT(SO_DEMO)
SYS_OUTLINE_020503165427311             3 FULL(SO_DEMO)
SYS_OUTLINE_020503165427311             2 NOREWRITE
SYS_OUTLINE_020503165427311             1 NOREWRITE

We can see that there is a category named demo that has only one stored outline, and looking at the sql_text for that 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 stored outline if the stored 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 exact match, and this was initially a big issue when experimenting with stored outlines.

You can see from the listing that stored outlines are just a set of 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.

Notice that a stored outline always belongs to a category; in this case the demo category, which we specified in our initial alter session command. If our original command had simply specified true rather than demo we would have found our stored outline in a category named default.

Stored outlines also have names, and the names have to be unique across the entire database. No two outlines can have the same name, even if different users generated them. In fact outlines do not have owners, they only have creators. If you create a stored outline that happens 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 stored outlines but that's another article). You may note that when Oracle is automatically generating stored outlines, the names have a simple format that includes a timestamp to the nearest millisecond.

Moving on with the process of "tuning" our problem SQL, we decide that if we can inject the hint /*+ and_equal(so_demo, sd_i1, sd_i2) */ Oracle will use the execution path we want, so we now explicitly create a stored outline as follows:

create or replace outline so_fix
for category demo on
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from    so_demo
where   n1 = 1 
and         n2 = 2; 

This creates an explicitly named stored outline called so_fix in our demo category. We can see what the stored outline looks like by repeating our queries against user_outlines and user_outline_hints, with the predicate name = 'SO_FIX'.

NAME                           CATEGORY                      USED
------------------------------ ------------------------------ ---------
SQL_TEXT
---------------------------------------------------------------------------
SO_FIX                         DEMO                           UNUSED
select /*+ and_equal(so_demo, sd_i1, sd_i2) */ v1
from so_demo
where n1 = 1
and n2 = 2


NAME                                STAGE HINT
------------------------------ ---------- --------------------------------
SO_FIX                                  3 NO_EXPAND
SO_FIX                                  3 ORDERED
SO_FIX                                  3 NO_FACT(SO_DEMO)
SO_FIX                                  3 AND_EQUAL(SO_DEMO SD_I1 SD_I2)
SO_FIX                                  2 NOREWRITE
SO_FIX                                  1 NOREWRITE

Note, in particular that the line FULL(SO_DEMO) has been replaced with a line AND_EQUAL(SO_DEMO SD_I1 SD_I2), which is what we wanted to see.

And now we have to "swap" the two 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 user_outlines and user_outline_hints are generated from two tables (ol$ and ol$hints respectively) owned by the schema outln, and we are going to have to modify these tables directly; which means connecting to the database as outln, or using an account with the privilege to update the tables.

Fortunately, the outln tables do not have any enabled referential integrity constraints. Conveniently, the relationship between the ol$ (outlines) table and the ol$hints (hints) table is defined by the name of the outline (stored in column ol_name). So, checking names extremely carefully, we can exchange hints between stored outlines by swapping names on the ol$hints table, as follows:

update outln.ol$hints
set ol_name = 
	decode(
		ol_name,
			'SO_FIX','SYS_OUTLINE_020503165427311',
			'SYS_OUTLINE_020503165427311','SO_FIX'
	)
where ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX')
;

You may feel a little uncomfortable with hacking something that 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 hints associated with each stored outline stays consistent. If you fail to do this, you may find that some of your stored outlines become damaged or destroyed on an export/import cycle.

 update outln.ol$ ol1
set hintcount = (
	select	hintcount 
	from	ol$ ol2
	where	ol2.ol_name in ('SYS_OUTLINE_020503165427311',' SO_FIX')
	and	ol2.ol_name != ol1.ol_name
	)
where
	ol1.ol_name in ('SYS_OUTLINE_020503165427311','SO_FIX')
;

Once the exchange is complete you can connect to a new session, tell it to start using stored outlines, re-run the procedure and exit; again using sql_trace to check what Oracle actually does with the SQL. The mechanism to tell Oracle to use the (hacked) stored outline is the command:

alter session set use_stored_outlines = demo;

Examining the trace file, you should find that the SQL now uses the and_equal path. (If you use 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 and_equal that took place, and the second path will probably show a full tablescan because the stored outline may not be invoked as tkprof runs explain plan against the traced SQL).

From Development to Production

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 stored outlines that help us. For example, we could rename the stored outline, export it from development, import it to the production system, check that it still works properly on production in a 'test' category, and then move it into the production category. Useful commands are:

alter outline SYS_OUTLINE_020503165427311 rename to AND_EQUAL_SAMPLE;
alter outline AND_EQUAL_SAMPLE change category to PROD_CAT;

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 where clause to an export parameter files, so we might have an export parameter file:

userid=outln/outln
tables=(ol$, ol$hints, ol$nodes)	# ol$nodes exists in v9 only
file=so.dmp
consistent=y			        	# very important
rows=yes
query='where ol_name  = ''AND_EQUAL_SAMPLE'''

Oracle 9 Enhancements

There are many other details to consider when getting to grips with 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.

The most trivial and obvious deficiency is that a 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 capitals and have white space stripped before comparison. This increases the chance that marginally different pieces of SQL will be able to use the same stored outline.

There are also some issues with more complex execution paths involving multiple query blocks -- Oracle Corp. has addressed these in Oracle 9 by introducing a third table in the outln schema called ol$nodes. This helps Oracle to break down the list of hints in 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 stored outline to another, as the 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 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.

Another feature of Oracle 9 is that there is more support for manufacturing stored outlines including the initial release of a package to allow you to edit stored outlines by direct access. More significantly though, there is an option to allow you to work 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 that 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 outln tables, and extract "public" stored outlines into them for "private" experimentation, without running the risk of accidentally making one of your private 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 can be used to allow independence of testing

Caveats

This article gives you enough information to start experimenting with stored outlines; but there are a few points you must be aware of before you start applying the technology to a production system.

First -- on Oracle 8i, the default password for outln (the schema that owns the tables used to hold stored outlines) has a well-known password, and the account has a very dangerous privilege. You must change the password on this account. On Oracle 9i, you should find that this account is locked.

Second -- the tables used to hold stored outlines are created in the system tablespace. For a production system, you could find that you are using a lot of space in the system tablespace when you start creating stored outlines. It is a good idea to move these tables, preferably to their own tablespace. Unfortunately, one of the tables includes a long column, so you will probably have to use exp/imp to move the tables to a new tablespace.

Third -- while stored outlines are extremely useful for solving critical performance problems, there is a cost involved. If stored outlines are activated, then Oracle checks whether a relevant stored outline exists every time a new statement is parsed. If there are large numbers of statements without a stored outline, then this overhead has to be balanced against the benefit you get on the few statements that do have stored outlines. However, this is only likely to be an issue on a system that has other, more serious, performance problems.

Conclusion

Stored outlines can be of enormous benefit. When you can't modify the source code or the indexing strategy, a stored outline may be the only way to make a 3rd party application operate efficiently.

Pushing the idea to the limit, if you still have to face the problem of switching a system from rule-based to cost-based optimization, then stored outlines may be your most cost-effective and risk-free option.

If you need to get the best out of 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 stored outlines.

--

Jonathan Lewis is a freelance consultant with more than 17 years' experience in Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of Practical Oracle 8i - Building Efficient Databases published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, tutorials, and seminars can be found at www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups.