Oracle parallel DML
Oracle Database Tips by Donald Burleson
DML is very useful when you have an SMP server (lots of independent
CPU processors, see your cpu_count) and multiple I/O channels
on multiple physical disks. If you can bypass disk I/O
bottlenecks by spreading data across multiple disks and controllers
(ideally with a segment partition on each physical spindle), then
parallel DML can greatly improve data loading speeds, especially
using tools like SQL*Loader (sqlldr).
Also, note my tips
high-speed data loading with Oracle.
DML does this by allocating multiple processes, each simultaneously
performing DML (updates, inserts).
You can invoke
parallel DML (i.e. using the PARALLEL and APPEND hint) to have
multiple inserts into the same table.
parallel DML configuration
parallel directives, follow one of the following methods:
FORCE PARALLEL DML.
Use an update,
merge, or delete parallel hint in the statement.
Use a parallel
clause in the definition of the table being updated or deleted
(the reference object).
Once it is decided
to use parallel processing, the maximum degree of parallelism DOP
that can be achieved in delete, merge, and update is equal to:
The number of
partitions (or sub-partitions, in the case of composite
sub-partitions) in the table.
The number of
The number of
execution server can update or merge into, or delete from multiple
partitions, but each partition can only be updated or deleted by one
parallel execution server. Each parallel execution server creates a
different parallel process transaction. As a result, parallel DML
requires more than one rollback segment for performance.
However, a transaction can contain multiple parallel DML statements
that modify different tables, but after a parallel DML statement
modifies a table, no subsequent serial or parallel statement (DML or
query) can access the same table again in that transaction.
append hint and parallel DML
You need to be
careful when deciding to combine an APPEND with a PARALLEL hint.
By using the APPEND hint, you ensure that Oracle always grabs
"fresh" data blocks by raising the high-water-mark for the table.
If you are doing parallel insert DML,
the Append mode is the default and you don't need to specify an
notes "Also, if you're going w/ APPEND, consider putting the
table into NOLOGGING mode, which will allow Oracle to avoid almost
all redo logging."
INSERT optimization, make sure to define multiple freelists (or use
automatic segment space management) and use the SQL "APPEND" option
with the PARALLEL hint.
notes that if you submit parallel jobs to insert against the table
(or same table partition) at the same time, using the APPEND hint
may cause serialization, removing the benefit of parallel
For more details, see my new book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.