standingzzz's Oracle memo

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

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

概要

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

検証のベースとなるSQL

select count(*)
  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
  COUNT(*)
----------
    900000

1 row selected.

Elapsed: 00:00:00.32

Execution Plan
----------------------------------------------------------
Plan hash value: 3207213458

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    42 |       |  3332   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    42 |       |            |          |
|*  2 |   HASH JOIN RIGHT ANTI|       |  1301K|    52M|  4192K|  3332   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | TAB_B |   104K|  2964K|       |   895   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | TAB_A |  1301K|    16M|       |   688   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("B2"."ID"="A"."ID")
   3 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1)

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


Statistics
----------------------------------------------------------
         48  recursive calls
         17  db block gets
       5966  consistent gets
          0  physical reads
       3288  redo size
        358  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

left joinしているtab_bが実行計画上にいません。どのように処理しているのでしょうか。
続いてoptimizer_features_enableを変更して、リリースバージョンによる違いを確認していきます。

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
  COUNT(*)
----------
    900000

1 row selected.

Elapsed: 00:00:01.21

Execution Plan
----------------------------------------------------------
Plan hash value: 4056045473

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    64 |       |  8831   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    64 |       |            |          |
|*  2 |   HASH JOIN RIGHT ANTI|       |  1301K|    79M|  4192K|  8831   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | TAB_B |   104K|  2964K|       |   895   (1)| 00:00:01 |
|*  4 |    HASH JOIN OUTER    |       |  1301K|    43M|    31M|  4834   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | TAB_A |  1301K|    16M|       |   688   (1)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL | TAB_B |  1049K|    22M|       |   908   (3)| 00:00:01 |
---------------------------------------------------------------------------------------

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

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

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


Statistics
----------------------------------------------------------
         32  recursive calls
         10  db block gets
       9069  consistent gets
          0  physical reads
       1036  redo size
        358  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

11.2.0.4では、まずtab_aとtab_bをOUTER JOINしてから、もう1つのtab_bにANTI結合しています。
うーん、正しいです。非常に正しい。安心しました(?)
もう1つ、12.1.0.2でも確認してみます。

12.1.0.2
alter session set optimizer_features_enable = '12.1.0.2';

NAME                                 TYPE         VALUE
------------------------------------ ------------ ------------------------------
optimizer_features_enable            string       12.1.0.2
  COUNT(*)
----------
    900000

1 row selected.

Elapsed: 00:00:00.84

Execution Plan
----------------------------------------------------------
Plan hash value: 4056045473

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    64 |       |  8831   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    64 |       |            |          |
|*  2 |   HASH JOIN RIGHT ANTI|       |  1301K|    79M|  4192K|  8831   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | TAB_B |   104K|  2964K|       |   895   (1)| 00:00:01 |
|*  4 |    HASH JOIN OUTER    |       |  1301K|    43M|    31M|  4834   (1)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | TAB_A |  1301K|    16M|       |   688   (1)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL | TAB_B |  1049K|    22M|       |   908   (3)| 00:00:01 |
---------------------------------------------------------------------------------------

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

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

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan


Statistics
----------------------------------------------------------
        433  recursive calls
          8  db block gets
       9697  consistent gets
        527  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
          1  rows processed

これは11.2.0.4と同じです。
もう少しだけ、12.2.0.1でどのように2つのtab_bを処理しているのか確認します。

12.2.0.1 advanced
select * from table(dbms_xplan.display(null, null, 'advanced'));
Plan hash value: 3207213458

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    42 |       |  3332   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |    42 |       |            |          |
|*  2 |   HASH JOIN RIGHT ANTI|       |  1301K|    52M|  4192K|  3332   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL  | TAB_B |   104K|  2964K|       |   895   (1)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | TAB_A |  1301K|    16M|       |   688   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$085E11BF
   3 - SEL$085E11BF / B2@SEL$3
   4 - SEL$085E11BF / A@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      SWAP_JOIN_INPUTS(@"SEL$085E11BF" "B2"@"SEL$3")
      USE_HASH(@"SEL$085E11BF" "B2"@"SEL$3")
      LEADING(@"SEL$085E11BF" "A"@"SEL$1" "B2"@"SEL$3")
      FULL(@"SEL$085E11BF" "B2"@"SEL$3")
      FULL(@"SEL$085E11BF" "A"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      ANSI_REARCH(@"SEL$1")
      OUTLINE(@"SEL$8812AA4E")
      ANSI_REARCH(@"SEL$2")
      OUTLINE(@"SEL$948754D7")
      MERGE(@"SEL$8812AA4E" >"SEL$948754D7")
      OUTLINE(@"SEL$2BFA4EE4")
      OUTLINE(@"SEL$3")
      ELIMINATE_JOIN(@"SEL$2BFA4EE4" "B"@"SEL$1")
      OUTLINE(@"SEL$56824FA9")
      UNNEST(@"SEL$3")
      OUTLINE_LEAF(@"SEL$085E11BF")
      ALL_ROWS
      DB_VERSION('12.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("B2"."ID"="A"."ID")
   3 - filter("B2"."STATUS" IS NULL AND MOD("B2"."ID2",10)=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=1; rowset=1019)
   3 - (rowset=256) "B2"."ID"[NUMBER,22]
   4 - (rowset=1019) "A"."ID"[NUMBER,22]

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

left joinしているtab_bが出てくるのは、下記のELIMINATE_JOINだけです。

ELIMINATE_JOIN(@"SEL$2BFA4EE4" "B"@"SEL$1")

マニュアルにも記載がありませんので推測になりますが、ELIMINATEは省略する、除くの意味で、
tab_aとtab_bはそれぞれTABLE FULL SCANして結合していますから、
もう1回同じテーブルを結合し直さなくても計算出来ますよ、ということなのでしょう。
何だか賢いですね。その2に続きます。