SQL programming Guru Laurent Schneider found this tidbit in the Oracle
documentation; SQL profiles will soon officially deprecate the old-fashioned
stored outlines (a.k.a. optimizer plan stability):
Performance Tuning Guide
Stored outlines will be desupported in a future release in favor of SQL plan
management. In Oracle Database 11g Release 1 (11.1), stored outlines
continue to function as in past releases.
However, Oracle strongly
recommends that you use SQL plan management for new applications.
management creates SQL plan baselines, which offer superior SQL performance
and stability compared with stored outlines.
As a review,
stored outlines (optimizer plan stability) allow the DBA to ?freeze? SQL
execution plans, and more important, change execution plans without touching
the SQL source, a critical tool for tuning third-party vendor systems where
you cannot touch the source code.
Note: Implementing SQL profiles locks in a execution
plan and disables dynamic sampling for that SQL statement.
outline facility was tricky and clumsy, and it's great that SQL profiles are
replacing plan stability. See
11g SQL execution plan management tips for details on this powerful new
and Oracle9i, MOSC note 92202 .1 describes a procedure to tune SQL that
you cannot touch by performing these steps:
the sub-optimal SQL and create a stored outline
equivalent query with a faster execution plan and create a stored
bad stored outline for the tuned stored outline
provides this example for swapping the outlines:
OL_NAME IN ('HINTSQL','ORIGINALSQL');
10g automatic tuning advisor allowed us to implement tuning suggestions in
the form of SQL profiles that will improve performance, and SQL profiles can
be used the same way as stored outlines (optimizer plan stability). The SQL
Profile is a collection of the historical information of prior runs of the
SQL statement, comparison details of the actual and estimated cardinality
and predicate selectivity, etc.
A SQL Profile
is stored persistently in the data dictionary, so it does not require any
application code changes.
A SQL profile
helps generate a better execution plan than the normal optimization because
it is tested against a real-world workload in the SQL Tuning Set (STS).
Additional tasks like checking for advanced predicate selectivity,
correlation between columns, join skews, and complex predicates such as
functions, help in profiling the SQL statement. Once a SQL statement is
profiled and stored, differing execution plans can be invoked at will.