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

From: Thomas Ranchon <thomas_at_ranchon.org>
Date: Fri, 15 Jun 2018 17:23:38 +0200
Message-ID: <CAJNN_2SEZ6gwsw=3f4aWhtKW+boJhRp4OYkONknn0ehDn8SRrg_at_mail.gmail.com>

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_at_SEL$1
> 16 - SEL$1 / B_at_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(_at_"SEL$1")
> OUTLINE_LEAF(_at_"INS$1")
> FULL(_at_"INS$1" "IA"_at_"INS$1")
> FULL(_at_"SEL$1" "A"_at_"SEL$1")
> FULL(_at_"SEL$1" "B"_at_"SEL$1")
> LEADING(_at_"SEL$1" "A"_at_"SEL$1" "B"@"SEL$1")
> USE_HASH(_at_"SEL$1" "B"_at_"SEL$1")
> PQ_DISTRIBUTE(_at_"SEL$1" "B"_at_"SEL$1" BROADCAST NONE)
> PQ_MAP(_at_"SEL$1" "B"_at_"SEL$1")
> GBY_PUSHDOWN(_at_"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_at_SEL$1
> 10 - SEL$1 / B_at_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(_at_"SEL$1")
> OUTLINE_LEAF(_at_"INS$1")
> PQ_DISTRIBUTE(_at_"INS$1" "IA"_at_"INS$1" NONE)
> FULL(_at_"INS$1" "IA"_at_"INS$1")
> FULL(_at_"SEL$1" "A"_at_"SEL$1")
> FULL(_at_"SEL$1" "B"_at_"SEL$1")
> LEADING(_at_"SEL$1" "A"_at_"SEL$1" "B"@"SEL$1")
> USE_HASH(_at_"SEL$1" "B"_at_"SEL$1")
> PQ_DISTRIBUTE(_at_"SEL$1" "B"_at_"SEL$1" NONE NONE)
> USE_PARTITION_WISE_GBY(_at_"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_at_SEL$1
> 16 - SEL$1 / B_at_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(_at_"SEL$1")
> OUTLINE_LEAF(_at_"INS$1")
> FULL(_at_"INS$1" "IA"_at_"INS$1")
> FULL(_at_"SEL$1" "A"_at_"SEL$1")
> FULL(_at_"SEL$1" "B"_at_"SEL$1")
> LEADING(_at_"SEL$1" "A"_at_"SEL$1" "B"@"SEL$1")
> USE_HASH(_at_"SEL$1" "B"_at_"SEL$1")
> PQ_DISTRIBUTE(_at_"SEL$1" "B"_at_"SEL$1" BROADCAST NONE)
> PX_JOIN_FILTER(_at_"SEL$1" "B"_at_"SEL$1")
> PQ_REPLICATE(_at_"SEL$1" "B"_at_"SEL$1")
> GBY_PUSHDOWN(_at_"SEL$1")
> END_OUTLINE_DATA
> */

Regards,
Thomas

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 15 2018 - 17:23:38 CEST

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message