Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 27, 2017 - 10:27 am UTC

Last updated: May 26, 2020 - 5:12 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi,

I need to execute MERGE statement on certain tables on Archival DB., statement looks like
MERGE /*+ PARALLEL(tb_test,pin_max_process) */ INTO tb_test@db_link tgt
USING ( SELECT /*+ PARALLEL(tb_test,pin_max_process) */ * FROM tb_test
WHERE ROWID BETWEEN v_from AND v_to ) src
ON ( condition.. )
WHEN MATCHED THEN
UPDATE
SET ..
WHEN NOT MATCHED THEN
INSERT ( insert_tgt )
VALUES ( insert_src );

Can you explain what is the difference -
if we Execute above MERGE command as is (i.e. PARALLEL hint)?
and
ALTER SESSION ENABLE PARALLEL DML then execute MERGE command with PARALLEL hint??

and Connor said...

Running queries in parallel can be done with hint or automatically based on the table/index definitions.

But to make *changes* in parallel, we need to take some special steps in order to do it. So we need you to tell us that your session wants to do so.

Hence the command:

ALTER SESSION ENABLE PARALLEL DML

If you dont run that comment, then we will happily let you specify

MERGE /*+ PARALLEL */

but we will *not* run the changes in parallel. We can still run the *queries* that make up the merge in parallel, but the *changes* will be done in serial.

So if you want to make changes in parallel...you need to alter your session first.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot!! but one more concern here..

A reader, March 28, 2017 - 5:19 am UTC

Hi Connor,

Thanks for your help.
But I didn't get below statement completely, may be I am asking silly doubts here -

Your statement -
<quote>
but we will *not* run the changes in parallel. We can still run the *queries* that make up the merge in parallel, but the *changes* will be done in serial.

So if you want to make changes in parallel...you need to alter your session first.
<unquote>

when you say "We can still run the *queries* that make up the merge in parallel, but the *changes* will be done in serial" - does it mean MERGE statement will run in PARALLEL but actual modification in tables will not be done in PARALLEL unless we ALTER the session?
Connor McDonald
March 28, 2017 - 12:18 pm UTC

You can think of a merge as a query and an update.

The query could run in parallel...but ultimately the changes will be done in serial (unless you alter the session).

without session being altered

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT         |                 |  6024M|  1183G|    55M  (1)| 00:36:06 |        |      |            |
|   1 |  MERGE                  | T1              |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR        |                 |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)  | :TQ10000        |  6024M|  3506G|    55M  (1)| 00:36:06 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     VIEW                |                 |       |       |            |          |  Q1,00 | PCWP |            |
|   5 |      NESTED LOOPS OUTER |                 |  6024M|  3506G|    55M  (1)| 00:36:06 |  Q1,00 | PCWP |            |
|   6 |       PX BLOCK ITERATOR |                 |       |       |            |          |  Q1,00 | PCWC |            |
|   7 |        TABLE ACCESS FULL| T2              | 77622 |     9M|   234   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|   8 |       VIEW              | VW_LAT_8626BD41 | 77618 |    36M|            |          |  Q1,00 | PCWP |            |
|*  9 |        TABLE ACCESS FULL| T1              | 77618 |     9M|   234   (1)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------


