How to identify Parallel DML is enabled for DML statement

In this post, I am going to show you how to identify Parallel DML is enabled or disabled for DML statement. For this I am going to compare the executions plans with parallel DML enabled and parallel DML Disabled. We will see how to identify whether Parallel DML is enabled for all types of DML statements. I am using oracle 12c for the demonstration of this post, so I am going to use the Oracle 12c hints of Parallel DML. Prior to 12c we need to enable parallel DML at session level.

1. For Insert Statement:

I am generating execution plans for INSERT SELECT statement with Parallel DML enabled and disabled.

--/*********************************Parallel DML ENABLED*****************************/*
20:31:16 SQL> explain plan for
20:36:48   2  INSERT /*+ENABLE_PARALLEL_DML parallel */ INTO TABLE_PARALLEL_TEST
20:36:48   3  SELECT * FROM TABLE_PARALLEL_TEST;

Explained.

20:36:48 SQL> @x

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2734430036

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time  |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |                     | 91822 |    10M|   238   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                    |                     |       |       |            |       |   |      |            |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000            | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TABLE_PARALLEL_TEST |       |       |            |       |  Q1,00 | PCWP |       |
|   4 |     OPTIMIZER STATISTICS GATHERING |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR             |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL            | TABLE_PARALLEL_TEST | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

17 rows selected.

--/*********************************Parallel DML DISABLED*****************************/*
20:36:50 SQL> explain plan for
20:37:03   2  INSERT /*+DISABLE_PARALLEL_DML parallel */ INTO TABLE_PARALLEL_TEST
20:37:03   3  SELECT * FROM TABLE_PARALLEL_TEST;

Explained.

20:37:04 SQL> @x

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2259503135

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                     | 91822 |    10M|   238   (0)| 00:00:01 |     |      |       |
|   1 |  LOAD TABLE CONVENTIONAL | TABLE_PARALLEL_TEST |       |       |            |          |     |      |       |
|   2 |   PX COORDINATOR         |                     |       |       |            |          |     |      |       |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000            | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL   | TABLE_PARALLEL_TEST | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session

17 rows selected.

The above generated plans seems to be executing in parallel but there is one difference between them. In first execution plan LOAD AS SELECT operation is below the PX COORDINATOR and in second execution plan LOAD TABLE is above PX COORDINATOR. In first plan, INSERT statement as well as SELECT statement are executing in PARALLEL. In Second execution plan, only select statement is running in parallel. If you are using Oracle 12c and PDML is disabled, Note section of plan shows us that “PDML is disabled in current session”.
If LOAD SELECT Statement is below PX COORDINATOR then PDML is enabled for the session. If your LOAD TABLE Statement is above PX COORDINATOR then PDML is disabled for the session.

2. For Delete Statement:

Now let’s check PDML is enabled or disabled with DELETE statement. I am generating execution plans for both the scenarios.

--/*********************************Parallel DML ENABLED*****************************/*
20:10:51 SQL> explain plan for
20:14:59   2  delete /*+ENABLE_PARALLEL_DML parallel */ from TABLE_PARALLEL_TEST;

Explained.

20:15:03 SQL> @x

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 1570925556

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                     | 91822 |   238   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR       |                     |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000            | 91822 |   238   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    DELETE             | TABLE_PARALLEL_TEST |       |            |          |  Q1,00 | PCWP |            |
|   4 |     PX BLOCK ITERATOR |                     | 91822 |   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| TABLE_PARALLEL_TEST | 91822 |   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

16 rows selected.

--/*********************************Parallel DML DISABLED*****************************/*
20:15:04 SQL> explain plan for
20:15:31   2  delete /*+DISABLE_PARALLEL_DML parallel */ from TABLE_PARALLEL_TEST;

Explained.

20:15:33 SQL> @x

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 2490469205

------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |                     | 91822 |   238   (0)| 00:00:01 |        |      |            |
|   1 |  DELETE               | TABLE_PARALLEL_TEST |       |            |          |        |      |            |
|   2 |   PX COORDINATOR      |                     |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000            | 91822 |   238   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR |                     | 91822 |   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL| TABLE_PARALLEL_TEST | 91822 |   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session

17 rows selected.

20:15:35 SQL>

In first execution plan, DELETE Operation is below PX COORDINATOR which tells us that PDML is enabled. In second execution plan, DELETE Operation is above PX COORDINATOR which tells us that PDML is disabled. Also in second execution plan, Note section tells us that PDML is disabled.

3. For Update Statement:

Now lets check PDML is enabled or disabled with UPDATE statement. I am generating execution plans for both the scenarios.

--/*********************************Parallel DML ENABLED*****************************/
20:39:34 SQL> explain plan for
20:39:37   2  update /*+ENABLE_PARALLEL_DML parallel */ TABLE_PARALLEL_TEST
20:39:38   3  set OBJECT_NAME = OBJECT_NAME || ROWNUM;

Explained.

