 |
|
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
SVRMGRL> ALTER SESSION ENABLE PARALLEL DML;
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)
as
select *
from
owner.customer
order by
customer_last_name
;
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 */
customer_key_idx
on owner.customer
(""customer_last_name"")
TABLESPACE
customer_flip
STORAGE (
INITIAL 3656K
NEXT 640K
MAXEXTENTS UNLIMITED
FREELISTS 80
)
;
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
emp_last_name_idx
rebuild
parallel 23
tablespace
emp_idx
unrecoverable;
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. |
|
