How to enable parallel DML using hint in oracle 12c

The conventional way of enabling parallel DML is to enable it with an ALTER SESSION command. This command enables parallel DML for the session and all subsequent DML statements are candidates for parallel execution provided that the rules and restrictions (like no FKs, no triggers) for parallel DML are met.

12c introduces a new way of enabling and disabling parallel DML. Rather than enabling or disabling it session-wise you can enable or disable it statement-wise using new hints. The hint ENABLE_PARALLEL_DML enables parallel DML for the statement, and the hint DISABLE_PARALLEL_DML disables it for the statement.

Create a table with some level of parallelism.

SQL> create table DEMO1 parallel 2 as select rownum id , ora_hash(rownum,10) a from xmltable('1 to 1000000');

Table created.

SQL> select count(1) from DEMO1;

  COUNT(1)
----------
   1000000

Create a blank new table demo2

SQL> create table DEMO2 as select * from DEMO1 where null is not null;

Table created.

SQL> select count(1) from DEMO2;

  COUNT(1)
----------
         0

SQL> set pages 0
SQL> set lines 350
SQL> insert into DEMO2 select * from DEMO1;

1000000 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
SQL_ID  bx27xdnkr7dvw, child number 1
-------------------------------------
insert into DEMO2 select * from DEMO1

Plan hash value: 4271246053

------------------------------------------------------
| Id  | Operation                | Name     | E-Rows |
------------------------------------------------------
|   0 | INSERT STATEMENT         |          |        |
|   1 |  LOAD TABLE CONVENTIONAL | DEMO2    |        |
|   2 |   PX COORDINATOR         |          |        |
|   3 |    PX SEND QC (RANDOM)   | :TQ10000 |   1000K|
|   4 |     PX BLOCK ITERATOR    |          |   1000K|
|*  5 |      TABLE ACCESS FULL   | DEMO1    |   1000K|
------------------------------------------------------

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

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

Note
-----
   - Degree of Parallelism is 2 because of table property
   - PDML is disabled in current session
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


30 rows selected.

Here select statement is done in parallel(below the coordinator) as the base table is defined with a parallelism of two. But the insert (LOAD TABLE) is above the coordinator which means that it is done in serial.As per the execution plan's note its clear that PDML(parallel dml) is not happened for this insert statement.

SQL>
SQL> insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1;
insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1
                                                *
ERROR at line 1:
ORA-12839: cannot modify an object in parallel after modifying it


SQL> rollback;

Rollback complete.

Now by using the new 12c feature we can enable parallel DML at query level.

SQL> insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from DEMO1;

1000000 rows created.

SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
SQL_ID  2b8q4k902pbdx, child number 2
-------------------------------------
insert /*+ parallel enable_parallel_dml */ into DEMO2 select * from
DEMO1

Plan hash value: 86785878

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | E-Rows |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |          |        |       |       |          |
|   1 |  PX COORDINATOR                    |          |        |       |       |          |
|   2 |   PX SEND QC (RANDOM)              | :TQ10000 |   1000K|       |       |          |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)|          |        |    38M|    38M| 2068K (0)|
|   4 |     OPTIMIZER STATISTICS GATHERING |          |   1000K|       |       |          |
|   5 |      PX BLOCK ITERATOR             |          |   1000K|       |       |          |
|*  6 |       TABLE ACCESS FULL            | DEMO1    |   1000K|       |       |          |
-------------------------------------------------------------------------------------------

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

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

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


31 rows selected.

SQL>

Here from the note section its clear that oracle doesn't disable the parallelism  and you could see LOAD section is below the coordinator.