standingzzz's Oracle memo

OracleSQLチューニングの豊富な経歴を生かして、主に11gと12cのオプティマイザ動作の違いなどについて、実用的な検証結果をメモしています。

Oracle12c OPTIMIZER_FEATURESによるNOT EXISTS句の実行計画の違いを確認する その2

概要

OPTIMIZER_FEATURESによるNOT EXISTS句の実行計画の違いを確認します。

その1ではSELECT句をcount(*)のみにしたことで、
12.2.0.1のELIMINATE_JOINにより、tab_bのJOINが省略される実行計画となりましたので、
tab_bのカラムをSELECT句に入れたSQLで確認してみます。

検証のベースとなるSQL

select max(a.cdate)
     , max(b.cdate)
  from tab_a a
  left join tab_b b
    on b.id = a.id
   and b.cdate > sysdate - 1/24
 where not exists ( select 1
                      from tab_b b2
                     where b2.id = a.id
                       and mod( b2.id2, 10 ) = 1
                       and b2.status is null
                  )
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
CentOS Linux release 7.3.1611 (Core) 

検証

実行計画の確認
12.2.0.1
NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
optimizer_features_enable            string       12.2.0.1
MAX(A.CDATE)        MAX(B.CDATE)
------------------- -------------------
2017/09/08 15:02:54

1 row selected.

Elapsed: 00:00:04.85

Execution Plan
----------------------------------------------------------
Plan hash value: 2392446093

----------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |    73 |       |  4799   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE        |       |     1 |    73 |       |            |          |
|*  2 |   HASH JOIN RIGHT OUTER|       |  1301K|    90M|       |  4799   (2)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL   | TAB_B |    36 |   792 |       |   908   (3)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT ANTI|       |  1301K|    63M|  4192K|  3887   (1)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL  | TAB_B |   104K|  2964K|       |   895   (1)| 00:00:01 |
|   6 |     TABLE ACCESS FULL  | TAB_A |  1301K|    27M|       |   689   (2)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("B"."ID"(+)="A"."ID")
   3 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667
              )
   4 - access("B2"."ID"="A"."ID")
   5 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
       8971  consistent gets
          0  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

当然ELIMINATE_JOINは出現せず、ごく当たり前の実行計画となりました。
ですが、全てのテーブルをFULLSCANしてHASHJOINするという、
ちょっと乱暴な実行計画になっているので、ヒント句でNestedLoopにします。

  1  select /*+ use_nl(a b) */
  2         max(a.cdate)
  3       , max(b.cdate)
  4    from tab_a a
  5    left join tab_b b
  6      on b.id = a.id
  7     and b.cdate > sysdate - 1/24
  8   where not exists ( select /*+ use_nl(b2) */
  9                             1
 10                        from tab_b b2
 11                       where b2.id = a.id
 12                         and mod( b2.id2, 10 ) = 1
 13                         and b2.status is null
 14*                   )
 15  /

MAX(A.CDATE)        MAX(B.CDATE)
------------------- -------------------
2017/09/08 15:02:54

1 row selected.

