Plan change depending on parallel degree setting

From: Thomas Ranchon <thomas_at_ranchon.org>
Date: Wed, 4 Apr 2018 14:15:49 +0200
Message-ID: <CAJNN_2RVcZgToDd+N3qL-qoM0qLFwQaKD_xtcJgzNdeyyjJFOA_at_mail.gmail.com>

Hi,

I'm in the process of migrating an application from oracle DB 11.2.0.4 to 12.2.0.1 (on an AIX 7.1 P7+ server)

Doing so I'm trying to take advantage of new functionalities like match recognize to merge consecutive rows with the same data values (Was using queries with analytic functions).

During my test I noticed that depending on the parallel degree the plan changed.

With DOP 8 :

>
> -------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
> | Id | Operation | Name
> | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ
> Distrib | Pstart| Pstop |
>
> -------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
> | 0 | CREATE TABLE STATEMENT |
> | | | 1362K | | | |
> | | |
> | 1 | PX COORDINATOR |
> | | | | | | |
> | | |
> | 2 | PX SEND QC (RANDOM) |
> :TQ10000 | 116M | 15G | 569K | 02:56:30 |:Q1000| P->S
> |QC (RANDOM)| | |
> | 3 | LOAD AS SELECT (HYBRID TSM/HWMB) |
> ARMS_ULV_BASE_ENS_CRIT4| | | | |:Q1000| PCWP |
> | | |
> | 4 | PX PARTITION HASH ALL |
> | 116M | 15G | 569K | 02:56:30 |:Q1000| PCWC |
> | 1 | 16 |
> | 5 | VIEW |
> | 116M | 15G | 569K | 02:56:30 |:Q1000| PCWP |
> | | |
> | 6 | MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON |
> | 116M | 6044M | 256K | 00:52:30 |:Q1000| PCWP |
> | | |
> | 7 | TABLE ACCESS FULL |
> ARMS_ULV_BASE_ENS | 116M | 6044M | 43K | 00:09:49 |:Q1000| PCWP |
> | 1 | 16 |
>
> -------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
> Query Block Name / Object Alias(identified by operation id):
> ------------------------------------------------------------
> 1 - SEL$2
> 5 - SEL$1 / from$_subquery$_002_at_SEL$2
> 6 - SEL$1
> 7 - SEL$1 / ARMS_ULV_BASE_ENS_at_SEL$1
> ------------------------------------------------------------
> Predicate Information:
> ----------------------
> 5 - filter("ID_TYP_DIF_RPM" IS NOT NULL)
>
> /*+
> 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' 6)
> ALL_ROWS
> OUTLINE_LEAF(_at_"SEL$1")
> OUTLINE_LEAF(_at_"SEL$2")
> NO_ACCESS(_at_"SEL$2" "from$_subquery$_002"_at_"SEL$2")
> FULL(_at_"SEL$1" "ARMS_ULV_BASE_ENS"_at_"SEL$1")
> END_OUTLINE_DATA
> */

With DOP 16 :

