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.
