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>
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 <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Thomas Ranchon <thomas@xxxxxxxxxxx>
Sent: 15 June 2018 16:23
To: 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>
, 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@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