>
> ------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
> | Id | Operation | Name
> | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ
> Distrib | Pstart| Pstop |
>
> ------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
> | 0 | CREATE TABLE STATEMENT |
> | | | 681K | | | |
> | | |
> | 1 | PX COORDINATOR |
> | | | | | | |
> | | |
> | 2 | PX SEND QC (RANDOM) |
> :TQ10001 | 116M | 15G | 284K | 00:58:15 |:Q1001| P->S
> |QC (RANDOM)| | |
> | 3 | LOAD AS SELECT (HYBRID TSM/HWMB) |
> ARMS_ULV_BASE_ENS_CRIT4| | | | |:Q1001| PCWP |
> | | |
> | 4 | VIEW |
> | 116M | 15G | 284K | 00:58:15 |:Q1001| PCWP |
> | | |
> | 5 | MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON |
> | 116M | 6044M | 128K | 00:26:15 |:Q1001| PCWP |
> | | |
> | 6 | PX RECEIVE |
> | 116M | 6044M | 21K | 00:04:24 |:Q1001| PCWP |
> | | |
> | 7 | PX SEND HASH |
> :TQ10000 | 116M | 6044M | 21K | 00:04:24 |:Q1000| P->P
> |HASH | | |
> | 8 | PX BLOCK ITERATOR |
> | 116M | 6044M | 21K | 00:04:24 |:Q1000| PCWC |
> | 1 | 16 |
> | 9 | TABLE ACCESS FULL |
> ARMS_ULV_BASE_ENS | 116M | 6044M | 21K | 00:04:24 |:Q1000| PCWP |
> | 1 | 16 |
>
> ------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
> Query Block Name / Object Alias(identified by operation id):
> ------------------------------------------------------------
> 1 - SEL$2
> 4 - SEL$1 / from$_subquery$_002_at_SEL$2
> 5 - SEL$1
> 9 - SEL$1 / ARMS_ULV_BASE_ENS_at_SEL$1
> ------------------------------------------------------------
> Predicate Information:
> ----------------------
> 4 - filter("ID_TYP_DIF_RPM" IS NOT NULL)
> 9 - access(:Z>=:Z AND :Z<=:Z)
>
> /*+
> 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' 6)
> ALL_ROWS
> OUTLINE_LEAF(_at_"SEL$1")
> OUTLINE_LEAF(_at_"SEL$2")
> NO_ACCESS(_at_"SEL$2" "from$_subquery$_002"_at_"SEL$2")
> FULL(_at_"SEL$1" "ARMS_ULV_BASE_ENS"_at_"SEL$1")
> END_OUTLINE_DATA
> */

Source table is defined as follow :

> CREATE
> TABLE arms_ulv_base_ens(
> id_pays NUMBER(9)
> , code_article NUMBER(8)
> , code_operation NUMBER(5)
> , code_ulv NUMBER(2)
> , id_base NUMBER(5)
> , id_ens_pays NUMBER(9)
> , date_activation DATE
> , date_desactivation DATE
> , id_demande_cre NUMBER(18)
> , id_demande_mod NUMBER(18)
> , ind_validation NUMBER(1)
> , id_statut_dif NUMBER(2)
> , id_typ_dif NUMBER(1)
> , id_statut_assort NUMBER(2)
> , id_assortiment NUMBER(2)
> , id_niv_prospectus NUMBER(2)
> , id_type_act_op NUMBER(1))
> PARTITION BY HASH (code_article)
> PARTITIONS 16;
>
> CREATE UNIQUE INDEX pk_arms_ulv_base_ens ON arms_ulv_base_ens(id_pays,
> code_article, code_operation, code_ulv, id_base, id_ens_pays,
> date_activation)
> LOCAL;
> ALTER TABLE arms_ulv_base_ens ADD
> CONSTRAINT pk_arms_ulv_base_ens PRIMARY KEY(id_pays, code_article,
> code_operation, code_ulv, id_base, id_ens_pays, date_activation)
> USING INDEX pk_arms_ulv_base_ens ENABLE VALIDATE;

This table have arround 122 millions rows and the distribution is almost even between partitions (between 7.3 and 7.9 millions rows per partition)

When changing the source table to 32 partitions and setting parallel degree 16 oracle is using the same plan as for parallel degree 8 and 16 partitions :

