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

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Fri, 15 Jun 2018 19:48:16 +0200
Message-ID: <CAJu8R6h9Jj4BTeDzDcHH5M=aqm6omEzOaJ1YqbKht_PbYTymuA_at_mail.gmail.com>

Thomas

There is one important requirement for partition-wise join to occur which is the need for Oracle to read the partitioned object via *Partition granule* i.e. you see *PX PARTITION* in the execution plan. If a *Block granule* is used (*PX BLOCK ITERATOR*) then partition wise join will not kick in.

It seems also that there is a hidden parameter that dictates the Partition granule choice instead of the Block granule: *_px_partition_scan_threshold*

If the number of partititions to be scanned by Oracle is greater than: DOP * _px_partition_scan_threshold then Oracle will opt for partition granule and partition-wise join. So I think that it all depends on the number of partitions to be scanned.

Best regards

Mohamed Houri

2018-06-15 17:23 GMT+02:00 Thomas Ranchon <thomas_at_ranchon.org>:

> 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
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

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

Received on Fri Jun 15 2018 - 19:48:16 CEST

This message: [ Message body ]
Next message: Martin Berger: "Re: Re: Explain Plan and Security"
In reply to Thomas Ranchon: "Plan difference when inserting to table partitionned by list automatic vs partitionned by interval"
Next in thread: Jonathan Lewis: "Re: Plan difference when inserting to table partitionned by list automatic vs partitionned by interval"

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

Original text of this message