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@SEL$2
9 - SEL$4 / from$_subquery$_003@SEL$3
10 - SEL$4
12 - SEL$4 / A@SEL$4
14 - SEL$4 / B@SEL$4
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE(@"SEL$4" "B"@"SEL$4" NONE NONE)
USE_HASH(@"SEL$4" "B"@"SEL$4")
SUBQUERY_PRUNING(@"SEL$4" "B"@"SEL$4" PARTITION SUBPARTITION)
LEADING(@"SEL$4" "A"@"SEL$4" "B"@"SEL$4")
FULL(@"SEL$4" "B"@"SEL$4")
FULL(@"SEL$4" "A"@"SEL$4")
PQ_DISTRIBUTE_WINDOW(@"SEL$3" 7)
NO_ACCESS(@"SEL$3" "from$_subquery$_003"@"SEL$3")
FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
FULL(@"INS$1" "IA"@"INS$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE_LEAF(@"SEL$F5BB74E1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"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@SEL$4
9 - SEL$1
13 - SEL$2 / from$_subquery$_002@SEL$1
14 - SEL$2
16 - SEL$2 / A@SEL$2
18 - SEL$2 / B@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE(@"SEL$2" "B"@"SEL$2" NONE NONE)
USE_HASH(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
FULL(@"SEL$2" "B"@"SEL$2")
FULL(@"SEL$2" "A"@"SEL$2")
PQ_DISTRIBUTE_WINDOW(@"SEL$1" 7)
NO_ACCESS(@"SEL$1" "from$_subquery$_002"@"SEL$1")
NO_ACCESS(@"SEL$4" "DATA_IND_ULV_BASE_ENS"@"SEL$4")
NO_ACCESS(@"SEL$5" "from$_subquery$_007"@"SEL$5")
NO_ACCESS(@"SEL$3" "from$_subquery$_005"@"SEL$3")
FULL(@"INS$1" "IA"@"INS$1")
OUTLINE_LEAF(@"INS$1")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"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
