Oracle Parallel Processing

Parallel DML

Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments where it’s necessary to maintain large summary or historical tables. In OLTP systems, parallel DML sometimes can be used to improve the performance of long-running batch jobs.

Deciding to Parallelize a DML Statement

When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that statement can be parallelized. For UPDATE and DELETE statements, the rules are identical. INSERT statements, however, have their own set of rules.

Tip

To parallelize DML (INSERT, UPDATE, and DELETE) statements, you must enable parallel DML. Refer to the section later in this chapter titled “Enabling Parallel DML” for details.

UPDATE and DELETE statements

Oracle can parallelize UPDATE and DELETE statements on partitioned tables, but only when multiple partitions are involved. You cannot parallelize UPDATE or DELETE operations on a nonpartitioned table or when such operations affect only a single partition. If such a statement includes a subquery, then the decision to parallelize the UPDATE or DELETE operation is made independently of the decision to parallelize the subquery.

UPDATE or DELETE operations are parallelized only if the table being updated or deleted has a parallel specification or if a PARALLEL hint is included after the UPDATE or DELETE keyword in the statement.

INSERT statements

Standard INSERT statements using a VALUES clause cannot be parallelized. Oracle can parallelize only INSERT . . . SELECT . . . FROM statements. The decision to parallelize the INSERT operation is made independently from the decision to parallelize the SELECT operation.

The INSERT operation can be parallelized only if the table being inserted into has a parallel specification or if a PARALLEL hint is included after the INSERT keyword in the statement.

Setting the Degree of Parallelism

As we discussed in Chapter 3, the degree of parallelism can be specified at the statement level, the object definition level, or the instance level. The PARALLEL and PARALLEL_INDEX hints discussed in relation to the SELECT statements apply to other DML statements as well. The following sections describe the rules used to determine the degree of parallelism for DML statements.

UPDATE and DELETE statements

The degree of parallelism to use for an UPDATE or DELETE statement is determined by the following precedence rules:

1. Oracle first retrieves the DEGREE and INSTANCES specifications from the definition of the target table.

2. If the statement contains a parallel hint associated with the UPDATE or DELETE, the hint overrides the specification in the table definition.

The number of partitions in the target table determines the maximum degree of parallelism achievable, because while one parallel slave process can update or delete multiple partitions, only one slave process can update any specific partition. If the specified degree of parallelism is less than the number of partitions, then some parallel slave processes work on more than one partition. As soon as a slave process finishes work on one partition, it will take up one of the remaining partitions. This process continues until the work on the statement is complete. If the specified degree of parallelism is more than the number of partitions targeted, the extra parallel slave processes will sit idle.

INSERT statements

Remember that the only type of INSERT statement that Oracle can parallelize is the INSERT . . . SELECT . . . FROM statement. When Oracle parallelizes such an INSERT statement, the same degree of parallelism is always applied to both the INSERT and the SELECT parts of the statement. The degree of parallelism is determined by the following precedence rules:

1. Oracle retrieves the DEGREE and INSTANCES specifications from all the tables and indexes involved in the SELECT part of the statement. Oracle then chooses the maximum values for those two settings.

2. The DEGREE and INSTANCES specifications from the table into which rows are being inserted overrides the degree of parallelism obtained from Step 1.

3. If the INSERT part of the statement contains a parallel hint, that hint overrides the degree of parallelism obtained from Step 2.

Enabling Parallel DML

Oracle’s parallel DML features are not enabled by default. Before you can execute DML statements in parallel, you must enable the parallel DML feature. You do this at the session level, using the following ALTER SESSION command:

ALTER SESSION ENABLE PARALLEL DML;

After enabling parallel DML support at the session level, you can issue DML statements that execute in parallel. In order for parallel execution to actually occur, at least one of the following must be true:

• The target table in your DML statement must have values greater than 1 for its DEGREE and/or INSTANCES specifications.

• The DML statement must include a parallel hint.

UPDATE and DELETE statements can be parallelized for partitioned tables only.

If a session has pending transactions, you can’t enable or disable parallel DML in the session. For example, in a SQL*Plus session you will see the following error message shown in the following example if you attempt to alter the session to enable (or disable) parallel DML within a transaction:

SQL> UPDATE emp SET COMM = 100 WHERE empno = 7369;

1 row updated.

SQL> ALTER SESSION ENABLE PARALLEL DML;
ERROR:
ORA-12841: cannot alter the session parallel DML state within a transaction

The enabling or disabling of parallel DML in a session does not affect parallel DDL statements nor does it affect parallel SELECT statements. You can execute parallel DDL statements and parallel SELECT statements in a session, regardless of whether parallel DML is enabled or disabled.

