Query Optimizer  «Prev  Next»

Lesson 4 Using stored outlines
ObjectiveUse Stored Outlines to predetermine Optimizer Behavior

Use Stored Outlines to predetermine Optimizer Behavior

As a rule, you should accept the verdict of the query optimizer. Oracle developers have spent a great deal of time creating and refining the optimizer, and the cost-based optimizer can take into account a statistical profile of the data itself. However, because the cost-based optimizer continues to be enhanced by Oracle, the specific optimization applied to the same SQL query may change with subsequent changes to the optimizer. Oracle allows you to freeze the execution plan for a particular query through the use of a stored outline.

Stored outlines

A stored outline is essentially a set of hints that specify an access path for a SQL statement. Stored outlines are assigned to a particular category, which allow you to organize the stored outlines for different applications.
To create stored outlines, use the ALTER SESSION command shown in the following Tooltip:

ALTER SESSION 
SET CREATE_STORED_OUTLINE = category;
  1. Required keywords to turn on the collection of stored outlines
  2. A category name that allows stored outlines to be grouped into logical categories

Required Keywords Outlines
After enabling the creation of stored outlines, run the application.
As each SQL query is executed, an outline for the statement is stored. To turn off the creation of stored outlines, use the ALTER SESSION command with the keyword FALSE in place of the category name. Finally, to use the stored outlines you have created, use the ALTER SESSION command shown in the following Tooltip:

ALTER SESSION 
SET USE_STORED_OUTLINE= category;

Using Stored Outlines
To use stored outlines, you must also set OPTIMIZER_MODE to CHOOSE.
When Oracle encounters a SQL statement with the USE_STORED_OUTLINES parameter set to a category, it compares the text of the SQL statement with the text of the SQL statement used to create the outline. If there is an exact match, the stored outline is used.
You should use stored outlines only when you need to ensure consistent performance–not simply as a means to override the optimizer. In the next lesson, you will learn how database statistics, the basis of all cost calculations, are collected.