 |
|
Oracle Tips by Burleson |
General Facts about OUTLN
Schema
The schema OUTLN owns the package OUTLN_PKG
that is used to manage stored outlines and their outline categories.
The database administrator should change the password for the OUTLN
schema just as for the SYS and SYSTEM schemas. OUTLINEs are not
available in the STANDARD release of Oracle only in the ENTERPRISE
release.
The "c0800050.sql" upgrade script from 8.0.5
to 8.1.x also creates the schema OUTLN.
The package outln_pkg is created by script "dbmsol.sql"
in the $ORACLE_HOME/rdbms/admin directory. The "dbmsol.sql" script
is called from "catproc.sql". The file "prvtol.plb" creates the
body of "outln_pkg"; it is also called from catproc.
There are other tables (base tables),
indexes, grants, and synonyms related to this package created during
the install process by the SQL.BSQ script.
After carefully tuning an application, you
might want to ensure that the optimizer generates the same execution
plan whenever the same SQL statements are executed. This is
accomplished via OUTLINEs. OUTLINEs can be generated in either the
rule or cost based optimizer. Plan stability allows you to maintain
the same execution plans for the same SQL statements, regardless of
changes to the database such as re-analyzing tables, adding or
deleting data, modifying a table's columns, constraints, or indexes,
changing the system configuration, or even upgrading to a new
version of the optimizer.
The CREATE OUTLINE statement creates a
stored outline, which contains a set of attributes that the
optimizer uses to create an execution plan. Stored outlines can also
be created automatically by setting the system parameter
CREATE_STORED_OUTLINES to TRUE.
The
system parameter USE_STORED_OUTLINES can be set to TRUE, FALSE, or
a category name to indicate whether to make use of existing stored
outlines for queries that are being executed. The OUTLN_PKG package
provides procedures used for managing stored outlines.
This is an excerpt by
Mike Ault’s e-book:
Using Oracle SQL Stored Outlines & Optimizer Plan Stability
ISBN:
0-9740716-8-4
http://www.rampant-books.com/ebook_plan_stability.htm
|
