I have 2 GTT tables that are used in a MERGE. When I look at the generated plan, I see that the actual MERGE is still happening in serial. This is not happening for permanent tables.
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL;
TRUNCATE TABLE tt_vik DROP STORAGE;
DROP TABLE tt_vik;
create global temporary table tt_vik on commit preserve rows as
select rownum rnum, cast('TXT' as varchar2(100)) as col
from user_objects;
TRUNCATE TABLE tt_vik1 DROP STORAGE;
DROP TABLE tt_vik1;
create global temporary table tt_vik1 on commit preserve rows as
SELECT /*+PARALLEL+*/ LEVEL just_a_column
FROM dual
CONNECT BY LEVEL <= 365;
MERGE into tt_vik USING
(SELECT just_a_column
FROM tt_vik1
) src
ON (tt_vik.rnum = src.just_a_column)
WHEN MATCHED THEN UPDATE set col = Col||tt_vik.rnum;
SQL_ID 9n9hpxg2xzva4, child number 1
-------------------------------------
MERGE into tt_vik USING (SELECT just_a_column FROM tt_vik1 ) src ON
(tt_vik.rnum = src.just_a_column) WHEN MATCHED THEN UPDATE set col =
Col||tt_vik.rnum
Plan hash value: 3257257279
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | 46 (100)| | | | |
| 1 | MERGE | TT_VIK | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 18777 | 220K| 46 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 4 | VIEW | | | | | | Q1,01 | PCWP | |
|* 5 | HASH JOIN | | 18777 | 220K| 46 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 365 | 1460 | 39 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10000 | 365 | 1460 | 39 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 8 | PX BLOCK ITERATOR | | 365 | 1460 | 39 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS STORAGE FULL| TT_VIK1 | 365 | 1460 | 39 (0)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 18777 | 146K| 7 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 11 | TABLE ACCESS STORAGE FULL | TT_VIK | 18777 | 146K| 7 (0)| 00:00:01 | Q1,01 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("TT_VIK"."RNUM"="JUST_A_COLUMN")
9 - storage(:Z>=:Z AND :Z<=:Z)
11 - storage(:Z>=:Z AND :Z<=:Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
- PDML disabled because temporary tables used
- Global temporary table session private statistics used