Oracle Parallel DML compared to Oracle Real Application Clusters (RAC)

Oracle "Parallel DML (PDML)" and SQL execution on Oracle Real Application Clusters (RAC) both involve parallel execution, but they have distinct purposes and mechanisms. Here’s how they differ:

1. Parallel DML (PDML)
   • Purpose: Used to speed up data modification operations (INSERT, UPDATE, DELETE, and MERGE) by breaking them into smaller subtasks and executing them concurrently across multiple CPU cores.
   • Execution Mechanism:
   • The operation is split into multiple smaller operations executed in parallel.
   • Each parallel execution server handles a different subset of the rows.
   • Oracle's Parallel Execution (PX) processes are used to distribute workload across available CPU resources within a single database instance or across RAC instances.
   • Resource Utilization:
   • Requires PARALLEL DML to be enabled (ALTER SESSION ENABLE PARALLEL DML).
   • Uses Direct Path Writes for better performance.
   • Utilizes Parallel Execution (PX) Coordinator to manage the parallel processes.
   • Instance Scope: Can be executed within a single instance or across multiple RAC instances (when Parallel Execution with RAC is enabled).
2. SQL Execution in Oracle RAC
   • Purpose: Designed to distribute the execution of SQL queries and transactions across multiple nodes in a clustered environment.
   • Execution Mechanism:
   • Each node in the RAC cluster can execute SQL statements independently.
   • Global Cache Service (GCS) and Global Enqueue Service (GES) manage data consistency across nodes.
   • Uses Cache Fusion for inter-node communication, allowing data blocks to be transferred between instances without disk I/O.
   • Resource Utilization:
   • Typically, RAC relies on load balancing to distribute SQL execution across nodes.
   • Queries are executed where the data is most readily available (e.g., local instance buffers).
   • Parallel Execution in RAC (PQ/RAC) allows parallel queries to run across multiple instances.
   • Instance Scope: Distributed execution across multiple RAC instances, with nodes sharing data via Cache Fusion.

Key Differences

Feature	Parallel DML (PDML)	SQL Execution on RAC
Scope	Works within a single instance or across RAC	Designed for multi-instance SQL execution
Parallelization	Uses Parallel Execution (PX) processes for DML operations	SQL execution is distributed across RAC nodes
Data Transfer	Writes directly to disk (Direct Path Writes)	Uses Cache Fusion for inter-node memory transfers
Performance Optimization	Optimizes large DML operations using multiple CPU cores	Optimizes SQL execution by distributing workload across nodes
Session Requirement	Needs ALTER SESSION ENABLE PARALLEL DML	Uses RAC's internal global services

• Use Parallel DML (PDML) when running bulk DML operations on large tables.
• Use RAC SQL Execution for high availability, workload balancing, and distributed execution in a multi-node cluster.

In some instances, you may want to simultaneously insert, update, or delete data in the OPS system. Oraclei enables you to do these tasks with the Data Manipulation Language (DML) parallel feature. You must study your individual applications and environment to determine whether using this new feature will optimize your system performance. The following is an example where your applications would benefit from using parallel DML. Let’s say that your parallel system has partitioned data along geographic lines to match the ten locations you have. When sales occur in each store, the data from the sales are updated in each store's partition. Your monthly sales account processing handles updates to this data on all the partitions. This means that your batch job’s updates could benefit from parallel DML, in which every partition’s, and thus each store's, updates are done concurrently.
Before you use parallel DML, Oracle recommends that you modify these initialization parameters on your OPS:

1. Set PARALLEL_AUTOMATIC_TUNING to TRUE. This allows the optimizer to choose the degree of parallelism and other factors to speed up processing.
2. Increase ENQUEUE_RESOURCES to allow for more space needed for parallel DML.
3. Increase DML_LOCKS to allow for additional locks used during parallel DML.

In the following diagram, you will see syntax and examples of the parallel insert command, parallel update command, and parallel delete command.

ALTER SESSION ENABLE PARALLEL DML;

INSERT INTO CUSTOMER_SALES_HISTORY
SELECT * FROM CUSTOMER_SALES
WHERE SALES_DATA > '01-JAN-2020'	

Here are some important restrictions that you should consider before using parallel DML:

1. Replication does not support parallel DML.
2. Triggers are not supported for parallel DML.
3. Certain constraints are not allowed for parallel DML: self-referential constraints, delete cascade constraints, and deferred integrity.
4. You cannot use parallel DML with object tables, tables with LOB data, index-organized tables, or clustered tables.

The next lesson covers parallel queries for object tables.