Creating Stored Outlines - Part I

Stored outlines bring stability to your application's SQL statements. It is possible to make a better decision than the CBO, and then to want the application use your decision over the CBO's decision. Additionally, changes in the data distribution can alter an execution plan of a SQL statement, once the CBO has new statistics on the new data. Stored outlines are also great when working with third party applications, where you cannot touch the SQL statements, but you'd like to alter how they run. You can provide execution plan stability through the use of stored outlines.

To create a stored outline, you must have the CREATE ANY OUTLINE system privilege. Outlines are created in one of two ways, with the CREATE OUTLINE SQL statement, or with the CREATE_STORED_OUTLINES initialization parameter. Below is an example of creating a stored outline:

CREATE OR REPLACE OUTLINE emp_outline
FOR CATEGORY employee_application 
ON
SELECT * FROM emp WHERE empno = 1001;

Here, the CREATE OUTLINE command is used to create the outline named EMP_OUTLINE in the category EMPLOYEE_APPLICATION. The outline was created for the SQL statement previously defined. When this outline is created, whatever execution plan was used to process the given SQL statement will be stored as an outline. To change the execution plan for this particular SQL statement, play around with initialization parameters until the query runs as you want it. When a user activates this category of outlines and issues the above query, it will run with the execution plan stored in the outline.

Another way to create the same outline is with the CREATE_STORED_OUTLINES initialization parameter. For example:

ALTER SESSION SET create_stored_outlines=employee_application;
SELECT * FROM emp WHERE empno = 1001;
ALTER SESSION SET create_stored_outlines=FALSE;

When the session is modified, all the statements executed will have their execution plans stored as outlines for the category specified in the initialization parameter's value. Setting the parameter to FALSE stops collection of outlines for SQL statements in that session.

Moving Stored Outlines

The OUTLN user in the database contains the repository of your stored outlines. To move the outline from one database to another, simply export the OL$ and OL$HINTS tables and then import them to another database.

exp userid=outln/outln file=outln.dmp tables=(ol$,ol$hints)

Transfer the dump file to the other database server and import the contents:

imp userid=outln/outln file=outln.dmp full=y ignore=y

This process will transfer all stored outlines from one database to another. The entire contents of the dump file (FULL=Y) were imported. The IGNORE=Y parameter is used since the OL$ and OL$HINTS tables already exist in the destination database.

If you do not want to transfer all outlines, use the WHERE parameter of the export utility to export the stored outlines for a specific category. To export the stored outlines of the EMPLOYEE_APPLICATION category only, invoke the export utility as follows:

exp userid=outln/outln file=outln.dmp tables=(ol$,ol$hints)
	query="where category='employee_application'"

One dump file can be created for each stored outline category. This is a very nice way of backing up outlines.

Application developers should get used to the idea of creating stored outlines to promote plan stability. This way, your application will run as you intended it to run. Part of the application installation procedure would be to add the stored outline to the database. The application would automatically set the USE_STORED_OUTLINES parameter (described in part 2 of this feature) to begin using that outline.

Part 2 of this article will cover administering stored outlines. Part 2 will be published in the June 2005 Pipeline Newsletter.