Re: Plan difference when inserting to table partitionned by list automatic vs partitionned by interval
Date: Mon, 18 Jun 2018 12:53:00 +0000
Message-ID: <MM1P123MB08420DCAAB6E5E57F842B011A5710_at_MM1P123MB0842.GBRP123.PROD.OUTLOOK.COM>
This may relate to why I was surprised that your previous attempts worked on the "wrong" one. Partition-wise activity always requires a very close match between the partitioned definitions.
You have the pq_distribute(alias none) hint in place, which may only be valid if the select partitioning must be aligned with the insert partitioning. If your automatic list table had fewer partitions than the other list/hash partitioned table when you did the insert then maybe the tables were consider to be insufficiently similar - while if you changed to list (without the automatic) you ended up with DML that had two identically structured tables.
The problem (whether it's a bug or a deliberate restriction) is probably lurking somewhere in that area. (But I still have no good ideas about why it worked with the range/interval target.)
Regards
Jonathan Lewis
From: Thomas Ranchon <thomas_at_ranchon.org> Sent: 18 June 2018 10:11
To: Jonathan Lewis
Cc: oracle-l_at_freelists.org
Subject: 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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>> on behalf of Thomas Ranchon <thomas_at_ranchon.org<mailto:thomas_at_ranchon.org>> Sent: 15 June 2018 16:23
To: oracle-l_at_freelists.org<mailto: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><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><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 :
---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+Query Block Name / Object Alias(identified by operation id):
| 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|
---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
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"@"INS$1")
FULL(_at_"SEL$1" "A"@"SEL$1")
FULL(_at_"SEL$1" "B"@"SEL$1")
LEADING(_at_"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
USE_HASH(_at_"SEL$1" "B"@"SEL$1")
PQ_DISTRIBUTE(_at_"SEL$1" "B"@"SEL$1" BROADCAST NONE)
PQ_MAP(_at_"SEL$1" "B"@"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):
------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+Query Block Name / Object Alias(identified by operation id):
| 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 |
------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
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"@"INS$1" NONE)
FULL(_at_"INS$1" "IA"@"INS$1")
FULL(_at_"SEL$1" "A"@"SEL$1")
FULL(_at_"SEL$1" "B"@"SEL$1")
LEADING(_at_"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
USE_HASH(_at_"SEL$1" "B"@"SEL$1")
PQ_DISTRIBUTE(_at_"SEL$1" "B"@"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 :
---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+Query Block Name / Object Alias(identified by operation id):
| 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|
---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
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"@"INS$1")
FULL(_at_"SEL$1" "A"@"SEL$1")
FULL(_at_"SEL$1" "B"@"SEL$1")
LEADING(_at_"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
USE_HASH(_at_"SEL$1" "B"@"SEL$1")
PQ_DISTRIBUTE(_at_"SEL$1" "B"@"SEL$1" BROADCAST NONE)
PX_JOIN_FILTER(_at_"SEL$1" "B"@"SEL$1")
PQ_REPLICATE(_at_"SEL$1" "B"@"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-lReceived on Mon Jun 18 2018 - 14:53:00 CEST