Oracle Database 12c新機能、適応計画(Adaptive Plan)のサブプランが発動した時／しなかった時の実行計画を DBMS_XPLAN.DISPLAY_CURSOR の FORMAT => 'ADAPTIVE' で比較してみる。

こっちがサブプラン発動時(HASH JOINに変更された時)の実行計画

```sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aqkqdv23rmnj7', NULL, 'ADAPTIVE ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  aqkqdv23rmnj7, child number 0
-------------------------------------
SELECT /*+ MONITOR */        DTL.*   FROM SALES        SAL      ,
SALES_DETAIL DTL  WHERE SAL.RECEIPT_NUM = DTL.RECEIPT_NUM    AND
TO_CHAR(SAL.SALES_DATE, 'YYYYMMDD')        = '20151101'

Plan hash value: 310527788

-------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |              |      1 |        |      1 |00:00:00.52 |    2982 |       |       |          |
|  *  1 |  HASH JOIN                    |              |      1 |      1 |      1 |00:00:00.52 |    2982 |    51M|  7163K|   69M (0)|
|-    2 |   NESTED LOOPS                |              |      1 |      1 |    870K|00:00:00.31 |    2965 |       |       |          |
|-    3 |    NESTED LOOPS               |              |      1 |      1 |    870K|00:00:00.23 |    2965 |       |       |          |
|-    4 |     STATISTICS COLLECTOR      |              |      1 |        |    870K|00:00:00.15 |    2965 |       |       |          |
|     5 |      TABLE ACCESS FULL        | SALES_DETAIL |      1 |      1 |    870K|00:00:00.06 |    2965 |       |       |          |
|- *  6 |     INDEX UNIQUE SCAN         | SALES_PK     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|- *  7 |    TABLE ACCESS BY INDEX ROWID| SALES        |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  *  8 |   TABLE ACCESS FULL           | SALES        |      1 |      1 |      1 |00:00:00.01 |      17 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
   6 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
   7 - filter(TO_CHAR(INTERNAL_FUNCTION("SAL"."SALES_DATE"),'YYYYMMDD')='20151101')
   8 - filter(TO_CHAR(INTERNAL_FUNCTION("SAL"."SALES_DATE"),'YYYYMMDD')='20151101')

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)
```

Noteに「(rows marked '-' are inactive)」の記載が有る通り、
NESTED LOOPS のステップは動作しないんやね彡(ﾟ)(ﾟ)


そしてこっちがサブプランが発動しなかった時(NESTED LOOPSのまま)の実行計画

```sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('aqkqdv23rmnj7', NULL, 'ADAPTIVE ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
SQL_ID  aqkqdv23rmnj7, child number 0
-------------------------------------
SELECT /*+ MONITOR */        DTL.*   FROM SALES        SAL      ,
SALES_DETAIL DTL  WHERE SAL.RECEIPT_NUM = DTL.RECEIPT_NUM    AND
TO_CHAR(SAL.SALES_DATE, 'YYYYMMDD')        = '20151101'

Plan hash value: 1292918998

-------------------------------------------------------------------------------------------------------------
|   Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT              |                 |      1 |        |      1 |00:00:00.01 |      22 |
|- *  1 |  HASH JOIN                    |                 |      1 |    300 |      1 |00:00:00.01 |      22 |
|     2 |   NESTED LOOPS                |                 |      1 |    300 |      1 |00:00:00.01 |      22 |
|     3 |    NESTED LOOPS               |                 |      1 |    300 |      1 |00:00:00.01 |      21 |
|-    4 |     STATISTICS COLLECTOR      |                 |      1 |        |      1 |00:00:00.01 |      17 |
|  *  5 |      TABLE ACCESS FULL        | SALES           |      1 |      1 |      1 |00:00:00.01 |      17 |
|  *  6 |     INDEX RANGE SCAN          | SALES_DETAIL_I1 |      1 |    300 |      1 |00:00:00.01 |       4 |
|     7 |    TABLE ACCESS BY INDEX ROWID| SALES_DETAIL    |      1 |    300 |      1 |00:00:00.01 |       1 |
|-    8 |   TABLE ACCESS FULL           | SALES_DETAIL    |      0 |    300 |      0 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")
   5 - filter(TO_CHAR(INTERNAL_FUNCTION("SALES_DATE"),'YYYYMMDD')='20151101')
   6 - access("SAL"."RECEIPT_NUM"="DTL"."RECEIPT_NUM")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)


33 rows selected.
```

動作しなかった HASH JOIN のとこに、
- がマークされてるやね彡(^)(^)