Plan difference when inserting to table partitionned by list automatic vs partitionned by interval

  • From: Thomas Ranchon <thomas@xxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 15 Jun 2018 17:23:38 +0200

Hi,

I'm currently migrating a custom application from Oracle db 11.2.0.4 to
12.2.0.1, in the process I'm testing new functionalities.

One such feature I'm testing is partition by list automatic vs partition by
interval, I'm experiencing some plan difference runing the the following
query (which is part of a loading process) :

INSERT /*+ APPEND PQ_DISTRIBUTE(ia NONE) */

  INTO tmp_c_data_ulv_op_base_frs ia (

       id_demande

     , id

     , code_article

     , code_frns

     , code_da

     , code_cif_lf

     , code_filiere_four

     , delai_garanti_base

     , delai_garanti_pdv)

SELECT /*+ USE_PARTITION_WISE_GBY LEADING(a b) FULL(a) FULL(b) USE_HASH(b)
NO_SWAP_JOIN_INPUTS(b) PQ_DISTRIBUTE(b NONE, NONE) */

       :nP_IdDemande AS id_demande

     , a.id

     , a.code_article

     , MAX (b.code_frns)

         KEEP(DENSE_RANK FIRST ORDER BY b.date_appli DESC) AS code_frns

     , MAX (b.code_da)

         KEEP(DENSE_RANK FIRST ORDER BY b.date_appli DESC) AS code_da

     , MAX (b.code_cif_lf)

         KEEP(DENSE_RANK FIRST ORDER BY b.date_appli DESC) AS code_cif_lf

     , MAX (b.code_filiere_four)

         KEEP(DENSE_RANK FIRST ORDER BY b.date_appli DESC) AS
code_filiere_four

     , MAX (b.delai_garanti_base)

         KEEP(DENSE_RANK FIRST ORDER BY b.date_appli DESC) AS
delai_garanti_base

     , MAX (b.delai_garanti_pdv)

         KEEP(DENSE_RANK FIRST ORDER BY b.date_appli DESC) AS
delai_garanti_pdv

  FROM tmp_c_perim_ulv_base_dt a

     , arcg_ulv_op_base_frs b

 WHERE a.id_demande = :nP_IdDemande

   AND b.id_pays = a.id_pays

   AND b.code_article = a.code_article

   AND b.code_operation = a.code_operation

   AND b.code_ulv = a.code_ulv

   AND b.id_base = a.id_base

   AND b.date_appli <= a.date_activation

 GROUP

    BY a.id

     , a.code_article;



   - arcg_ulv_op_base_frs is partitioned by HASH(code_article) with 32
   partitions, 24 million rows
   - tmp_c_perim_ulv_base_dt is partitioned by LIST(id_demande) AUTOMATIC
   and sub partitioned by HASH(code_article) with 32 subpartitions, 55 million
   rows after the initial load with only one distinct value for id_demande in
   the initial load.
   - tmp_c_data_ulv_op_base_frs is partitioned by LIST (id_demande)
   AUTOMATIC and sub partitioned by HASH(code_article) with 32 subpartitions
   - tmp_c_data_ulv_op_base_frs_interval  is partitioned by
   RANGE(id_demande) INTERVAL(1) and sub partitioned by HASH(code_article)
   with 32 subpartitions

Query is run in parallel dml and parallel query with a degree of 16 altered
for the session.

The plan when inserting into  tmp_c_data_ulv_op_base_frs is the following :


---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                                    | Name
      | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |
Pstart| Pstop |

---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | INSERT STATEMENT                             |
       |       |       |   29K |           |      |      |           |
 |       |
| 1   |  PX COORDINATOR                              |
       |       |       |       |           |      |      |           |
 |       |
| 2   |   PX SEND QC (RANDOM)                        | :TQ10002
      | 1158K |  165M |   29K |  00:06:01 |:Q1002| P->S |QC (RANDOM)|
 |       |
| 3   |    LOAD AS SELECT (HIGH WATER MARK BROKERED) |
TMP_C_DATA_ULV_OP_BASE_FRS|       |       |       |           |:Q1002| PCWP
|           |       |       |
| 4   |     OPTIMIZER STATISTICS GATHERING           |
       | 1158K |  165M |   29K |  00:06:01 |:Q1002| PCWP |           |
 |       |
| 5   |      SORT GROUP BY                           |
       | 1158K |  165M |   29K |  00:06:01 |:Q1002| PCWP |           |
 |       |