and after session is altered

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |                 |  6024M|  1183G|    13M  (1)| 00:09:04 |        |      |            |
|   1 |  PX COORDINATOR            |                 |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)      | :TQ10001        |  6024M|  3506G|    13M  (1)| 00:09:04 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    MERGE                   | T1              |       |       |            |          |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE             |                 |  6024M|  3506G|    13M  (1)| 00:09:04 |  Q1,01 | PCWP |            |
|   5 |      PX SEND (ROWID RANDOM)| :TQ10000        |  6024M|  3506G|    13M  (1)| 00:09:04 |  Q1,00 | P->P | (ROWID RAND|
|   6 |       VIEW                 |                 |       |       |            |          |  Q1,00 | PCWP |            |
|   7 |        NESTED LOOPS OUTER  |                 |  6024M|  3506G|    13M  (1)| 00:09:04 |  Q1,00 | PCWP |            |
|   8 |         PX BLOCK ITERATOR  |                 |       |       |            |          |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL | T2              | 77622 |     9M|    59   (2)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |         VIEW               | VW_LAT_8626BD41 | 77618 |    36M|            |          |  Q1,00 | PCWP |            |
|* 11 |          TABLE ACCESS FULL | T1              | 77618 |     9M|    59   (2)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------


You can see where the "P=>S" occurs (parallel to serial) in the plan

Answers to : "one more concern here"

Rajeshwaran, March 28, 2017 - 11:16 am UTC 

drop table t1 purge;
drop table t2 purge;

create table t1 as select * from all_objects;
create table t2 as select * from all_objects;

exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');


without parallel dml enabled in a session, if you have a MERGE statement with parallel hints.
a) the "read" portion of the queries will be done in parallel - you can see that step from this Explain plan as "PX BLOCK ITERATOR" before full scan of T1 and T2.
b) the write portion of the queries are NOT done in parallel - since the MERGE operation is above the "PX COORDINATOR"

so each parallel slaves spanned by the coordinator, will scan the T1 and T2, join them in parallel and produce those results to coordinator, and coordinator will take care of modifications (in serial phase)

demo@ORA11G> explain plan for 
  2  merge /*+ parallel(t1,2) parallel(t2,2) */ into t1 using t2
  3  on (t1.object_id = t2.object_id)
  4  when matched then
  5     update set t1.object_name = t2.object_name ;

Explained.

demo@ORA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1241486182

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |          | 75730 |  3180K|   337   (1)| 00:00:05 |        |      |            |
|   1 |  MERGE                     | T1       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR           |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10001 | 75730 |    14M|   337   (1)| 00:00:05 |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                   |          |       |       |            |          |  Q1,01 | PCWP |            |
|*  5 |      HASH JOIN             |          | 75730 |    14M|   337   (1)| 00:00:05 |  Q1,01 | PCWP |            |
|   6 |       PX BLOCK ITERATOR    |          | 75731 |  7247K|   168   (0)| 00:00:03 |  Q1,01 | PCWC |            |
|   7 |        TABLE ACCESS FULL   | T2       | 75731 |  7247K|   168   (0)| 00:00:03 |  Q1,01 | PCWP |            |
|   8 |       BUFFER SORT          |          |       |       |            |          |  Q1,01 | PCWC |            |
|   9 |        PX RECEIVE          |          | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,01 | PCWP |            |
|  10 |         PX SEND BROADCAST  | :TQ10000 | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,00 | P->P | BROADCAST  |
|  11 |          PX BLOCK ITERATOR |          | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,00 | PCWC |            |
|  12 |           TABLE ACCESS FULL| T1       | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

24 rows selected.


when explicitly requested for "Parallel dml" plan changes like this ( the "MERGE" step in the plan is below the query coordinator PX COORDINATOR)

demo@ORA11G> alter session enable parallel dml;

Session altered.

demo@ORA11G> explain plan for
  2  merge /*+ parallel(t1,2) parallel(t2,2) */ into t1 using t2
  3  on (t1.object_id = t2.object_id)
  4  when matched then
  5     update set t1.object_name = t2.object_name ;

Explained.