20:39:39 SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1235474706

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |                     | 91822 |  2241K|   238   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                  |                     |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002            | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    UPDATE                        | TABLE_PARALLEL_TEST |       |       |            |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                   |                     | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10001            | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,01 | S->P | HASH (BLOCK|
|   6 |       BUFFER SORT                |                     | 91822 |  2241K|            |          |  Q1,01 | SCWP |            |
|   7 |        COUNT                     |                     |       |       |            |          |  Q1,01 | SCWP |            |
|   8 |         PX RECEIVE               |                     | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,01 | SCWP |            |
|   9 |          PX SEND 1 SLAVE         | :TQ10000            | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,00 | P->S | 1 SLAVE    |
|  10 |           PX BLOCK ITERATOR      |                     | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |            TABLE ACCESS FULL     | TABLE_PARALLEL_TEST | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

22 rows selected.

--/*********************************Parallel DML DISABLED*****************************/*
20:39:41 SQL>
20:40:03 SQL> explain plan for
20:40:06   2  update /*+DISABLE_PARALLEL_DML parallel */ TABLE_PARALLEL_TEST
20:40:07   3  set OBJECT_NAME = OBJECT_NAME || ROWNUM
20:40:07   4  ;

Explained.

20:40:10 SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 287822989

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |                     | 91822 |  2241K|   238   (0)| 00:00:01 |        |      |            |
|   1 |  UPDATE                | TABLE_PARALLEL_TEST |       |       |            |          |        |      |            |
|   2 |   COUNT                |                     |       |       |            |          |        |      |            |
|   3 |    PX COORDINATOR      |                     |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)| :TQ10000            | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   5 |      PX BLOCK ITERATOR |                     | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| TABLE_PARALLEL_TEST | 91822 |  2241K|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session

18 rows selected.

20:40:11 SQL>

In first execution plan, UPDATE Operation is below PX COORDINATOR which tells us that PDML is enabled. In second execution plan, UPDATE Operation is above PX COORDINATOR which tells us that PDML is disabled. Also in second execution plan, Note section tells us that PDML is disabled.

4. For Merge Statement:

Now lets check PDML is enabled or disabled with MERGE statement. I am generating execution plans for both the scenarios.

--/*********************************Parallel DML ENABLED*****************************/*

21:01:05 SQL> explain plan for
21:01:09   2  Merge /*+ ENABLE_PARALLEL_DML parallel*/ into TABLE_PARALLEL_TEST A
21:01:10   3  using TABLE_PARALLEL_TEST B
21:01:10   4  on (A.OBJECT_ID = B.OBJECT_ID)
21:01:10   5  WHEN Matched then
21:01:10   6  update set a.object_name = b.object_name
21:01:10   7  WHEN NOT Matched then
21:01:10   8  INSERT (object_id,object_name) values(b.object_id,b.object_name);

Explained.

21:01:10 SQL> @x

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1326459086

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                |                     | 91822 |    16M|   477   (1)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                |                     |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)          | :TQ10002            | 91822 |    20M|   477   (1)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    MERGE                       | TABLE_PARALLEL_TEST |       |       |            |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                 |                     | 91822 |    20M|   477   (1)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND (ROWID RANDOM)    | :TQ10001            | 91822 |    20M|   477   (1)| 00:00:01 |  Q1,01 | P->P | (ROWID RAND|
|   6 |       VIEW                     |                     |       |       |            |          |  Q1,01 | PCWP |            |
|*  7 |        HASH JOIN OUTER BUFFERED|                     | 91822 |    20M|   477   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX BLOCK ITERATOR      |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   9 |          TABLE ACCESS FULL     | TABLE_PARALLEL_TEST | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  10 |         PX RECEIVE             |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|  11 |          PX SEND BROADCAST     | :TQ10000            | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|  12 |           PX BLOCK ITERATOR    |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  13 |            TABLE ACCESS FULL   | TABLE_PARALLEL_TEST | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------

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

   7 - access("A"."OBJECT_ID"(+)="B"."OBJECT_ID")

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

29 rows selected.


--/*********************************Parallel DML DISABLED*****************************/*
21:01:42 SQL> explain plan for
21:01:45   2  Merge /*+ DISABLE_PARALLEL_DML parallel*/ into TABLE_PARALLEL_TEST A
21:01:45   3  using TABLE_PARALLEL_TEST B
21:01:45   4  on (A.OBJECT_ID = B.OBJECT_ID)
21:01:45   5  WHEN Matched then
21:01:45   6  update set a.object_name = b.object_name
21:01:45   7  WHEN NOT Matched then
21:01:45   8  INSERT (object_id,object_name) values(b.object_id,b.object_name);

Explained.

21:01:45 SQL> @x

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2436683564

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     | TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT              |                     | 91822 |    16M|   477   (1)| 00:00:01 |     |         |            |
|   1 |  MERGE                       | TABLE_PARALLEL_TEST |       |       |            |          |     |         |            |
|   2 |   PX COORDINATOR             |                     |       |       |            |          |     |         |            |
|   3 |    PX SEND QC (RANDOM)       | :TQ10001            | 91822 |    20M|   477   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   4 |     VIEW                     |                     |       |       |            |          |  Q1,01 | PCWP |            |
|*  5 |      HASH JOIN OUTER BUFFERED|                     | 91822 |    20M|   477   (1)| 00:00:01 |  Q1,01 | PCWP |            |
|   6 |       PX BLOCK ITERATOR      |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   7 |        TABLE ACCESS FULL     | TABLE_PARALLEL_TEST | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |       PX RECEIVE             |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   9 |        PX SEND BROADCAST     | :TQ10000            | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | P->P | BROADCAST  |
|  10 |         PX BLOCK ITERATOR    |                     | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |          TABLE ACCESS FULL   | TABLE_PARALLEL_TEST | 91822 |    10M|   238   (0)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------

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

   5 - access("A"."OBJECT_ID"(+)="B"."OBJECT_ID")

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session

28 rows selected.

21:01:47 SQL>

In first execution plan, MERGE Operation is below PX COORDINATOR which tells us that PDML is enabled. In second execution plan, MERGE Operation is above PX COORDINATOR which tells us that PDML is disabled. Also in second execution plan, Note section tells us that PDML is disabled.

How to identify Parallel DML is enabled for DML statement

Leave a Reply