 |
|
Oracle Tips by Burleson |
Views Used With OUTLINES
These views are defined by the script
catol.sql:
VIEW
DESCRIPTION
USER_OUTLINES
Shows all OUTLINEs owned by user
ALL_OUTLINES
Shows all OUTLINEs accessible by user
DBA_OUTLINES
Shows all OUTLINEs defined in system
USER_OUTLINE_HINTS
Shows hints for all users OUTLINEs
ALL_OUTLINE_HINTS
Shows hints for all OUTLINES accessible by
user
DBA_OUTLINE_HINTS
Shows hints for all OUTLINEs in system
All of these views are based on the tables
OUTLN.OL$ and OUTLN.OL$HINTS
Packages Used with OUTLINEs
Packages are defined in the script
dbmsol.sql.
PACKAGE
DESCRIPTION
DBMS_OUTLN (actually OUTLN_PKG)
Used To mange outlines
DBMS_OUTLN_EDIT (actually OUTLN_EDIT_PKG)
Used to edit outlines
Plan Stability
This storing of plan outlines for SQL
statements is known as plan stability and insures that changes in
the Oracle environment don't affect the way a SQL statement is
optimized by the cost based optimizer. If you wish, Oracle will
define plans for all issued SQL statements at the time they are
executed and this stored plan will be reused until altered or
dropped. Generally I do not suggest using the automatic outline
feature as it can lead to poor plans being reused by the optimizer.
It makes more sense to monitor for high cost statements and tune
them as required, storing an outline for them only once they have
been properly tuned.
As with the storage of SQL in the shared
pool, storage of outlines depends on the statement being reissued in
an identical fashion each time it is used. If even one space is out
of place the stored outline is not reused. (Note: In Oracle9i excess
white space is cleaned from SQL before use, so this limit is only
for pre-9i databases.) Therefore your queries should be stored as
PL/SQL procedures, functions or packages (or perhaps Java routines)
and bind variables should always be used. This allows reuse of the
stored image of the SQL as well as reuse of stored outlines.
Remember that to be useful over the life of an application the
outlines will have to be periodically verified by checking SQL
statement performance. If performance of SQL statements degrades the
stored outline may have to be dropped and regenerated after the SQL
is re-tuned.
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
|
