the final installment of this series, we will cover the basics of using Stored
Outlines and Oracle Application specific information.
14. Stored Outlines
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
outlines are global and apply to all identical statements, regardless of the
user firing it.
Setup for Stored Outlines
user OUTLN is created automatically during installation. This user should be
locked or password protected by the DBA.
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.
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.
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;
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
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;
option is also provided to test and use private outlines (CREATE PRIVATE
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
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.
following are some of the important procedures provided for maintenance.
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’);
Drops outlines that have never been used.
SQL> exec outln_pkg.drop_unused;
Drops all outlines in the specified category.
SQL> exec outln_pkg.drop_by_cat('AM_OLTP');