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.
rajesh@PDB1>
select * from table(dbms_xplan.display) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash
value: 3050126167
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | |
39 | | | |
| 1 | LOAD TABLE CONVENTIONAL | T1 | |
| | |
| 2 |
PX COORDINATOR | | |
| | |
| 3 |
PX SEND QC (RANDOM) | :TQ10000
| 39 |
Q1,00 | P->S | QC (RAND) |
| 4 |
PX BLOCK ITERATOR | |
39 | Q1,00 | PCWC | |
| 5 |
TABLE ACCESS FULL | T |
39 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 8 because of
table property
- PDML is disabled
in current session
17 rows
selected.
rajesh@PDB1>
We can see that parallel DML is not enabled for the above statement
indicated by the LOAD operation being above the PX COORDINATOR in the plan.
rajesh@PDB1>
explain plan for
2
insert /*+ enable_parallel_dml parallel(t1)
*/ into t1
3
select /*+ parallel(t) */ * from t ;
Explained.
rajesh@PDB1>
rajesh@PDB1>
select * from table(dbms_xplan.display)
;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash
value: 550883001
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | |
39 | | | |
| 1 |
PX COORDINATOR
| | |
| | |
| 2 |
PX SEND QC (RANDOM) |
:TQ10000 | 39 | Q1,00 | P->S | QC (RAND) |
| 3 |
LOAD AS SELECT (HYBRID TSM/HWMB)| T1
| | Q1,00 | PCWP | |
| 4 |
OPTIMIZER STATISTICS GATHERING |
| 39 | Q1,00 | PCWP | |
| 5 |
PX BLOCK ITERATOR
| | 39 |
Q1,00 | PCWC | |
| 6 |
TABLE ACCESS FULL |
T | 39 |
Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 8 because of
table property
17 rows
selected.
rajesh@PDB1>
Here we see that the notes section does not mention parallel DML as
disabled and the LOAD operation is
under the PX COORDINATOR, both of
these indicate that parallel DML is enabled
No comments:
Post a Comment