ENABLE_PARALLEL_DML hint in the cloud or not

So you want to update that table in parallel eh?  Up until 12.1.01 you had to first alter your session to get Parallel DML, and then issue the statement.  Then turn it off if you didn’t want other DML statements to be done in Parallel.   Not a huge deal of course, but now there is an easier way.

(BTW – Here DML, Data Manipulation Langue, refers only to insert, update, and delete.  Technically a select statement is DML, but not in this particular case.)  

So a little example here, first I check to see if my session has parallel DML enabled.  Then I run an update with the ENABLE_PARALLEL_DML  hint and check the plan.   The database version used in this example is 12.2.0.1.0.

SQL> select PDML_STATUS from v$session WHERE audsid = userenv('sessionid')

  2  /

PDML_STA

--------

DISABLED

SQL>

SQL> update /*+ ENABLE_PARALLEL_DML parallel */

  2  big_tab set EDITION_NAME = 'BOB'

  3  where owner != 'SYS';

SQL>

SQL> SELECT PLAN_TABLE_OUTPUT

  2  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR

  3  ('&psqlid','&PCHILDNO',FORMAT=>'typical allstats last alias'))

  4  /

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------

SQL_ID  1qrndwafn5vrc, child number 5

-------------------------------------

update /*+ ENABLE_PARALLEL_DML parallel */ big_tab set EDITION_NAME =

'BOB' where owner != 'SYS'

Plan hash value: 2335173333

----------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |    TQ  |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT         |          |      1 |        |       |  1622 (100)|          |        |      |            |

|   1 |  PX COORDINATOR          |          |      1 |        |       |            |          |        |      |            |

|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |

|   3 |    INDEX MAINTENANCE     | BIG_TAB  |      0 |        |       |            |          |  Q1,01 | PCWP |            |

|   4 |     PX RECEIVE           |          |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,01 | PCWP |            |

|   5 |      PX SEND RANGE       | :TQ10000 |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,00 | P->P | RANGE      |

|   6 |       UPDATE             | BIG_TAB  |      0 |        |       |            |          |  Q1,00 | PCWP |            |

|   7 |        PX BLOCK ITERATOR |          |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,00 | PCWC |            |

|*  8 |         TABLE ACCESS FULL| BIG_TAB  |      0 |   2353K|   161M|  1622   (1)| 00:00:01 |  Q1,00 | PCWP |            |

----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - UPD$1

   8 - UPD$1 / BIG_TAB@UPD$1

Predicate Information (identified by operation id):

---------------------------------------------------

   8 - access(:Z>=:Z AND :Z<=:Z)

       filter("OWNER"<>'SYS')

Note

-----

   - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit

And boom!  It’s a parallel update!   Rock-n-Roll.  

If I didn’t use the ENABLE_PARALLEL_DML hint but still had the parallel hint, the statement's plan looks like what is below.  Notice that the statement is running “in parallel” but only to find the rows, then the update is applied serially.  Seriously.  Also there is a note now telling me that parallel DML is not enabled.

SQL_ID  8xyhtpc76rwfq, child number 1

-------------------------------------

update /*+ parallel */ big_tab set EDITION_NAME = 'BOB' where owner !=

'SYS'

Plan hash value: 3425284328

-------------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name     | Starts | E-Rows |E-Bytes| E-Time   |    TQ  |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT      |          |      3 |        |       |          |        |      |            |

|   1 |  UPDATE               | BIG_TAB  |      3 |        |       |          |        |      |            |

|   2 |   PX COORDINATOR      |          |      3 |        |       |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)| :TQ10000 |      0 |   2353K|   161M| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   4 |     PX BLOCK ITERATOR |          |      0 |   2353K|   161M| 00:00:01 |  Q1,00 | PCWC |            |

|*  5 |      TABLE ACCESS FULL| BIG_TAB  |      0 |   2353K|   161M| 00:00:01 |  Q1,00 | PCWP |            |

-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access(:Z>=:Z AND :Z<=:Z)

       filter("OWNER"<>'SYS')

Note

-----

   - automatic DOP: Computed Degree of Parallelism is 2

   - PDML is disabled in current session