 |
|
Oracle Tips by Burleson |
Requirements for OUTLINE Use
The only privilege needed to create outlines
is the CREATE ANY OUTLINE privilege. However it is also useful to be
able to select from DBA_OUTLINES
To force a session to either use or not
create out lines you would issue the command:
ALTER
SESSION SET CREATE_STORED_OUTLINES = TRUE | FALSE | <category>
This command causes Oracle to automatically
create outlines for all SQL statements issued during the session. If
set to TRUE then the category name for the outlines is set to
DEFAULT.
Note: Category should not be quoted contrary
to documentation
To turn on or off the creation of stored
outlines at the system level issue the command:
ALTER
SYSTEM SET CREATE_STORED_OUTLINES = TRUE | FALSE | <category> [NOOVERRIDE]
This determines whether Oracle should
automatically create and store an outline for each query submitted
on the system. These outlines are stored in the DEFAULT category.
If a particular query already has an outline defined for it in the
DEFAULT category, that outline will remain and a new outline will
not be created.
The NOOVERRIDE option specifies that this
system setWhen set Oracle checks for a known stored plan based on an
address calculated from the SQL TEXT of the statement. If a plan
exists in the selected category then that plan will be used
(provided it is valid).
Some General Usage Notes:
-
Plan outlines are global: They apply to
all identical statements
-
Outlines, if present, will be used,
regardless of which user issues the statement.
-
Use of an outline is based on the SQL
TEXT being IDENTICAL
-
Use is NOT based on resolved names of
underlying objects so changing a synonym etc.. still uses the
outline (if it is valid)
-
When creating outlines outside the
application ensure:
-
SQL TEXT is identical character for
character
-
Binds should be of the expected type when creating the
outline to ensure the correct plan is obtained.
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
|