Parallel DML Transactions

Transactions involving parallel DML statements differ from those involving only serial statements in at least three areas:

• The visibility of changes made by parallel DML statements

• The amount of rollback information generated

• The number of locks held

Normally, when you use DML statements in a transaction to change data in the database, those changes are visible to subsequent SELECT statements that participate in the transaction. With parallel DML, that’s not the case. The results of parallel DML are not visible to subsequent statements in the same transaction. Not only are the changes not visible, you are not even allowed to select from the tables involved. If you modify (insert, update, or delete) a table using parallel DML, no other query or DML statement (whether serial or parallel) can access the same table in that transaction. Oracle will generate an error if you try to access any table that has been modified previously, in the same transaction, using parallel DML. Here is an example:

SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

SQL> INSERT /*+ PARALLEL (emp_temp,4,1) */ 
  2  INTO emp_temp SELECT * FROM emp;

14 rows created.

SQL> SELECT * FROM emp_temp;
select * from emp_temp
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Parallel DML statements require more rollback segments than equivalent serial DML statements. When DML statements are executed using parallel DML, each parallel slave process creates a separate transaction within Oracle. In addition, the parallel coordinator also creates a transaction for itself. All of these transactions need rollback segments. Therefore, when you are using parallel DML, the rollback segment requirements are much greater than when you are using serial DML. You need to plan ahead for a sufficient number and size of rollback segments when using parallel DML.

Parallel DML statements also require more locks than serial DML statements. When a DML statement is being executed in parallel, each parallel slave process acquires its own locks on the table being modified. The parallel coordinator also acquires locks on the table. The result is that parallel DML statements hold many more locks than comparable serial DML statements. To compensate for this, when using parallel DML, you should increase the values for the DML_LOCKS and ENQUEUE_RESOURCES initialization parameters.

Restrictions on Parallel DML

Parallel DML cannot be used in all circumstances. There are a number of restrictions governing when a given statement can be executed in parallel. Certain table types simply do not support parallel operations. There is also a fairly long list of specific cases in which parallel DML is not an option.

Parallel DML is not supported for the following types of tables:

• Clustered tables

• Tables having object columns or LOB (large object) columns

• Index-organized tables

• Nonpartitioned tables when DELETE or UPDATE statements are being executed

Tip

INSERT . . . SELECT . . . FROM statements can be performed in parallel even on nonpartitioned tables.

In addition to the restrictions on table types, there are a number of specific situations in which a DML statement cannot be parallelized. The restrictions are the following:

• A DML statement that may fire a trigger on a table cannot be parallelized. You must disable the triggers that may be fired by your DML statement if you want to use parallel DML.

Tip

Triggers defined with a WHEN clause are still considered to have fired, even if the WHEN clause prevents the body of the trigger from executing.

• You cannot use parallel DML on tables being replicated.

• A DML statement cannot be parallelized if it encounters any of the following types of table or column constraints:

  • Referential integrity constraints involving DELETE CASCADE

  • Self-referential integrity constraints

  • Deferrable integrity constraints

• A DML statement cannot be parallelized if it modifies or queries a remote object. Also, you cannot access a remote table within a parallel transaction. Here is an example that demonstrates this point:

  SQL> ALTER SESSION ENABLE PARALLEL DML; 
  
  Session altered.
  
  SQL> INSERT /*+ PARALLEL(empx,4) */ INTO empx SELECT * FROM emp;
  
  14 rows created.
  
  SQL> SELECT * FROM dept@test;
  SELECT * FROM dept@test
                        *
  ERROR at line 1:
  ORA-12840: cannot access remote table in a parallel transaction

  The INSERT statement in this example starts a parallel transaction. The next statement within the transaction accesses the remote table dept@test. This is not allowed and Oracle returns an appropriate error.

• Once you modify a table, regardless of whether it was modified in parallel or serially, you cannot use parallel DML on the same table within the same transaction. For example:

  SQL> ALTER SESSION ENABLE PARALLEL DML;
  
  Session altered.
  
  SQL> UPDATE empx SET sal = sal * 1.1;
  
  28 rows updated.
  
  SQL> INSERT /*+ PARALLEL(empx,4) */ INTO empx SELECT * FROM emp;
  INSERT /*+ PARALLEL(empx,4) */ INTO empx SELECT * FROM emp
                                      *
  ERROR at line 1:
  ORA-12839: cannot modify an object in parallel after modifying it

• Parallel DML cannot be used if the initialization parameter ROW_LOCKING is set to INTENT.