Database Reference
In-Depth Information
When to Use It
Whenever the default configuration isn't suitable for part of the application or part of the users, changing it is a good
thing. Although changing the initialization parameters at the session level should always be possible, hints can be
used only when it's also possible to change the SQL statements.
Pitfalls and Fallacies
Altering the execution environment at the session level is easy when the setting can be centralized either in the
database or in the application. Take extra care if you're using a connection pool shared by applications or modules
that need a different execution environment. In fact, session parameters are associated with the physical connection.
Because the physical connection might have been used by another application or module, you must set the execution
environment every time you get a connection from the pool (which is of course expensive, because additional round-trips
to the database are needed). To avoid this overhead, if you have applications or modules needing different execution
environments, you should use different connection pools using different users as well. In this way, you can have a
single configuration for each connection pool, and by defining different users to connect to the database, you may
possibly be able to centralize the configuration into a simple database trigger.
Altering the execution environment at the SQL statement level is also subject to the same pitfalls and fallacies
previously described for hints.
Stored Outlines
Stored outlines are designed to provide stable execution plans in case of changes in the execution environment or
object statistics. For this reason, this feature is also called
plan stability
. Two important scenarios that can benefit from
this feature are reported in the Oracle documentation. The first is the migration from the rule-based optimizer to the
cost-based optimizer. The second is the upgrade of an Oracle Database release to a newer one. In both cases, the idea
is to store information about the execution plans while the application is using the old configuration or version and
then use that information to provide the same execution plans against the newer one. In practice, unfortunately, even
with stored outlines in place, you may observe changes in execution plans. Probably for this reason, I've never seen a
single database where stored outlines were used on a large scale. Consequently, in practice, stored outlines are used
for specific SQL statements only.
■
From version 11.1 onward, stored outlines are deprecated in favor of
SQL plan management
(covered
later in this chapter).
Note
How It Works
The following sections describe what stored outlines are and how to work with them.
What Are Stored Outlines?
A stored outline is an object associated to a SQL statement and is designed to influence the query optimizer while
it generates an execution plan for the SQL statement. More concretely, a stored outline is a set of hints or, more
precisely, all the hints that are necessary to force the query optimizer to consistently generate a specific execution
plan for a given SQL statement.