>
>> ----------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
>
> | Id | Operation | Name
>> | Rows | Bytes | Cost | Time | TQ |IN-OUT|PQ
>> Distrib | Pstart| Pstop |
>
>
>> ----------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
>
> | 0 | CREATE TABLE STATEMENT |
>> | | | 1234K | | | |
>> | | |
>
> | 1 | PX COORDINATOR |
>> | | | | | | |
>> | | |
>
> | 2 | PX SEND QC (RANDOM) |
>> :TQ10000 | 116M | 15G | 441K | 02:30:15 |:Q1000| P->S
>> |QC (RANDOM)| | |
>
> | 3 | LOAD AS SELECT (HYBRID TSM/HWMB) |
>> ARMS_ULV_BASE_ENS_CRIT_P32| | | | |:Q1000| PCWP
>> | | | |
>
> | 4 | PX PARTITION HASH ALL |
>> | 116M | 15G | 441K | 02:30:15 |:Q1000| PCWC |
>> | 1 | 32 |
>
> | 5 | VIEW |
>> | 116M | 15G | 441K | 02:30:15 |:Q1000| PCWP |
>> | | |
>
> | 6 | MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON |
>> | 116M | 6044M | 128K | 00:26:15 |:Q1000| PCWP |
>> | | |
>
> | 7 | TABLE ACCESS FULL |
>> ARMS_ULV_BASE_ENS_P32 | 116M | 6044M | 22K | 00:04:25 |:Q1000| PCWP
>> | | 1 | 32 |
>
>
>> ----------------------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
>
> Query Block Name / Object Alias(identified by operation id):
>
> ------------------------------------------------------------
>
> 1 - SEL$2
>
> 5 - SEL$1 / from$_subquery$_002_at_SEL$2
>
> 6 - SEL$1
>
> 7 - SEL$1 / ARMS_ULV_BASE_ENS_P32_at_SEL$1
>
> ------------------------------------------------------------
>
> Predicate Information:
>
> ----------------------
>
> 5 - filter("ID_TYP_DIF_RPM" IS NOT NULL)
>
>
>
> /*+
>>
> 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' 6)
>
> ALL_ROWS
>
> OUTLINE_LEAF(_at_"SEL$1")
>
> OUTLINE_LEAF(_at_"SEL$2")
>
> NO_ACCESS(_at_"SEL$2" "from$_subquery$_002"_at_"SEL$2")
>
> FULL(_at_"SEL$1" "ARMS_ULV_BASE_ENS_P32"_at_"SEL$1")
>
> END_OUTLINE_DATA
>
> */
>
>

Query used is as follow for the 32 partitions tables :

> CREATE /*+ PQ_DISTRIBUTE(arms_ulv_base_ens_crit_p32 NONE)
> GATHER_PLAN_STATISTICS */
> TABLE arms_ulv_base_ens_crit_p32
> PARTITION BY HASH(code_article) PARTITIONS 32
> NOLOGGING
> AS
> SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */

       id_pays


>      , code_article

>      , code_operation

>      , code_ulv

>      , id_base

>      , id_ens_pays

>      , date_activation_rpm AS date_activation

>      , date_desactivation_rpm AS date_desactivation

>      , ind_validation_rpm AS ind_validation

>      , id_typ_dif_rpm AS id_typ_dif

>      , id_assortiment_rpm AS id_assortiment

>   FROM arms_ulv_base_ens_p32

>     MATCH_RECOGNIZE(

>       PARTITION BY id_pays, code_article, code_operation, code_ulv,

> id_base, id_ens_pays

>       ORDER BY date_activation

>       MEASURES FIRST(date_activation) AS date_activation_rpm

>              , LAST(date_desactivation) AS date_desactivation_rpm

>              , FIRST(ind_validation) AS ind_validation_rpm

>              , FIRST(id_typ_dif) AS id_typ_dif_rpm

>              , FIRST(id_assortiment) AS id_assortiment_rpm

>       ONE ROW PER MATCH

>       PATTERN(a ind_same*)

>       DEFINE ind_same AS (    date_activation - 1 =

> PREV(date_desactivation)

>                           AND ind_validation = PREV(ind_validation)

>                           AND id_typ_dif = PREV(id_typ_dif)

>                           AND DECODE(id_assortiment, PREV(id_assortiment),

> 1, 0) = 1))
> WHERE id_typ_dif_rpm IS NOT NULL;

I did a quick comparison of the three runs with the data from v$sql_workarea for the match recognize operatio.

MATCH RECOGNIZE (SORT) Operation
Parallel degree Hash partition count Total time to complete Estimated optimal size Mb Estimated onepasse size Mb Last Memory used Mb Last execution Last degree
16 32 2 min 35 sec 5629 24 316 Optimal 16 16 16 3 min 12 sec 8749 29 354 1 Pass 16 8 16 3 min 12 sec 5673 24 609 Optimal 8
Is there a way (hint or alter session) to force the optimizer to apply the match recognize on the partition level or am I forced to have at least number of partitions 2 times greater than parallel degree ?

Thanks,
Thomas

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

Received on Wed Apr 04 2018 - 14:15:49 CEST

This message: [ Message body ]
Next message: Mark J. Bobak: "Re: Got ASM + RAC 12c class/books?"
Previous message: Jonathan Lewis: "Re: buffer cache structure in SGA"

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

Original text of this message