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');

Previous   Next

Back to DBAsupport.com