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

Cartesian Joins in the cloud or not

Is a Cartesian a join method or a join type?  Turns out that most of us have been thinking that MERGE JOIN CARTESIAN was a join method but it’s not.  And even the oracle documentation has it wrong.  This is one of the small morsels of knowledge that came out of Maria Colgan’s training day at the Hotsos Symposium (#hotsym18) this year. 

Most of the time we see MERGE JOIN CARTESIAN in the plan but you can also have a NESTED LOOPS method as a Cartesian as well.  The problem is that the Cartesian option doesn’t show up for the NESTED LOOPS plan.  So it’s not as obvious that it’s a Cartesian results being produced. 

A HASH join cannot be a Cartesian; it requires an equijoin and hence can’t be used to create a Cartesian result. 

So here’s two simple plans showing the good old sort merge and nested loops producing a Cartesian result.

This plan is producing 739,584 rows from my 27,392 employees table and 27 row departments table, which is 27,392 multiplied by 27.  Unlikely this is really the results I want.

Here is the same Cartesian result being produced by a nested loop join method.  Notice a few things:

• Notice the options is blank (it’s the OPTI… column here)
• There is no join predicate (same as sort merge plan)
• LIOs go from 407 to 13,768
• Elapsed time goes from 155,474ms to 200,157ms

Clearly the sort merge method did a lot less work then the nested loops method.  This means we will likely see these more than the nested loop.  And if you do get the nested loops for a method on a Cartesian it’s not as obvious.   Which means you may have some nested loops in your plans producing Cartesian results and not know it.

Maria has logged a documentation bug on this and hopefully we’ll see the documentation correctly identifying this in the future.