DBA Hub

📋Steps in this guide1/3

ENABLE_PARALLEL_DML hint in oracle 12c - DBACLASS DBACLASS

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 […]

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Till oracle 12c, For doing 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. In oracle 12c, it introduced a hint for parallel dml, , which we can use directly inside the dml sql statement. No need to issuing alter session statement. It will look as below: Lets compare the execution plan with and without

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
ALTER SESSION ENABLE PARALLEL DML;

-- Then parallel dml statement

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

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

Section 2

WITHOUT ENABLE_PARALLEL_DML: WITH ENABLE_PARALLEL_DML hint:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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.

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.
3

Section 3

We can see, With this ENABLE_PARALLEL_DML hint, even without the alter session command, PDML is enabled. Similarly for disabling the PDML. New features of oracle 12.2.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!