Oracle11g parallel DML

Parallel DML applications have significant scalability and performance when accessing large objects in the decisionsuppsystemsystemdss environment. You cannot

Parallel DML applications have significant scalability and performance when accessing large objects in the demo-support system DSS environment. You cannot

Oracle 11g parallel DML (PDML)

Parallel DML applications have significant scalability and performance when accessing large objects in the demo-support system DSS environment.

PDML cannot be used as a feature to speed up OLTP applications. PDML is very useful in large data warehouses, which facilitates batch update of large amounts of data.

Enable PDML

PDML is different from parallel query. PDML cannot be executed unless the displayed request is PDML.

SQL> alter session enable parallel dml;

Session altered.

This table attribute may be parallel, but different from parallel queries, this is not enough for PDML and must be displayed to start PDML in the session.

PDML adopts a pseudo-distributed implementation with some restrictions.

1. triggers are not supported during PDML.

2. During the PDML period, the integrity of the reference declared in some ways is not supported. Because each part of the table is processed as a separate transaction in a separate session. PDML operations do not support self-reference integrity, which may lead to deadlocks.

3. Tables modified with PDML cannot be accessed before submission or rollback.

4. latency constraints are not supported.

5. If it indicates partitioning, PDML can only be executed on a table with a bitmap index or a LOB column. The degree of parallelism depends on the number of partitions. It is impossible to perform parallel operations in the subpartition, because each partition has only one parallel execution server for processing.

6. distributed transactions are not supported when PDML is executed.

7. PDML does not support clustering tables.

Test:

SQL> alter session disable parallel dml;

SQL> explain plan for update/* + PARALLEL (4) */test_ B set object_name = 'aaa ';

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

Plan hash value: 725367477
Bytes ---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes ---------------------------------------------------------------------------------------------------------------
| 0 | update statement | 75339 | 1839K | 81 (0) | 00:00:01 |
| 1 | UPDATE | TEST_ B |
| 2 | px coordinator |
| 3 | px send qc (RANDOM) |: TQ10000 | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 00 | P-> S | QC (RAND) |
| 4 | px block iterator | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 00 | PCWC |
| 5 | table access full | TEST_ B | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 00 | PCWP |
Certificate ---------------------------------------------------------------------------------------------------------------------------------------

-- No real parallel implementation is found.

Enable PDML

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for update/* + parallel (4) */test_ B set object_name = 'bbbbb ';

Explained.

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

Plan hash value: 2467161980

Bytes ------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | TQ | IN-OUT | PQ Distrib |
Bytes ------------------------------------------------------------------------------------------------------------------
| 0 | update statement | 75339 | 1839K | 81 (0) | 00:00:01 |
| 1 | px coordinator |
| 2 | px send qc (RANDOM) |: TQ10001 | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 01 | P-> S | QC (RAND) |
| 3 | index maintenance | TEST_ B | Q1, 01 | PCWP |
| 4 | px receive | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 01 | PCWP |
| 5 | px send range |: TQ10000 | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 00 | P-> P | RANGE |
| 6 | UPDATE | TEST_ B | Q1, 00 | PCWP |
| 7 | px block iterator | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 00 | PCWC |
| 8 | table access full | TEST_ B | 75339 | 1839K | 81 (0) | 00:00:01 | Q1, 00 | PCWP |
Bytes ------------------------------------------------------------------------------------------------------------------

Certificate ---------------------------------------------------------------------------------------------------------------------------------------

Note: During Concurrent INSERT, data is inserted to the table using the APPEND method. If you need to INSERT data in the conventional mode, add the noappend prompt.

Summary:

PDML must be displayed and opened. Only when PDML is enabled can it be a real concurrent operation.

SQL> alter session enable parallel dml;

Close after execution

SQL> alter session disable parallel dml;

Related reading:

Oracle DML Process

PL/SQL ORA-14551: cannot perform DML operations in queries solved

Common MySQL DDL, DML, and DCL languages (example)

Execute batch DML exercises for Oracle basic transactions and ForAll

Oracle DML Statement (insert, update, delete) rollback Estimation