Database Reference
In-Depth Information
Next, an alternative execution plan based on a hash join:
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
Basically, the first execution plan is better than the second one when the scan of the
t1
table returns a small
number of rows. Hence, to decide which execution plan should be used, the query optimizer estimates the maximum
number of rows (called
inflection point
) that can be efficiently processed with the nested loops join. To determine,
during the execution phase, which execution plan has to be used, the
STATISTICS COLLECTOR
operation buffers and
counts the number of rows the scan of the
t1
table returns. Then—and only if the number is lower than the inflection
point—is the nested loops join executed. Otherwise, the hash join is executed. The execution plan that is actually
used is called
final execution plan
. Once the final execution plan has been determined, the
STATISTICS COLLECTOR
operation is disabled and, therefore, no further buffering takes place. In addition, the operations related to the
inefficient join method are also disabled.
■
Be aware that the execution plan switch is only performed during the first execution of a child cursor.
all successive executions use the final execution plan.
Note
The
v$sql
dynamic performance view provides a new column to help you know whether, for a specific child
cursor, the final execution plan was already selected. That column is
is_resolved_adaptive_plan
. It's set to one of
the following values:
NULL
means that the execution plan associated to the cursor isn't adaptive.
•
N
means that the final execution plan hasn't been determined. This value can be observed
only until the final execution plan has been determined.
•
Y
means that the final execution plan was determined.
•
Two initialization parameters control adaptive execution plans:
optimizer_adaptive_features
fully enables or disables the feature. Adaptive execution plans
are disabled when the parameter is set to
FALSE
. The default value is
TRUE
.
•
optimizer_adaptive_reporting_only
enables or disables adaptive execution plans in
reporting mode. This mode can be useful to assess whether an execution plan would
change because of adaptive execution plans. When set to
TRUE
, adaptive execution plans are
generated, and the SQL engine checks the inflection point, but the SQL engine uses only the
default execution plan. Then, with the reporting feature shown in the following example, you
can check which execution plan would be used if the feature were to be fully enabled. The
default value is
FALSE
:
•
SQL> ALTER SESSION SET
optimizer_adaptive_reporting_only = TRUE
;