Elapsed: 00:00:06.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2565967754

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     1 |    73 |  3654K  (1)| 00:02:23 |
|   1 |  SORT AGGREGATE                |            |     1 |    73 |            |          |
|   2 |   NESTED LOOPS ANTI            |            |  1301K|    90M|  3654K  (1)| 00:02:23 |
|   3 |    NESTED LOOPS OUTER          |            |  1301K|    54M|  2351K  (1)| 00:01:32 |
|   4 |     TABLE ACCESS FULL          | TAB_A      |  1301K|    27M|   689   (2)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| TAB_B      |     1 |    22 |     2   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | IDX1_TAB_B |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | TAB_B      |     1 |    29 |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN          | IDX1_TAB_B |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667)
   6 - access("B"."ID"(+)="A"."ID")
   7 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1)
   8 - access("B2"."ID"="A"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         11  recursive calls
          0  db block gets
     117577  consistent gets
       2091  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

普通にどちらのtab_bもヒント句が効いて、NestedLoopになりました。
まあこれが効かないようでは困ります。

次は、特に理由はありませんが、ヒント句で結合順がNOT EXISTS句が
先にすることが出来るか確認してみます。

  1  select /*+ use_nl(a b) leading(a b2 b) */
  2         max(a.cdate)
  3       , max(b.cdate)
  4    from tab_a a
  5    left join tab_b b
  6      on b.id = a.id
  7     and b.cdate > sysdate - 1/24
  8   where not exists ( select /*+ use_nl(b2) */
  9                             1
 10                        from tab_b b2
 11                       where b2.id = a.id
 12                         and mod( b2.id2, 10 ) = 1
 13                         and b2.status is null
 14*                   )
 15  /

MAX(A.CDATE)        MAX(B.CDATE)
------------------- -------------------
2017/09/08 15:02:54

1 row selected.

Elapsed: 00:00:01.29

Execution Plan
----------------------------------------------------------
Plan hash value: 2649977225

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     1 |    73 |  4703K  (1)| 00:03:04 |
|   1 |  SORT AGGREGATE                |            |     1 |    73 |            |          |
|   2 |   NESTED LOOPS OUTER           |            |  1301K|    90M|  4703K  (1)| 00:03:04 |
|   3 |    NESTED LOOPS ANTI           |            |  1301K|    63M|  2351K  (1)| 00:01:32 |
|   4 |     TABLE ACCESS FULL          | TAB_A      |  1301K|    27M|   689   (2)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| TAB_B      |     1 |    29 |     2   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | IDX1_TAB_B |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | TAB_B      |     1 |    22 |     2   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN          | IDX1_TAB_B |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1)
   6 - access("B2"."ID"="A"."ID")
   7 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667)
   8 - access("B"."ID"(+)="A"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
        208  recursive calls
          6  db block gets
     116259  consistent gets
          0  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          1  rows processed

Predicate Informationを見ると、LEADINGヒント句通りに結合しているのがわかります。

次は、同じSQLで11.2.0.4の実行計画を確認してみます。

alter session set optimizer_features_enable = '11.2.0.4';

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
optimizer_features_enable            string       11.2.0.4
  1  select /*+ use_nl(a b) leading(a b2 b) */
  2         max(a.cdate)
  3       , max(b.cdate)
  4    from tab_a a
  5    left join tab_b b
  6      on b.id = a.id
  7     and b.cdate > sysdate - 1/24
  8   where not exists ( select /*+ use_nl(b2) */
  9                             1
 10                        from tab_b b2
 11                       where b2.id = a.id
 12                         and mod( b2.id2, 10 ) = 1
 13                         and b2.status is null
 14*                   )
 15  /

MAX(A.CDATE)        MAX(B.CDATE)
------------------- -------------------
2017/09/08 15:02:54

1 row selected.

Elapsed: 00:00:01.28

Execution Plan
----------------------------------------------------------
Plan hash value: 2649977225

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     1 |    73 |  4703K  (1)| 00:03:04 |
|   1 |  SORT AGGREGATE                |            |     1 |    73 |            |          |
|   2 |   NESTED LOOPS OUTER           |            |  1301K|    90M|  4703K  (1)| 00:03:04 |
|   3 |    NESTED LOOPS ANTI           |            |  1301K|    63M|  2351K  (1)| 00:01:32 |
|   4 |     TABLE ACCESS FULL          | TAB_A      |  1301K|    27M|   689   (2)| 00:00:01 |
|*  5 |     TABLE ACCESS BY INDEX ROWID| TAB_B      |     1 |    29 |     2   (0)| 00:00:01 |
|*  6 |      INDEX UNIQUE SCAN         | IDX1_TAB_B |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | TAB_B      |     1 |    22 |     2   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN          | IDX1_TAB_B |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   5 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1)
   6 - access("B2"."ID"="A"."ID")
   7 - filter("B"."CDATE"(+)>SYSDATE@!-.0416666666666666666666666666666666666667)
   8 - access("B"."ID"(+)="A"."ID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         21  recursive calls
          4  db block gets
     116063  consistent gets
          0  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

12.2.0.1と変わらずでした。何かちょっとつまらない確認になりましたが、
変わらないということを確認することも重要ですね。きっと。