Database Reference
In-Depth Information
Notice that the
Note
section in the previous excerpt points out that the execution plan is adaptive. In the
execution plan itself, however, there's no sign of anything peculiar. The fact is that, by default, the
display
function of
the
dbms_xplan
package shows the
default execution plan
only. Simply put, this is the execution plan that the query
optimizer would choose without considering adaptive execution plans. If you want to see the
full execution plan
containing the subplans, you have to specify the
adaptive
modifier when using the
dbms_xplan
package. In this case,
three additional operations are shown in the execution plan:
SQL> SELECT * FROM table(dbms_xplan.display(format=>' basic +predicate +note +
adaptive
'));
PLAN_TABLE_OUTPUT
-------------------------------------------------
Plan hash value: 1837274416
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
|
- *
1
|
HASH JOIN
| |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
|
-
4
|
STATISTICS COLLECTOR
| |
| * 5 | TABLE ACCESS FULL | T1 |
| * 6 | INDEX UNIQUE SCAN | T2_PK |
| 7 | TABLE ACCESS BY INDEX ROWID| T2 |
|
-
8
|
TABLE ACCESS FULL
|
T2
|
-------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
5 - filter("T1"."N"=666)
6 - access("T1"."ID"="T2"."ID")
Note
-----
- this is an adaptive plan
(rows marked '-' are inactive)
Such an execution plan isn't very readable because, in fact, it contains two different execution plans. First, the
default execution plan based on a nested loops join:
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | T1 |
| 4 | INDEX UNIQUE SCAN | T2_PK |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 |
----------------------------------------------
