Oracle Database 12c 新機能、適応計画(Adaptive Plan)のサブプランが発動した時／しなかった時の実行計画を DBMS_XPLAN.DISPLAY_CURSOR の FORMAT => 'ADAPTIVE' で比較してみる。
こっちがサブプラン発動時(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 のとこに、
'-' がマークされてるやね彡(^)(^)
Qiita にも書いたやで彡(ﾟ)(ﾟ)
http://qiita.com/ora_gonsuke777/items/5c1aa84d3fc330c11d1e
