Parallel Server   «Prev  Next»

Lesson 3Parallel DML
ObjectiveRecognize correct syntax for parallel DML.

Parallel DML

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

Question: How does Oracle Parallel DML differentiate itself from the way in which SQL statements are executed on Oracle Real Application Clusters (RAC)?
In the vast tapestry of Oracle's rich database technology, there exists a nuanced interplay between Oracle Parallel DML and Oracle Real Application Clusters (RAC). While both are iconic in their own right and address scaling and performance challenges, their functioning and primary objectives are rooted in different principles. To fully appreciate their intricacies, one must delve deeper into the heart of each technology and the specific scenarios they are tailored to address.

Oracle Parallel DML (Data Manipulation Language)

Set against the backdrop of a database world clamoring for more speed, Oracle Parallel DML is the hero that scales vertically. It's Oracle's mechanism for executing data manipulation operations – such as inserts, updates, and deletes – by harnessing the power of multiple parallel processes or threads, all within a single database instance. Imagine a maestro conducting a symphony, where each instrument corresponds to a processor or a thread. The true artistry lies in the maestro’s ability to ensure each instrument plays its part cohesively, producing harmonious music. Similarly, Parallel DML ensures that every processor does its share of the work efficiently, speeding up operations that would have been ponderous if executed serially.

Oracle Real Application Clusters (RAC)

On the other hand, we have RAC, Oracle's magnus opus for horizontal scaling. RAC is not just about a single database instance, but rather, it's about having multiple instances of the database running on different nodes or servers, all accessing the same physical database. Picture a vast, bustling market square where numerous vendors (representing the nodes) cater to a sea of customers, all trading and transacting under the same shared space. This is the essence of RAC – a shared everything architecture that boosts availability and scalability.
Now, how do these titans differentiate when it comes to executing SQL statements?
  1. Scope of Parallelism:
    1. Parallel DML: It focuses on dividing a large task within a single database instance into smaller chunks, allowing multiple threads or processes to work on these chunks concurrently. Its purview is to optimize resource utilization inside a single node.
    2. RAC: Here, the entire SQL operation can be executed by different instances on different nodes. The operation is not broken down for parallel execution within an instance; rather, different operations can run on different nodes altogether.
  2. Objective:
    1. Parallel DML: Primarily aims to expedite data manipulation tasks by making optimal use of available resources within a single instance.
    2. RAC: Its raison d'être is ensuring high availability and scalability across multiple instances and nodes. While it does not inherently break down a SQL statement for parallel execution like Parallel DML, RAC ensures that the workload is distributed across the cluster, promoting load balancing.
  3. Failover Mechanisms:
    1. Parallel DML: If a parallel process fails during an operation, that specific process might need to be restarted, or the task might need to be reallocated to another process.
    2. RAC: Boasts a more sophisticated failover mechanism. If an instance or node fails, services are relocated, and operations are rerouted to another node, ensuring continuity.
    In the grand narrative of Oracle's technologies, both Parallel DML and RAC play pivotal roles. Yet, it's imperative to understand that while both deal with the principle of 'parallelism', their methods, intentions, and results are intricately distinct.

Operations on the Oracle Parallel System

In some instances, you may want to simultaneously insert, update, or delete data in the OPS system. Oracle8i 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 MouseOver, you will see syntax and examples of the parallel insert command, parallel update command, and parallel delete command.
Oracle Command 
ALTER SESSION ENABLE PARALLEL DML;

INSERT INTO CUSTOMER_SALES_HISTORY
SELECT * FROM CUSTOMER_SALES
WHERE SALES_DATA > '01-JAN-2020'
  1. This command tells Oracle to consider all transactions following the command for parallel execution. Use this command, or use hints (see below) to enable parallel execution of specific commands.
  2. The table that is modified must be a partitioned table to use parallel execution. The syntax is identical to non-parallel DDL.
  3. In general, you should execute only one parallel execution command per transaction - issue a commit between transactions.
  4. A parallel UPDATE command uses many more locks than a non-parallel (serial) UPDATE command.
  5. This hint tells Oracle to use parallel execution of the command with a degree of parallelism set to five. There are many other hints you can use to customize parallel execution, but this is the main one.

Enable Parallel DML 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.