Databases Reference
In-Depth Information
To avoid such a situation, where the execution plan depends upon the first value provided
to bind variables, another feature was introduced in Oracle Database 11
g
, called Adaptive
Cursor Sharing. This feature allows the optimizer to identify how an SQL statement can be
satisfied by a different execution plan when different values for bind variables are used, as
in our example.
Adaptive Cursor Sharing is enabled by default. In
OnLine
Transaction
Processing
(
OLTP
) databases, there is often only one optimal
execution plan, regardless of the actual value of binding variables. In
this kind of database, we probably want to disable the Adaptive Cursor
Sharing feature to eliminate the related overhead. To disable it, you
need to set the
CURSOR_SHARING
initialization parameter to
EXACT
and the hidden
_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL
initialization parameter to
NONE
, as follows:
ALTER
SYSTEM
SET
CURSOR_SHARING
=
EXACT
SCOPE
=
BOTH;
ALTER
SYSTEM
SET
_OPTIMIZER_EXTENDED_CURSOR_
SHARING_REL
=
NONE
SCOPE
=
BOTH;
With Adaptive Cursor Sharing multiple execution plans can be stored for a single SQL
statement, resulting in the best plan for every value provided.
Please note that the SQL*Plus auto-trace feature is not aware of this situation, so the
execution plan returned was the same.
TKPROF
, on the other hand, is more detailed and
should be used whenever we want to seriously tune a statement or investigate what really
happens under the hood.
See also
F
For bind variables, see the
Using
Bind
Variables
recipe in
Chapter
4
,
Optimizing SQL
Code
, and the
Using
Bind
Variables
and
Parsing
recipe in
Chapter
6
,
Optimizing PL/
SQL Code
F
For detailed information on the use of statistics to help the optimizer, see the
Collecting
statistics
and
Using
histograms
recipes in this chapter
F
For details about using TKPROF, see the
Tracing
SQL
Activity
recipe in
Chapter
4
,
Optimizing SQL Code
Creating SQL Tuning Sets
In this recipe, we will see how we can store a group of SQL statements along with their
execution context and statistics, obtaining a so-called SQL Tuning Set.
