Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 27, 2004

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

By Amar Kumar Padhi

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.


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.

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;


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.

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.

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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM