Using Stored Outlines

Stored outlines were introduced in Oracle8i as a means to guarantee that a certain execution plan would be used for a particular SQL statement. This is useful for those tuning efforts that want to guarantee a certain index will or will not be used. It is also useful for when tuning takes place on a different system or the tuning effort on one SQL statement is desired to be automatically repeated on other systems where the application resides.

To save a SQL statement in a stored outline, use the syntax CREATE OR REPLACE <outline name> FOR CATEGORY <category name> ON <SQL statement>. You must have the CREATE ANY OUTLINE permission granted to perform this task. The system or session parameter CREATE_STORED_OUTLINES must be set to true. Categories are used for multiple SQL statements for the same application, for example.

Oracle will use the stored outline if there is an exact match of the SQL text, including any HINT syntax that might be present.

Oracle uses two tables, OL$ and OL$HINTS, to store the stored outline reference and SQL text. The stored outlines will be stored in these tables and used indefinitely unless explicitly removed.

These stored outlines can easily be moved to other systems using Oracle Export/Import: EXP OUTLN/OUTLN FILE = <file name> TABLES = 'OL$' 'OL$HINTS' SILENT=Y [WHERE CATEGORY=<category>] and IMP OUTLN/OUTLN FILE=<file name> TABLES = 'OL$' 'OL$HINT' IGNORE=Y SILENT=Y.

Oracle9i has enhanced the stored outline environment by allowing you to make a private copy of any stored outline into your own schema, and making changes to it in this private, or offline stored outline. Oracle9I supports the OUTLN_PKG.CREATE_EDIT_TABLES package, which will create the private stored outline tables in your schema.

Listing 14.7 shows an example stored outline edit session.

Listing 14.7. Stored Outline Edit Session

SQL> ALTER SESSION SET USE_PRIVATE_OUTLINES = TRUE;
System Altered
SQL> CREATE PRIVATE OUTLINE mysql FROM mysql;
Outline Created
SQL> UPDATE OL$HINTS
2> SET HINT_TEXT = 'INDEX(T1 I1)'
3> WHERE HINT# = 6;
Record Updated