| 6   |       PX RECEIVE                             |
       | 1158K |  165M |   29K |  00:06:01 |:Q1002| PCWP |           |
 |       |
| 7   |        PX SEND HASH                          | :TQ10001
      | 1158K |  165M |   29K |  00:06:01 |:Q1001| P->P |HASH       |
 |       |
| 8   |         SORT GROUP BY                        |
       | 1158K |  165M |   29K |  00:06:01 |:Q1001| PCWP |           |
 |       |
| 9   |          HASH JOIN                           |
       | 1158K |  165M |   29K |  00:06:01 |:Q1001| PCWP |           |
 |       |
| 10  |           PART JOIN FILTER CREATE            | :BF0000
       |   46M | 4607M |  6705 |  00:01:21 |:Q1001| PCWP |           |
 |       |
| 11  |            PX RECEIVE                        |
       |   46M | 4607M |  6705 |  00:01:21 |:Q1001| PCWP |           |
 |       |
| 12  |             PX SEND BROADCAST LOCAL          | :TQ10000
      |   46M | 4607M |  6705 |  00:01:21 |:Q1000| P->P |BROADCAST LOCAL|
     |       |
| 13  |              PX BLOCK ITERATOR               |
       |   46M | 4607M |  6705 |  00:01:21 |:Q1000| PCWC |           | 1
 | 32    |
| 14  |               TABLE ACCESS FULL              |
TMP_C_PERIM_ULV_BASE_DT   |   46M | 4607M |  6705 |  00:01:21 |:Q1000| PCWP
|           | KEY   | KEY   |
| 15  |           PX BLOCK ITERATOR                  |
       |   23M | 1040M |  3236 |  00:00:39 |:Q1001| PCWC |           |
:BF0000| :BF0000|
| 16  |            TABLE ACCESS FULL                 |
ARCG_ULV_OP_BASE_FRS      |   23M | 1040M |  3236 |  00:00:39 |:Q1001| PCWP
|           | :BF0000| :BF0000|

---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$1
14 - SEL$1                / A@SEL$1
16 - SEL$1                / B@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------
9 - access("B"."CODE_ARTICLE"="A"."CODE_ARTICLE" AND
"B"."ID_PAYS"="A"."ID_PAYS" AND "B"."CODE_OPERATION"="A"."CODE_OPERATION"
AND "B"."CODE_ULV"="A"."CODE_ULV" AND "B"."ID_BASE"="A"."ID_BASE")
9 - filter("B"."DATE_APPLI"<="A"."DATE_ACTIVATION")
14 - access(:Z>=:Z AND :Z<=:Z)
14 - filter("A"."ID_DEMANDE"=:NP_IDDEMANDE)
16 - access(:Z>=:Z AND :Z<=:Z)

Content of other_xml column
===========================
nodeid/pflags: 16 513nodeid/pflags: 15 513nodeid/pflags: 14
1nodeid/pflags: 13 1nodeid/pflags: 13 17  derived_cpu_dop: 0
  derived_io_dop : 0
  dop_reason     : session
  dop            : 16
  px_in_memory_imc: no
  px_in_memory   : no
  db_version     : 12.2.0.1
  parse_schema   : SENAFR5
  dynamic_sampling: 5
  plan_hash_full : 330082513
  plan_hash      : 3714696070
  plan_hash_2    : 2398615721
Peeked Binds
============
  Bind variable information
    position=2
    datatype(code)=2
    datatype(string)=NUMBER
    precision=0
    scale=0
    max length=22
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 5)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "IA"@"INS$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_HASH(@"SEL$1" "B"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "B"@"SEL$1" BROADCAST NONE)
      PQ_MAP(@"SEL$1" "B"@"SEL$1")
      GBY_PUSHDOWN(@"SEL$1")
    END_OUTLINE_DATA
  */

The plan when inserting into  tmp_c_data_ulv_op_base_frs_interval is the
following (with the exact same select statement):


------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                                    | Name
               | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ
Distrib | Pstart| Pstop |

------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | INSERT STATEMENT                             |
                |       |       |   29K |           |      |      |
   |       |       |
| 1   |  PX COORDINATOR                              |
                |       |       |       |           |      |      |
   |       |       |
| 2   |   PX SEND QC (RANDOM)                        | :TQ10000
               | 1158K |  165M |   29K |  00:06:01 |:Q1000| P->S |QC
