Optimizer and Query Improvements

Optimizer and Query Improvements

Optimizer Plan Equivalence and Stored Plans

Stored plans are also known as stored outlines. Execution plans are generated from the outlines and used to provide stability for SQL execution performance despite changes to system configuration or statistics values. A stored plan is mostly a set of hints that cause a SQL statement to use the same execution plan each time it is run.
Stored plans can also be used to help with migration from the rule-based optimizer to the cost-based optimizer.
Plan Stability needs to exactly match the text of the SQL statement to use the stored outline. Oracle compares cursors in the shared pool to the stored outline.
Use bind variables in applications to avoid problems with literal substitutions causing mismatches between a SQL statement and its corresponding stored outline.
CREATE_STORED_OUTLINES Parameter – Can be set to TRUE or a category name. Will create outlines in the DEFAULT category if set to TRUE or the category specified. Rely on hints that primarily use the cost-based optimizer. Set parm using ALTER SESSION or ALTER SYSTEM command (SESSION and INSTANCE level).
USE_STORED_OUTLINES Parameter – Can be set to TRUE or FLASE. Instructs Oracle to use or not use stored outlines. Set parm using ALTER SESSION or ALTER SYSTEM commands (SESSION and INSTANCE level).
CREATE OUTLINE statement – SQL statement to create a stored outline.
ALTER OUTLINE statement – SQL statement to change an outline category, rename an outline or rebuild an outline.
The new OUTLN userid is created when a database is created. This user owns the stored outlines. This user owns the OL$ table which contains the outline name, statement text, category, and the creation date. This user also owns the OL$HINTS table which contains the hints for the outlines in the OL$ table.
Can also view outlines in the DBA_OUTLINES, DBA_OUTLINE_HINTS, USER_OUTLINES and USER_OUTLINE_HINTS views.
OUTLN_PKG package can be used to manage outlines. Procedures include:
DROP_UNUSED - Drops outlines not used since created.
DROP_BY_CAT - Drops outlines assigned to a specific category name.
UPDATE_BY_CAT - Moves outlines from one category to another.
When configuring a hybrid system with different requirements during daytime processing vs. nighttime processing, you can create two different stored outlines in different categories.
The first step in ensuring a SQL statement uses the same execution plan each time is to create a stored outline for the SQL statement using the CREATE OUTLINE command.
Stored outlines can be easily transferred from one database to another by exporting and importing the schema that is owned by the OUTLN userid. All stored outlines are stored in the OUTLN schema.