demo@ORA11G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4094314131

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                 |          | 75730 |  3180K|   337   (1)| 00:00:05 |        |      |            |
|   1 |  PX COORDINATOR                 |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)           | :TQ10002 | 75730 |    14M|   337   (1)| 00:00:05 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    MERGE                        | T1       |       |       |            |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                  |          | 75730 |    14M|   337   (1)| 00:00:05 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HYBRID (ROWID PKEY)| :TQ10001 | 75730 |    14M|   337   (1)| 00:00:05 |  Q1,01 | P->P | HYBRID (ROW|
|   6 |       VIEW                      |          |       |       |            |          |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN                |          | 75730 |    14M|   337   (1)| 00:00:05 |  Q1,01 | PCWP |            |
|   8 |         PX BLOCK ITERATOR       |          | 75731 |  7247K|   168   (0)| 00:00:03 |  Q1,01 | PCWC |            |
|   9 |          TABLE ACCESS FULL      | T2       | 75731 |  7247K|   168   (0)| 00:00:03 |  Q1,01 | PCWP |            |
|  10 |         BUFFER SORT             |          |       |       |            |          |  Q1,01 | PCWC |            |
|  11 |          PX RECEIVE             |          | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,01 | PCWP |            |
|  12 |           PX SEND BROADCAST     | :TQ10000 | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,00 | P->P | BROADCAST  |
|  13 |            PX BLOCK ITERATOR    |          | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,00 | PCWC |            |
|  14 |             TABLE ACCESS FULL   | T1       | 75730 |  7247K|   168   (0)| 00:00:03 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

26 rows selected.

demo@ORA11G>


with parallel dml enabled in a session, if you have a MERGE statement with parallel hints.
a) the "read" portion of the queries will be done in parallel - you can see that step from this Explain plan as "PX BLOCK ITERATOR" before full scan of T1 and T2.
b) the write portion of the queries are done in parallel - since the MERGE operation is now below the "PX COORDINATOR"

When you move into 12c, the explain plan provides information in the "notes" section (about the Parallel dml is not enabled).

demo@ORA12C> create table t1 as select * from all_objects;

Table created.

demo@ORA12C> create table t2 as select * from all_objects;

Table created.

demo@ORA12C> explain plan for
  2  merge /*+ parallel(t1,2) parallel(t2,2) */ into t1 using t2
  3  on (t1.object_id = t2.object_id)
  4  when matched then
  5     update set t1.object_name = t2.object_name ;

Explained.

demo@ORA12C> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
Plan hash value: 1303478760

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT           |          | 60714 |  6166K|   677   (1)| 00:00:01 |        |      |            |
|   1 |  MERGE                    | T1       |       |       |            |          |        |      |            |
|   2 |   PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10001 | 60714 |    15M|   677   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                  |          |       |       |            |          |  Q1,01 | PCWP |            |
|*  5 |      HASH JOIN            |          | 60714 |    15M|   677   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX RECEIVE          |          | 60714 |  8122K|   338   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX SEND BROADCAST  | :TQ10000 | 60714 |  8122K|   338   (1)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|   8 |         PX BLOCK ITERATOR |          | 60714 |  8122K|   338   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   9 |          TABLE ACCESS FULL| T1       | 60714 |  8122K|   338   (1)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |       PX BLOCK ITERATOR   |          | 60715 |  8123K|   338   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|  11 |        TABLE ACCESS FULL  | T2       | 60715 |  8123K|   338   (1)| 00:00:01 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - Degree of Parallelism is 2 because of table property
   - PDML is disabled in current session

28 rows selected.


Also we have a new hint available in 12c "enable_parallel_dml" that helps you to enable the parallel dml at statement level.

demo@ORA12C> conn demo/demo@ora12c
Connected.
demo@ORA12C> explain plan for
  2  merge /*+ enable_parallel_dml parallel(t1,2) parallel(t2,2) */ into t1 using t2
  3  on (t1.object_id = t2.object_id)
  4  when matched then
  5     update set t1.object_name = t2.object_name ;

Explained.

demo@ORA12C> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1850178028

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT             |          | 60714 |  6166K|   376   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR             |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10002 | 60714 |    15M|   376   (1)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    MERGE                    | T1       |       |       |            |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE              |          | 60714 |    15M|   376   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND (ROWID RANDOM) | :TQ10001 | 60714 |    15M|   376   (1)| 00:00:01 |  Q1,01 | P->P | (ROWID RAND|
|   6 |       VIEW                  |          |       |       |            |          |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN BUFFERED   |          | 60714 |    15M|   376   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX BLOCK ITERATOR   |          | 60715 |  8123K|   188   (1)| 00:00:01 |  Q1,01 | PCWC |            |
|   9 |          TABLE ACCESS FULL  | T2       | 60715 |  8123K|   188   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|  10 |         PX RECEIVE          |          | 60714 |  8122K|   188   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |          PX SEND BROADCAST  | :TQ10000 | 60714 |  8122K|   188   (1)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|  12 |           PX BLOCK ITERATOR |          | 60714 |  8122K|   188   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|  13 |            TABLE ACCESS FULL| T1       | 60714 |  8122K|   188   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - Degree of Parallelism is 2 because of table property

29 rows selected.

demo@ORA12C>

Connor McDonald
March 28, 2017 - 12:24 pm UTC

LOL ... I just did the exact same as a review...and then saw this one :-)

