Oracle Optimizer: Moving to and working with CBO – Part 7

In
the final installment of this series, we will cover the basics of using Stored
Outlines and Oracle Application specific information.

14. Stored Outlines

Oracle
provides the option of changing the execution plan for running queries, without
changing the code itself. This feature also allows us to insulate our
application execution plans against any structural changes or upgrades. This feature is known as Stored Outlines and the idea is to
store the execution plan in Oracle provided tables, which will later be
referred to by the optimizer to execute the queries, thus providing optimizer
plan stability.

Stored
outlines are global and apply to all identical statements, regardless of the
user firing it.

Setup for Stored Outlines

The
user OUTLN is created automatically during installation. This user should be
locked or password protected by the DBA.

The
following System or session specific parameters are provided for creating and
using stored outlines. These are not initialization parameters and are not set
in the Initialization file.

CREATE_STORED_OUTLINES

Setting
this to TRUE or an appropriate category name will automatically enable creating
and storing of outlines for every subsequent query fired. A DEFAULT category is
used unless specified.

e.g.:

SQL> alter session set create_stored_outlines=true;

SQL> alter session set create_stored_outlines=AM_OLTP;
SQL> alter system set create_stored_outlines = AM_OLTP;
SQL> alter system set create_stored_outlines = false;

USE_STORED_OUTLINES

This
parameter enables the use of public stored outlines. Setting USE_STORED_OUTLINES
to TRUE causes the execution plan stored in the DEFAULT category to take
effect. Set the parameter to a proper category name to explicitly use a
different category.

e.g.:

SQL> alter session set use_stored_outlines=true;

SQL> alter session set use_stored_outlines=AM_OLTP;
SQL> alter system set use_stored_outlines = AM_OLTP;
SQL> alter system set use_stored_outlines = false;

This
option is also provided to test and use private outlines (CREATE PRIVATE
OUTLINE, USE_PRIVATE_OUTLINES)

The outlines present in the session’s private area are used rather
than the publicly stored outlines.

Optionally, stored outlines can be created for individual
statements with the CREATE OUTLINE command. This has to be used with care, as
the SQL statement provided should be identical to what is present in the
application.

e.g.:

SQL> variable b1 number;

SQL> create or replace outline run25 on

  2  select transaction_date, creation_date from mtl_material_transactions

  3  where transaction_id = :b1;

The package DBMS_OUTLN is used for managing stored outlines. This
is synonymous with the OUTLN_PKG package.

The
following are some of the important procedures provided for maintenance.

UPDATE_BY_CAT:
Changes all outlines from one category to another.

e.g.: moving all DEFAULT category outlines to AM_OLTP category.

SQL> exec outln_pkg.update_by_cat(‘DEFAULT’, ‘AM_OLTP’);

DROP_UNUSED:
Drops outlines that have never been used.

SQL> exec outln_pkg.drop_unused;

DROP_BY_CAT:
Drops all outlines in the specified category.

SQL> exec outln_pkg.drop_by_cat('AM_OLTP');