(RANDOM)|       |       |
| 3   |    LOAD AS SELECT (HIGH WATER MARK BROKERED) |
TMP_C_DATA_ULV_OP_BASE_FRS_INTERVAL|       |       |       |
 |:Q1000| PCWP |           |       |       |
| 4   |     OPTIMIZER STATISTICS GATHERING           |
                | 1158K |  165M |   29K |  00:06:01 |:Q1000| PCWP |
   |       |       |
| 5   |      PX PARTITION HASH ALL                   |
                | 1158K |  165M |   29K |  00:06:01 |:Q1000| PCWC |
   | 1     | 32    |
| 6   |       SORT GROUP BY                          |
                | 1158K |  165M |   29K |  00:06:01 |:Q1000| PCWP |
   |       |       |
| 7   |        HASH JOIN                             |
                | 1158K |  165M |   29K |  00:06:01 |:Q1000| PCWP |
   |       |       |
| 8   |         PX PARTITION LIST SINGLE             |
                |   46M | 4607M |  6705 |  00:01:21 |:Q1000| PCWC |
   | KEY   | KEY   |
| 9   |          TABLE ACCESS FULL                   |
TMP_C_PERIM_ULV_BASE_DT            |   46M | 4607M |  6705 |  00:01:21
|:Q1000| PCWP |           | KEY   | KEY   |
| 10  |         TABLE ACCESS FULL                    |
ARCG_ULV_OP_BASE_FRS               |   23M | 1040M |  3236 |  00:00:39
|:Q1000| PCWP |           | 1     | 32    |

------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$1
 9 - SEL$1                / A@SEL$1
10 - SEL$1                / B@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------
7 - access("B"."CODE_ARTICLE"="A"."CODE_ARTICLE" AND
"B"."ID_PAYS"="A"."ID_PAYS" AND "B"."CODE_OPERATION"="A"."CODE_OPERATION"
AND "B"."CODE_ULV"="A"."CODE_ULV" AND "B"."ID_BASE"="A"."ID_BASE")
7 - filter("B"."DATE_APPLI"<="A"."DATE_ACTIVATION")

Content of other_xml column
===========================
nodeid/pflags: 10 1nodeid/pflags: 9 1nodeid/pflags: 8 1nodeid/pflags: 8
1nodeid/pflags: 5 17  derived_cpu_dop: 0
  derived_io_dop : 0
  dop_reason     : session
  dop            : 16
  px_in_memory_imc: no
  px_in_memory   : no
  db_version     : 12.2.0.1
  parse_schema   : SENAFR5
  dynamic_sampling: 5
  plan_hash_full : 3726494627
  plan_hash      : 1012408917
  plan_hash_2    : 4291354839
