Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Using Parallel DML

Oracle Tips by Burleson

Starting with Oracle version 7.2, Oracle introduced parallel create table as select, parallel query, parallel index building, and parallel update, insert, and delete functionality. Oracle Parallel DML is used exclusively by the Oracle DBA to improve the speed of database maintenance. As we recall, DML is short for Data Manipulation Language, and DML is normally associated with DBA commands such as create table. Here is a list of supported parallel operations.

  • Parallel execution of select statements

  • create table as select (CTAS), alter table

  • create cluster, alter cluster

  • create index

  • Subqueries in all update, insert, and delete SQL statements

  • In Oracle8, we see the following additions to parallelism:

  • Parallel insert (subselect) on partitioned and nonpartitioned tables

  • Parallel delete on partitioned tables

  • Parallel update on partitioned tables

  • Parallel select using rowid

Turning On Parallel DML

Unlike Oracle Parallel Query, there is no Oracle parameter associated with enabling Parallel DML. Parallel DML is only enabled at the session level, and a commit or rollback must be executed prior to enabling a session for Parallel DML.

L 12-22


Note that there is no mention of DEGREE in this syntax. When you specify alter session force parallel DML, it will use a default level of parallelism unless specified in the hint. Using the default means that the parallelization will be determined by the relevant Oracle parameters.

NOTE: Any tables and indexes must have partitions and multiple freelists in order to use Parallel DML. Without multiple freelists, the Parallel DML will “hang” waiting on the segment header, and there will be no improvement in performance.

Parallel DML can be used to speed up insert, update, and delete operations against large database objects. These DML operations are especially useful in a data warehouse environment where tables and indexes tend to be very large. An update or delete statement can be parallelized only on partitioned tables. It is not possible to parallelize these functions on a nonpartitioned table. Once Parallel DML is enabled, an update or delete statement can be parallelized by setting the table with DEGREE>1 or by using a PARALLEL hint in the statement.

Next, let's look at how Parallel DML can be used by the DBA to speed up table reorganizations.

Parallelizing Oracle Table Reorganizations

You can use parallel create table as select (PCTAS) to dramatically reduce the time that it takes to reorganize an Oracle table. For example, in the next listing we create a table with a default PARALLEL option. This directs Oracle to invoke four parallel processes to copy the customer table to new_customer. Also, note the ORDER BY clause, whereby the customer rows are resequenced in the same order as the primary-key index after retrieval of the rows.

L 12-23

create table new_customer

tablespace customer_flip
   storage (initial         500m
            next            50m
            maxextents      unlimited
parallel (degree 23)
select *
order by

When reorganizing very large tables, parallelization of the full table scan can greatly reduce the overall time required to clean up the table. Next let's look at parallel index rebuilding.

Parallel Index Rebuilding

In Oracle, the create index command invokes a full table scan of the target table, so it is appropriate to incorporate parallelism when creating a large index. In the example here, the primary-key index for the customer table is being created with 23 parallel processes, each reading a slice of the table. In this example, we know in advance that the server has 24 CPUs and that the customer table resides in 23 partitions.

L 12-24

create index /* parallel 23 */
on owner.customer
   INITIAL 3656K
   NEXT 640K

CAUTION: Whenever parallel sorting is invoked, the DBA needs to be especially careful about storage within the TEMP tablespace. As parallel sorts are involved with operations such as parallel index creation, a work area will be assigned for each Parallel Query slave according to the value for the initial extent in the TEMP tablespace.

As we discussed in Chapter 10, we can use the rebuild index command to clean up deleted leaf nodes and rebalance a B-tree index. The alter index rebuild command can easily be parallelized, and in this example 23 processes are being dedicated to rebuilding the index:

L 12-25

alter index
parallel 23

Also note that the index is being built unrecoverable, thereby bypassing the overhead of writing to the redo logs. This is a very common practice in large databases since it is nearly double the speed of a traditional index rebuild. Of course, since the index images are not recorded, you must remember to re-create these indexes following a roll-forward recovery operation.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter