LoginSignup
1

More than 5 years have passed since last update.

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

Last updated at Posted at 2016-09-16

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

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のまま)の実行計画

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 のとこに、
- がマークされてるやね彡(^)(^)

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1