|
 |
|
Oracle Tips by Burleson |
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.
|
