Creating Stored Outlines

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.