Warning: session_start(): open(/tmp/sess_q67c9h6tki2l2cpfccp774ede3, O_RDWR) failed: No space left on device (28) in /razi/eraazi/darbari/mercy/index.php on line 15
EraAzi | ENABLE_PARALLEL_DML hint in oracle 12c ENABLE_PARALLEL_DML hint in oracle 12c
 

Till oracle 12c, For doing DML transactions in parallel, we need to enable PDML (parallel DML) at session level.
I.e before any DML statement, we need to issue below statement.

ALTER SESSION ENABLE PARALLEL DML;

-- Then parallel dml statement

insert /*+ parallel(8) */ into TEST2 select * from TEST2;

In oracle 12c, it introduced a hint for parallel dml, ENABLE_PARALLEL_DML , which we can use directly inside the dml sql statement. No need to issuing alter session statement.

It will look as below:

 insert /*+ parallel(8)  enable_parallel_dml */ into TEST2 select * from TEST2;

Lets compare the execution plan with and without ENABLE_PARALLEL_DML

WITHOUT ENABLE_PARALLEL_DML:

SQL> explain plan for insert /*+ parallel(8) */ into TEST2 select * from TEST2;

Explained.


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 2876518734

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |          |   122K|    13M|    82   (2)| 00:00:01 |        |      |            |
|   1 |  LOAD TABLE CONVENTIONAL | TEST2    |       |       |            |          |        |      |            |  ----- > NOT UNDER PX CORDIN..
|   2 |   PX COORDINATOR         |          |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |   122K|    13M|    82   (2)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   4 |     PX BLOCK ITERATOR    |          |   122K|    13M|    82   (2)| 00:00:01 |  Q1,00 | PCWC |            |
|   5 |      TABLE ACCESS FULL   | TEST2    |   122K|    13M|    82   (2)| 00:00:01 |  Q1,00 | PCWP |            |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint
   - PDML is disabled in current session --- >          --- >>>  IT INDICATED PDML IS DISABLED

17 rows selected.

 

WITH ENABLE_PARALLEL_DML hint:

SQL> explain plan for insert /*+ parallel(8) enable_parallel_dml */ into TEST2 select * from TEST2;

Explained.

SQL> set lines 299
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
Plan hash value: 4043334015

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Dist
-----------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          | 61649 |  6863K|    40   (3)| 00:00:01 |        |      |        
|   1 |  PX COORDINATOR                    |          |       |       |            |          |        |      |         
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 | 61649 |  6863K|    40   (3)| 00:00:01 |  Q1,00 | P->S | QC (RAN --- > LOAD IS UNDER PX COORDINATOR
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| TEST2    |       |       |            |          |  Q1,00 | PCWP |        
|   4 |     PX BLOCK ITERATOR              |          | 61649 |  6863K|    40   (3)| 00:00:01 |  Q1,00 | PCWC |        
|   5 |      TABLE ACCESS FULL             | TEST2    | 61649 |  6863K|    40   (3)| 00:00:01 |  Q1,00 | PCWP |        

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 8 because of hint         - PARALLEL IS ENABLED 

16 rows selected.

We can see, With this ENABLE_PARALLEL_DML hint, even without the alter session command, PDML is enabled.

Similarly DISABLE_PARALLEL_DML for disabling the PDML.

 

 

SEE ALSO:

New features of oracle 12.2.

hint ORACLE 12C