"Great minds"

It doesn't matter

Andrew Sayer, March 29, 2017 - 7:24 pm UTC

The merge statement in question is trying to merge into a remote table, the docs tell us this is not allowed for parallel dml:
A DML operation cannot be executed in parallel if it is in a distributed transaction or if the DML or the query operation is on a remote object.
https://docs.oracle.com/database/121/VLDBG/GUID-6626C70C-876C-47A4-8C01-9B66574062D8.htm

I believe the common work around for this is to use dbms_parallel_execute to execute the statement serially but in many sessions - of course you have to be careful with locking.
Connor McDonald
March 30, 2017 - 12:39 am UTC

Excellent point - I totally missed the database link.

INSERT PARALLEL over DB Link

JanS, January 22, 2019 - 8:04 pm UTC

Regarding "A DML operation cannot be executed in parallel if it is in a distributed transaction or if the DML or the query operation is on a remote object."

I tried:

INSERT /*+ APPEND PARALLEL(a,16) ENABLE_PARALLEL_DML */ INTO a SELECT /*+ PARALLEL(b,16) */ * FROM b@db_link

vs.
INSERT /*+ APPEND no parallel */ INTO a SELECT /*+ PARALLEL(b,16) */ * FROM b@db_link

The response time of both INSERTS are the same (the same test without db link is 1:6 difference).

But the one with ENABLE_PARALLEL_DML is parallel also on INSERT side (v$sql_monitor show multiple PX sessions that have some numbers in the Direct_Writes column).

Also, PARALLEL is doing Direct Path Load so the APPEND hint is redundant: If I remove the APPEND hint, the INSERT with ENABLE_PARALLEL_DML does Direct Path Load, the one without ENABLE_PARALLEL_DML does Conventional Load.

So it seems like it does PARALLEL over a DB link, but in SERIAL :). Maybe because of a one db link session?

It would be nice if someone who knows internals can put light on it as this is very confusing - both the docs and the results.

Parallel merge doesnt work here

Martin, May 16, 2020 - 12:38 am UTC

Guys you did a great job here. Thanks a lot for a great article. However I would have one question.

I went through the whole story yesterday on my own, read tons of documentation and got almost the same results as you described here. But....

