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@xxxxxxxxxxx>
Sent: 18 June 2018 10:11
To: Jonathan Lewis
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> 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(@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 (@query_block alias@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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Thomas Ranchon <thomas@xxxxxxxxxxx<mailto:thomas@xxxxxxxxxxx>>
Sent: 15 June 2018 16:23
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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 :
---------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
