Parallel DML not used when inserting data with a query using a polymorphic table function
Date: Thu, 24 Oct 2019 15:33:36 +0200
Message-ID: <CAJNN_2QoW7+7S9bGyTMG3zyp9N2Q5ZC==HpkPtF0U6LtefJ-Zg_at_mail.gmail.com>
Hi,
We've recently completed the migration of one of our application from Oracle 11GR2 (11.2.0.4) to Oracle 18c (18.0.0.6) on AIX 7.2.
The application is using a lot of pipeline functions, most of those functions share the same algorithm but applied to different tables / types. Now that the application is running great under Oracle 18c, I'm using new functionalities from Oracle 12 and 18 like match recognize and polymorphic table functions.
With polymorphic table functions I'm able to replace most of my pipeline functions sharing the same algorithm by just one polymorphic table functions.
I'm willing to sacrifice a little performance for reducing code complexity.
I've done a benchmark with the same dataset opposing polymorphic table functions vs pipeline function when loading 30 millions of rows in a table in parallel 16 mode (parallel forced for both query and DML)
WIth a pipeline function my insert take 50 seconds vs 150 seconds with
polymorphic table function.
My polymorphic table function is table semantic and with a partition by
clause to make sure the parallel mode is used.
Here is the plan for both query :
Plan for the query with pipeline function :
| Id | Operation |
Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
| 0 | INSERT STATEMENT
| | 1 | 4 | 2 (0)| 00:00:01
| | | | | |
| 1 | PX COORDINATOR
| | | | |
| | | | | |
| 2 | PX SEND QC (RANDOM) |
:TQ10001 | 1 | 4 | 2 (0)| 00:00:01
| | | Q1,01 | P->S | QC (RAND) |
*| 3 | LOAD AS SELECT (HIGH WATER MARK BROKERED)| ARPR_IND_ULV_BASE_ENS_WRK | | | |
| | | Q1,01 | PCWP | |*
|* 4 | COLLECTION ITERATOR PICKLER FETCH | GEN_IND_ULV_BASE_ENS | 1 | 4 | 2 (0)| 00:00:01
| | | Q1,01 | PCWP | |
| 5 | WINDOW SORT
| | 82 | 16318 | 1847 (2)| 00:00:01
| | | Q1,01 | PCWP | |
| 6 | PX RECEIVE
| | 82 | 16318 | 1845 (2)| 00:00:01
| | | Q1,01 | PCWP | |
| 7 | PX SEND HASH |
:TQ10000 | 82 | 16318 | 1845 (2)| 00:00:01
| | | Q1,00 | P->P | HASH |
| 8 | PX PARTITION HASH ALL
| | 82 | 16318 | 1845 (2)| 00:00:01
| 1 | 32 | Q1,00 | PCWC | |
| 9 | VIEW
| | 82 | 16318 | 1845 (2)| 00:00:01
| | | Q1,00 | PCWP | |
|* 10 | HASH JOIN OUTER
| | 82 | 14104 | 1845 (2)| 00:00:01
| | | Q1,00 | PCWP | |
| 11 | PX PARTITION RANGE SINGLE
| | 82 | 10004 | 2 (0)| 00:00:01 |
KEY | KEY | Q1,00 | PCWC | | |* 12 | TABLE ACCESS FULL | ARPR_IND_ULV_BASE_ENS_PERIM | 82 | 10004 | 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
| 13 | PX PARTITION RANGE SUBQUERY
| | 18M| 870M| 1837 (2)| 00:00:01
|KEY(SQ)|KEY(SQ)| Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL |
ARMS_IND_ULV_BASE_ENS | 18M| 870M| 1837 (2)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
1 - SEL$F5BB74E1 4 - SEL$F5BB74E1 / KOKBF$0_at_SEL$2
9 - SEL$4 / from$_subquery$_003_at_SEL$3
10 - SEL$4 12 - SEL$4 / A_at_SEL$4
14 - SEL$4 / B_at_SEL$4
Outline Data
/*+
BEGIN_OUTLINE_DATA PQ_DISTRIBUTE(_at_"SEL$4" "B"_at_"SEL$4" NONE NONE)
USE_HASH(_at_"SEL$4" "B"_at_"SEL$4")
SUBQUERY_PRUNING(_at_"SEL$4" "B"_at_"SEL$4" PARTITION SUBPARTITION)
LEADING(_at_"SEL$4" "A"_at_"SEL$4" "B"@"SEL$4")
FULL(_at_"SEL$4" "B"_at_"SEL$4")
FULL(_at_"SEL$4" "A"_at_"SEL$4")
PQ_DISTRIBUTE_WINDOW(_at_"SEL$3" 7)
NO_ACCESS(_at_"SEL$3" "from$_subquery$_003"_at_"SEL$3")
FULL(_at_"SEL$F5BB74E1" "KOKBF$0"_at_"SEL$2")
FULL(_at_"INS$1" "IA"_at_"INS$1")
OUTLINE(_at_"SEL$2")
OUTLINE(_at_"SEL$1")
OUTLINE_LEAF(_at_"INS$1")
MERGE(_at_"SEL$2" >"SEL$1")
OUTLINE_LEAF(_at_"SEL$F5BB74E1")
OUTLINE_LEAF(_at_"SEL$3")
OUTLINE_LEAF(_at_"SEL$4")
ALL_ROWS OPT_PARAM('optimizer_dynamic_sampling' 5)
DB_VERSION('18.1.0') OPTIMIZER_FEATURES_ENABLE('18.1.0') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */
Plan for the query with polymorphic function : Plan hash value: 2083180999
| Id | Operation | Name
| E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
| 0 | INSERT STATEMENT |
| 748 | 125K| 6564 (2)| 00:00:01 | | | | | | *| 1 | LOAD AS SELECT | ARPR_IND_ULV_BASE_ENS_WRK | | | | | | | | | |*
| 2 | PX COORDINATOR |
| | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002
| 748 | 125K| 6564 (2)| 00:00:01 | | | Q1,02 | P->S
| QC (RAND) |
|* 4 | POLYMORPHIC TABLE FUNCTION | GEN_MISSING_PERIOD | 748 | 129K| | | | | Q1,02 | PCWC
| |
| 5 | SORT ORDER BY |
| 748 | 129K| | | | | Q1,02 | PCWP
| |
| 6 | PX RECEIVE |
| 748 | 129K| 6564 (2)| 00:00:01 | | | Q1,02 | PCWP
| |
| 7 | PX SEND HASH | :TQ10001
| 748 | 129K| 6564 (2)| 00:00:01 | | | Q1,01 | P->P
| HASH |
| 8 | VIEW |
| 748 | 129K| 6564 (2)| 00:00:01 | | | Q1,01 | PCWP
| |
| 9 | WINDOW SORT |
| 748 | 108K| 6564 (2)| 00:00:01 | | | Q1,01 | PCWP
| |
| 10 | PX RECEIVE |
| 748 | 108K| 6563 (2)| 00:00:01 | | | Q1,01 | PCWP
| |
| 11 | PX SEND HASH | :TQ10000
| 748 | 108K| 6563 (2)| 00:00:01 | | | Q1,00 | P->P
| HASH |
| 12 | PX PARTITION HASH ALL |
| 748 | 108K| 6563 (2)| 00:00:01 | 1 | 32 | Q1,00 | PCWC
| |
| 13 | VIEW |
| 748 | 108K| 6563 (2)| 00:00:01 | | | Q1,00 | PCWP
| |
|* 14 | HASH JOIN | | 748 | 124K| 6563 (2)| 00:00:01 | | | Q1,00 | PCWP
| |
| 15 | PX PARTITION RANGE SINGLE|
| 299K| 34M| 3785 (1)| 00:00:01 | KEY | KEY | Q1,00 | PCWC
| |
|* 16 | TABLE ACCESS FULL | ARPR_IND_ULV_BASE_ENS_PERIM | 299K| 34M| 3785 (1)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |
| 17 | PX PARTITION RANGE ALL |
| 18M| 872M| 2771 (1)| 00:00:01 | 1 |1048575| Q1,00 | PCWC
| |
| 18 | TABLE ACCESS FULL | ARMS_IND_ULV_BASE_ENS
| 18M| 872M| 2771 (1)| 00:00:01 | 1 |1048575| Q1,00 | PCWP
| |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
1 - SEL$3 4 - SEL$5 8 - SEL$1 / DATA_IND_ULV_BASE_ENS_at_SEL$4 9 - SEL$1 13 - SEL$2 / from$_subquery$_002_at_SEL$1 14 - SEL$2 16 - SEL$2 / A_at_SEL$2 18 - SEL$2 / B_at_SEL$2
Outline Data
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE(_at_"SEL$2" "B"_at_"SEL$2" NONE NONE)
USE_HASH(_at_"SEL$2" "B"_at_"SEL$2")
LEADING(_at_"SEL$2" "A"_at_"SEL$2" "B"@"SEL$2")
FULL(_at_"SEL$2" "B"_at_"SEL$2")
FULL(_at_"SEL$2" "A"_at_"SEL$2")
PQ_DISTRIBUTE_WINDOW(_at_"SEL$1" 7)
NO_ACCESS(_at_"SEL$1" "from$_subquery$_002"_at_"SEL$1")
NO_ACCESS(_at_"SEL$4" "DATA_IND_ULV_BASE_ENS"_at_"SEL$4")
NO_ACCESS(_at_"SEL$5" "from$_subquery$_007"_at_"SEL$5")
NO_ACCESS(_at_"SEL$3" "from$_subquery$_005"_at_"SEL$3")
FULL(_at_"INS$1" "IA"_at_"INS$1")
OUTLINE_LEAF(_at_"INS$1")
OUTLINE_LEAF(_at_"SEL$3")
OUTLINE_LEAF(_at_"SEL$5")
OUTLINE_LEAF(_at_"SEL$4")
OUTLINE_LEAF(_at_"SEL$1")
OUTLINE_LEAF(_at_"SEL$2")
ALL_ROWS
OPT_PARAM('optimizer_dynamic_sampling' 5)
DB_VERSION('18.1.0')
OPTIMIZER_FEATURES_ENABLE('18.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
As you can see the load is not done in parallel when using the polymorphic function, when checking v$active_session_history the query is spending most of it's time in the load.
Any idea why the insert is not done in parallel when using the polymorphic table function?
Best regards,
Thomas Ranchon
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 24 2019 - 15:33:36 CEST