The only thing I did not achieve is the one with parallel merge (parallel update/insert portion of the merge). I altered the session for parallel dml (enable/force), put the hint (in all the possible combinations (with/without comma, with/without table alias, just with degree spec) right after the merge keyword, made partitions in source/destination, set parallel attribute on objects, but I was not able to run the update/insert portion of the merge statement in parallel at all.

Just to make the whole thing even more obscured... I am able to run update, delete and insert/select in parallel. But the merge not (except the select part, this I can do ).

Please tell me what do I do wrong...

One small yet important detail here. We still run 11.2.0.4 (preparing the app it is using it for a jump to 19). Could this be the issue?
Connor McDonald
May 18, 2020 - 3:01 am UTC

Can we see your test case? Parallel merge *is* available in 11.2

Parallel merge doesnt work here - continued

Martin, May 23, 2020 - 12:01 am UTC

Thank you for the answer. I was preparing the test case when I 've figured out the root cause. We've got a blob column in the table (blobvalue, see below).

MVA@MVATEST>alter session enable parallel dml;

Session altered.

MVA@MVATEST>explain plan for
merge /*+ parallel(4)*/
into tbl_dst_b dst
  using tbl_src_b src
    on (dst.dtype = src.dtype and dst.id = src.id and dst.version = src.version)
  when matched then
   update set dst.validto     = src.validto,
              dst.justupdated = 1,
              dst.modifiedat  = sysdate
  when not matched then
   insert(
          dst.dtype,
          dst.id,
          dst.version,
          dst.guid,
          dst.validfrom,
          dst.validto,
          dst.modifiedat,
          dst.justupdated,
          dst.stringvalue,
          dst.blobvalue
          )
   values
          (
          src.dtype,
          src.id,
          src.version,
          src.guid,
          src.validfrom,
          src.validto,
          src.modifiedAt,
          src.justupdated,
          src.stringvalue,
          src.blobvalue
          );


Explained.

MVA@MVATEST>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 1376614084

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT            |           |    52M|   277G|       |   207K  (1)| 00:06:56 |       |       |        |      |            |
|   1 |  MERGE                     | TBL_DST_B |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR           |           |       |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)     | :TQ10000  |    52M|  9922M|       |   207K  (1)| 00:06:56 |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     VIEW                   |           |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX PARTITION LIST ALL |           |    52M|  9922M|       |   207K  (1)| 00:06:56 |     1 |    28 |  Q1,00 | PCWC |            |
|*  6 |       HASH JOIN RIGHT OUTER|           |    52M|  9922M|   899M|   207K  (1)| 00:06:56 |       |       |  Q1,00 | PCWP |            |
|   7 |        TABLE ACCESS FULL   | TBL_DST_B |    34M|  3205M|       | 36249   (1)| 00:01:13 |     1 |    28 |  Q1,00 | PCWP |            |
|   8 |        TABLE ACCESS FULL   | TBL_SRC_B |    52M|  5036M|       | 56814   (1)| 00:01:54 |     1 |    28 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("DST"."DTYPE"(+)="SRC"."DTYPE" AND "DST"."VERSION"(+)="SRC"."VERSION" AND "DST"."ID"(+)="SRC"."ID")

Note
-----
   - Degree of Parallelism is 4 because of hint

24 rows selected.


MVA@MVATEST>explain plan for
merge /*+ parallel(4)*/
into tbl_dst_b dst
  using tbl_src_b src
    on (dst.dtype = src.dtype and dst.id = src.id and dst.version = src.version)
  when matched then
   update set dst.validto     = src.validto,
              dst.justupdated = 1,
              dst.modifiedat  = sysdate
  when not matched then
   insert(
          dst.dtype,
          dst.id,
          dst.version,
          dst.guid,
          dst.validfrom,
          dst.validto,
          dst.modifiedat,
          dst.justupdated,
          dst.stringvalue
          )
   values
          (
          src.dtype,
          src.id,
          src.version,
          src.guid,
          src.validfrom,
          src.validto,
          src.modifiedAt,
          src.justupdated,
          src.stringvalue
          );


Explained.

MVA@MVATEST>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 2261262066

