Question: How does optimizer plan
stability work? Is optimizer plan stability the same
as stored outlines?
Answer:
Optimizer plan stability (a.k.a. stored outlines): Stored
outlines were cumbersome to manage, and it was very
difficult to "swap" execution plans with plan stability.
Swap plans using stored outlines.
See this eBook for complete details on optimizer plan
stability. Also see my notes on
SQL plan management.
Optimizer plan stability has been
replaced b y SQL profiles but optimizer plan stability
is still useful for "freezing" all SQL execution plans
before applying a patch or upgrading an database release.
This storing of plan outlines for SQL statements is known as
optimizer 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 stored 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.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
