TUNINIG:Stored outlines for constant CBO

The CBO doesn't always use the same execution strategies. Changes in Oracle versions or changes in the initialization parameters concerning memory allocation may force the CBO to modify its plans. For example, changing the values of initialization parameters such as sort_area_size and bitmap_merge_area_size may sometimes cause Oracle to change its access paths. What if you like an existing execution plan and don't want the CBO to change it on its own? You can use Oracle's plan stability feature to ensure that the execution plan remains stable regardless of any changes in the database environment.
The plan stability feature uses stored outlines to preserve the current execution plans, even if the statistics and optimizer mode are changed. The CBO will use the same execution plan with identical access paths each time you execute the same query. The catch is that the query must be exactly identical each time if you want Oracle to use the stored plan.
On the face of it, the stored outline feature doesn't seem impressive. Let's consider a simple example to see how stored outline could be useful in a real production environment.
Suppose you have a system that is running satisfactorily and, due to a special need, you add an index to a table. The addition of the new index could unwittingly modify the execution plans of the CBO, and your previously fast-running SQL queries may slow down. It could conceivably take a lot of effort, testing, and time to fix the problem by changing the original query. However, if you had created stored outlines, these kinds of problems would not arise. Once Oracle creates an outline, it stores it until you remove it.
HOW TO Use Outlines:
Outlines are very useful when you're planning migrations from one version of Oracle to another. The CBO could behave differently in between versions, and you can cut your risk down by using stored outlines to preserve the application's present performance. You can also use them when you're upgrading your applications. Outlines ensure that the execution paths the queries used in a test instance will successfully carry over to the production instance.
Stored plan outlines can be public, in which case all users can use them. If, on the other hand, they're private, only selected users can use them. Unless you remove them explicitly, all stored outlines are stored permanently in the data dictionary.
Stored outlines are especially useful when the users of an application have information about the environment that the Oracle CBO doesn't possess. By enabling the direct editing of stored outlines, Oracle9i lets you tune SQL queries without changing the underlying application. This is especially useful when you're dealing with packaged applications where you can't get at the source code.
mplementing Plan Stability
Implementing plan stability is a simple matter. You have to ensure that the following initialization parameters are consistent in all the environments. The value of the three following parameters must be set to true:
Query_rewrite_enabled
Star_transformation_enabled
Optimizer_features_enable
Creating Outlines
The outlines themselves are managed through the DBMS_OUTLN and DBMS_OUTLN_EDIT Oracle packages. To create outlines for all your current SQL queries, you simple set the initialization parameter create_stored_outlines to true.
You must have noticed a user called OUTLN, who is part of the database when it is created. The OUTLN user owns the stored outlines in the database. The outlines are stored in the table OL$. Listing 18-1 shows the structure of the OL$ table.
Listing 18-1: The OL$ Table
SQL> desc OL$
Name Null? Type
-------------------- ------------ ------
OL_NAME VARCHAR2(30)
SQL_TEXT LONG
TEXTLEN NUMBER
SIGNATURE RAW(16)
HASH_VALUE NUMBER
HASH_VALUE2 NUMBER
CATEGORY VARCHAR2(30)
VERSION VARCHAR2(64)
CREATOR VARCHAR2(30)
TIMESTAMP DATE
FLAGS NUMBER
HINTCOUNT NUMBER
SPARE1 NUMBER
SPARE2 VARCHAR2(1000)
SQL>
The sql_text column has the SQL statement that is outlined. In addition to the OL$ table, the user OUTLN uses the OL$HINTS and OL$NODES tables to manage stored outlines.
Create a special tablespace for the user OUTLN and the tables OL$, OL$HINTS, and OL$NODES. By default, they are created in the System tablespace. After you create a new tablespace for user OUTLN, you can use the export/import utilities to move the tables.
Creating Outlines at the Instance Level
If you want outlines for all the SQL statements to be stored automatically, make the following changes in your initialization file, init.ora:
Create_Stored_Outlines=TRUE
You can also have Oracle create stored outlines for the entire database by issuing the following command:
SQL> alter system set create_stored_outlines = true;
System altered.
SQL>

Creating Outlines for Specific Statements
You can create outlines for a specific statement or a set of statements by altering the session or system in the following manner:
SQL> alter session set create_stored_outlines = true;
Session altered.
SQL>
Any statements you issue after the alter session statement is processed have outlines stored for them.
If you want to create a stored outline for a specific SQL statement, you can do so by using the create outline statement. The user issuing this command must have the create outline privilege. The following set of statements show to create and drop a simple outline for a select operation on table employees:
SQL> create outline test_outline
2* on select employee_id,last_name from hr.employees;
Outline created.
SQL> drop outline test_outline;
Outline dropped.
SQL>
Using the Stored Outlines
After you create the stored outlines, Oracle won't automatically start using them. You have to use the alter session or alter system command use_stored_outlines to enable the use of the stored outlines, as shown here:
SQL> alter system set use_stored_outlines=true;
System altered.
SQL>