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

Jonathan, Mohamed,

I tried your different suggestions and nothing worked, then I tried to change the partition method for tmp_c_data_ulv_op_base_frs from list automatic to list and now the plan is as expected a partition wise join.

Why would the optimizer produce plan difference in such case? I'll try to find if there is any related bug.

Regards,
Thomas

Le dim. 17 juin 2018 à 14:27, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> a écrit :

>
> Thomas,
>
> To me the really strange thing is that if you'd told me only one of them
> did what you wanted I would have guessed that the automatic list version
> behaved and the interval range wouldn't because of the compatibility
> between the composite in the insert and the composite in the select.
>
> Just before you do the insert can you check that there is an "obvious"
> one-to-one match in partitions and partition numbering between the inserted
> and selected list composites.
> Have you tried using pq_distribute(_at_query_block alias PARTITION) rather
> than NONE ?
> How about adding a (redundant) a.id_demande to the "group by" clause ?
>
> How about giving every query block a name, then putting all the hints at
> the top of the statement and using (_at_query_block alias_at_queryblock) rather
> than adding hints to the select block ? (I don't think it should make any
> difference - but you can try; you might try adding the outline from the
> working version of the statement to the insert query block to see if
> Oracle's own hint set produces the plan you want in the case where your
> hints don't).
>
>
> I am sufficiently curious that if you want to zip and send me the two
> 10053 trace files I'll spend a little time looking at them and let you know
> if I can spot anything. A list of any column declared not null and any
> constraints would also be useful.
>
>
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Thomas Ranchon <thomas_at_ranchon.org>
> Sent: 15 June 2018 16:23
> To: oracle-l_at_freelists.org
> Subject: Plan difference when inserting to table partitionned by list
> automatic vs partitionned by interval
>
> 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<http://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<http://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
>
>
>

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