Peeked Binds
============
  Bind variable information
    position=2
    datatype(code)=2
    datatype(string)=NUMBER
    precision=0
    scale=0
    max length=22
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 5)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      PQ_DISTRIBUTE(@"INS$1" "IA"@"INS$1" NONE)
      FULL(@"INS$1" "IA"@"INS$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_HASH(@"SEL$1" "B"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "B"@"SEL$1" NONE NONE)
      USE_PARTITION_WISE_GBY(@"SEL$1")
    END_OUTLINE_DATA
  */

Both plan are taken from the 10053 trace generated.

Both queries are inserting 55 million rows.

Inserting into tmp_c_data_ulv_op_base_frs took 2mn9sec Vs for 1mn32s
tmp_c_data_ulv_op_base_frs_interval.

I was expecting partition wise join and group by as in the insert into the
interval table, any idea why it's not doing so when inserting into list
automatic table? (If needed I can provide full 10053 trace).

In a previous attempt statistics where locked on the TMP% tables and
optimizer_dynamic_sampling was set at 0 for the session, the plan for the
list automatic table was the following and the insert took 9mn30sec :


---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                                    | Name
       | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib |
Pstart| Pstop |

---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | INSERT STATEMENT                             |
      |       |       |  3247 |           |      |      |           |
|       |
| 1   |  PX COORDINATOR                              |
      |       |       |       |           |      |      |           |
|       |
| 2   |   PX SEND QC (RANDOM)                        | :TQ10001
       |     4 |   584 |  3247 |  00:00:39 |:Q1001| P->S |QC (RANDOM)|
  |       |
| 3   |    LOAD AS SELECT (HIGH WATER MARK BROKERED) |
TMP_C_DATA_ULV_OP_BASE_FRS|       |       |       |           |:Q1001| PCWP
|           |       |       |
| 4   |     SORT GROUP BY                            |
      |     4 |   584 |  3247 |  00:00:39 |:Q1001| PCWP |           |
|       |
| 5   |      PX RECEIVE                              |
      |     4 |   584 |  3247 |  00:00:39 |:Q1001| PCWP |           |
|       |
| 6   |       PX SEND HASH                           | :TQ10000
       |     4 |   584 |  3247 |  00:00:39 |:Q1000| P->P |HASH       |
  |       |
| 7   |        SORT GROUP BY                         |
      |     4 |   584 |  3247 |  00:00:39 |:Q1000| PCWP |           |
|       |
| 8   |         HASH JOIN                            |
      |     4 |   584 |  3247 |  00:00:39 |:Q1000| PCWP |           |
|       |
| 9   |          JOIN FILTER CREATE                  | :BF0001
      |    82 |  8200 |     2 |  00:00:01 |:Q1000| PCWP |           |
|       |
| 10  |           PART JOIN FILTER CREATE            | :BF0000
      |    82 |  8200 |     2 |  00:00:01 |:Q1000| PCWP |           |
|       |
| 11  |            PARTITION LIST SINGLE             |
      |    82 |  8200 |     2 |  00:00:01 |:Q1000| PCWC |           | KEY
| KEY   |
| 12  |             PARTITION HASH ALL               |
      |    82 |  8200 |     2 |  00:00:01 |:Q1000| PCWC |           | 1
| 32    |
| 13  |              TABLE ACCESS FULL               |
TMP_C_PERIM_ULV_BASE_DT   |    82 |  8200 |     2 |  00:00:01 |:Q1000| PCWP
|           | KEY   | KEY   |
| 14  |          JOIN FILTER USE                     | :BF0001
      |   23M | 1040M |  3236 |  00:00:39 |:Q1000| PCWP |           |
|       |
| 15  |           PX BLOCK ITERATOR                  |
      |   23M | 1040M |  3236 |  00:00:39 |:Q1000| PCWC |           |
:BF0000| :BF0000|
| 16  |            TABLE ACCESS FULL                 |
ARCG_ULV_OP_BASE_FRS      |   23M | 1040M |  3236 |  00:00:39 |:Q1000| PCWP
|           | :BF0000| :BF0000|

---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$1
13 - SEL$1                / A@SEL$1
16 - SEL$1                / B@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------
8 - access("B"."CODE_ARTICLE"="A"."CODE_ARTICLE" AND
"B"."ID_PAYS"="A"."ID_PAYS" AND "B"."CODE_OPERATION"="A"."CODE_OPERATION"
AND "B"."CODE_ULV"="A"."CODE_ULV" AND "B"."ID_BASE"="A"."ID_BASE")
8 - filter("B"."DATE_APPLI"<="A"."DATE_ACTIVATION")
13 - filter("A"."ID_DEMANDE"=:NP_IDDEMANDE)
16 - access(:Z>=:Z AND :Z<=:Z)
16 -
filter(SYS_OP_BLOOM_FILTER(:BF0001,"B"."ID_PAYS","B"."CODE_ARTICLE","B"."CODE_OPERATION","B"."CODE_ULV","B"."ID_BASE"))

Content of other_xml column
===========================
nodeid/pflags: 16 513nodeid/pflags: 15 513nodeid/pflags: 13
1nodeid/pflags: 12 1nodeid/pflags: 12 17nodeid/pflags: 11 1
 derived_cpu_dop: 0
  derived_io_dop : 0
  dop_reason     : session
  dop            : 16
  px_in_memory_imc: no
  px_in_memory   : no
  db_version     : 12.2.0.1
  parse_schema   : SENAFR5
  plan_hash_full : 1307301773
  plan_hash      : 3760498484
  plan_hash_2    : 1307301773
Peeked Binds
============
  Bind variable information
    position=2
    datatype(code)=2
    datatype(string)=NUMBER
    precision=0
    scale=0
    max length=22
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "IA"@"INS$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_HASH(@"SEL$1" "B"@"SEL$1")
      PQ_DISTRIBUTE(@"SEL$1" "B"@"SEL$1" BROADCAST NONE)
      PX_JOIN_FILTER(@"SEL$1" "B"@"SEL$1")
      PQ_REPLICATE(@"SEL$1" "B"@"SEL$1")
      GBY_PUSHDOWN(@"SEL$1")
    END_OUTLINE_DATA
  */


Regards,
Thomas

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 06-2018