-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                    |           |    52M|   180G|       |   207K  (1)| 00:06:56 |       |       |        |      |            |
|   1 |  PX COORDINATOR                    |           |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10002  |    52M|  9922M|       |   207K  (1)| 00:06:56 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE               | TBL_DST_B |       |       |       |            |          |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                     |           |    52M|  9922M|       |   207K  (1)| 00:06:56 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANGE                 | :TQ10001  |    52M|  9922M|       |   207K  (1)| 00:06:56 |       |       |  Q1,01 | P->P | RANGE      |
|   6 |       MERGE                        | TBL_DST_B |       |       |       |            |          |       |       |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE                  |           |    52M|  9922M|       |   207K  (1)| 00:06:56 |       |       |  Q1,01 | PCWP |            |
|   8 |         PX SEND HYBRID (ROWID PKEY)| :TQ10000  |    52M|  9922M|       |   207K  (1)| 00:06:56 |       |       |  Q1,00 | P->P | HYBRID (ROW|
|   9 |          VIEW                      |           |       |       |       |            |          |       |       |  Q1,00 | PCWP |            |
|  10 |           PX PARTITION LIST ALL    |           |    52M|  9922M|       |   207K  (1)| 00:06:56 |     1 |    28 |  Q1,00 | PCWC |            |
|* 11 |            HASH JOIN RIGHT OUTER   |           |    52M|  9922M|   899M|   207K  (1)| 00:06:56 |       |       |  Q1,00 | PCWP |            |
|  12 |             TABLE ACCESS FULL      | TBL_DST_B |    34M|  3205M|       | 36249   (1)| 00:01:13 |     1 |    28 |  Q1,00 | PCWP |            |
|  13 |             TABLE ACCESS FULL      | TBL_SRC_B |    52M|  5036M|       | 56814   (1)| 00:01:54 |     1 |    28 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  11 - access("DST"."DTYPE"(+)="SRC"."DTYPE" AND "DST"."VERSION"(+)="SRC"."VERSION" AND "DST"."ID"(+)="SRC"."ID")

Note
-----
   - Degree of Parallelism is 4 because of hint

29 rows selected.

MVA@MVATEST>


but

MVA@MVATEST>insert /*+ parallel(4) */ into tbl_dst_b (dtype, id, version, guid, validfrom, validto, modifiedat, justupdated, stringvalue, blobvalue)
select src.dtype,
       src.id,
       src.version,
       src.guid,
       src.validfrom,
       src.validto,
       src.modifiedat,
       src.justupdated,
       src.stringvalue,
       src.blobvalue
from tbl_src_b src
where not exists (select 1
                  from tbl_dst_b x
                  where x.dtype=src.dtype
                  and x.id=src.id
                  and x.version=src.version
                  );

Explained.

MVA@MVATEST>select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 1986002144

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                  |              |    16M|  2442M|       |   175K  (1)| 00:05:51 |       |       |        |      |            |
|   1 |  PX COORDINATOR                   |              |       |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)             | :TQ10003     |    16M|  2442M|       |   175K  (1)| 00:05:51 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE              | TBL_DST_B    |       |       |       |            |          |       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                    |              |    16M|  2442M|       |   175K  (1)| 00:05:51 |       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND RANGE                | :TQ10002     |    16M|  2442M|       |   175K  (1)| 00:05:51 |       |       |  Q1,02 | P->P | RANGE      |
|   6 |       LOAD AS SELECT              | TBL_DST_B    |       |       |       |            |          |       |       |  Q1,02 | PCWP |            |
|   7 |        PX RECEIVE                 |              |    16M|  2442M|       |   175K  (1)| 00:05:51 |       |       |  Q1,02 | PCWP |            |
|   8 |         PX SEND PARTITION (KEY)   | :TQ10001     |    16M|  2442M|       |   175K  (1)| 00:05:51 |       |       |  Q1,01 | P->P | PART (KEY) |
|*  9 |          HASH JOIN RIGHT ANTI     |              |    16M|  2442M|   515M|   175K  (1)| 00:05:51 |       |       |  Q1,01 | PCWP |            |
|  10 |           PX RECEIVE              |              |    34M|  1668M|       | 23020   (1)| 00:00:47 |       |       |  Q1,01 | PCWP |            |
|  11 |            PX SEND PARTITION (KEY)| :TQ10000     |    34M|  1668M|       | 23020   (1)| 00:00:47 |       |       |  Q1,00 | P->P | PART (KEY) |
|  12 |             PX BLOCK ITERATOR     |              |    34M|  1668M|       | 23020   (1)| 00:00:47 |       |       |  Q1,00 | PCWC |            |
|  13 |              INDEX FAST FULL SCAN | TBL_DST_B_PK |    34M|  1668M|       | 23020   (1)| 00:00:47 |       |       |  Q1,00 | PCWP |            |
|  14 |           PX PARTITION LIST ALL   |              |    52M|  5036M|       | 56814   (1)| 00:01:54 |     1 |    28 |  Q1,01 | PCWC |            |
|  15 |            TABLE ACCESS FULL      | TBL_SRC_B    |    52M|  5036M|       | 56814   (1)| 00:01:54 |     1 |    28 |  Q1,01 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   9 - access("X"."DTYPE"="SRC"."DTYPE" AND "X"."ID"="SRC"."ID" AND "X"."VERSION"="SRC"."VERSION")

Note
-----
   - Degree of Parallelism is 4 because of hint

31 rows selected.

MVA@MVATEST>

Connor McDonald
May 26, 2020 - 5:12 am UTC

Well spotted. That was not a limitation I was aware of.

You could potentially workaround this with DBMS_PARALLEL_EXECUTE
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions