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 in Oracle are a set of hints that can be used to determine a specific execution plan or access path for a SQL statement. They serve as a mechanism to preserve or "freeze" a desired execution plan for a specific SQL statement, allowing that plan to be consistently used whenever the associated SQL statement is executed. Stored outlines are created using the Oracle database's OUTLN_PKG package, or automatically when the system parameter CREATE_STORED_OUTLINES is set to true. Once an outline is created for a particular SQL statement, the Oracle optimizer will attempt to generate an execution plan that matches the outline, effectively overriding its normal cost-based calculations. The stored outline includes hints that dictate aspects such as:

1. Access Paths: These hints instruct the optimizer on how to access the data for a given SQL operation. They might direct the optimizer to perform a full table scan, use an index, or use a hash join, among other things.
2. Join Orders: These hints affect the order in which the optimizer joins tables in a SQL statement.
3. Join Methods: These hints direct the optimizer to use specific methods for joining tables, such as nested loops, sort merge joins, or hash joins.

By using stored outlines, database administrators can ensure consistent performance for specific SQL statements, even in the face of changes to the database statistics or system parameters. However, the use of stored outlines should be carefully considered, as they can potentially lead to suboptimal execution plans if the underlying data or database structures change significantly. They are typically used for performance stability in situations where preserving a known execution plan is of critical importance. Stored outlines in Oracle are a collection of hints that specify a particular execution plan for a SQL statement. By using stored outlines, Oracle can consistently reproduce a particular plan, thereby ensuring stable performance for that SQL statement over time. However, their use requires careful consideration and monitoring to avoid potential performance degradation.
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 diagram.

Location 1	Required keywords.
Location 2	Required keywords to turn on the collection of stored outlines.
Location 3	A category name that allows stored outlines to be grouped into logical categories.

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.

Oracle can force similar statements to share SQL by replacing literals with system-generated bind variables. This works with plan stability if the outline was generated using the CREATE_STORED_OUTLINES parameter, not the CREATE OUTLINE statement. Also, the outline must have been created with the CURSOR_SHARING parameter set to SIMILAR or FORCE, and the parameter must also set to SIMILAR or FORCE when attempting to use the outline. To manage stored outlines, use the OUTLN_PKG package, which gives you three capabilities:

1. Drop outlines that have never been used
2. Drop outlines within a specific category
3. Move outlines from one category to another

Each of these three capabilities has a corresponding procedure within OUTLN_PKG. To drop outlines that have never been used, execute the DROP_UNUSED procedure, as shown in the following listing:

execute OUTLN_PKG.DROP_UNUSED;

To drop all of the outlines within a category, execute the DROP_BY_CAT procedure, which has the name of the category as its only input parameter. The following example drops all of the outlines within the DEVELOPMENT category:

execute OUTLN_PKG.DROP_BY_CAT -
(cat => 'DEVELOPMENT');

To reassign outlines from an old category to a new category, use the UPDATE_BY_CAT procedure, as shown in the following example.

execute OUTLN_PKG.UPDATE_BY_CAT -
(old_cat => 'DEVELOPMENT', -
new_cat => 'TEST');

To drop a specific outline, use the drop outline command.
You can enable the usage of stored outlines by setting the USE_STORED_OUTLINES parameter to TRUE within your session (via alter session).

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.