Parallel Execution - Expert Oracle Database Architecture - page 655

Chapter 14
Parallel Execution
Parallel execution , a feature of Oracle Enterprise Edition (it is not available in the Standard Edition), was first
introduced in Oracle version 7.1.6 in 1994. It is the ability to physically break a large serial task (any DML, or DDL in
general) into many smaller bits that may all be processed simultaneously. Parallel executions in Oracle mimic the
real-life processes we see all of the time. For example, you would not expect to see a single individual build a house;
rather, many people team up to work concurrently to rapidly assemble the house. In that way, certain operations can
be divided into smaller tasks and performed concurrently; for instance, the plumbing and electrical wiring can take
place concurrently to reduce the total amount of time required for the job as a whole.
Parallel execution in Oracle follows much the same logic. It is often possible for Oracle to divide a certain large
job into smaller parts and to perform each part concurrently. In other words, if a full table scan of a large table is
required, there is no reason why Oracle cannot have four parallel sessions, P001 - P004 , perform the full scan together,
with each session reading a different portion of the table. If the data scanned by P001 - P004 needs to be sorted, this
could be carried out by four more parallel sessions, P005 - P008 , which could ultimately send the results to an overall
coordinating session for the query.
Parallel execution is a tool that, when wielded properly, may result in increased orders of magnitude with regard
to response time for some operations. When it's wielded as a “fast = true” switch, the results are typically quite the
opposite. In this chapter, the goal is not to explain precisely how parallel query is implemented in Oracle, the myriad
combinations of plans that can result from parallel operations, and the like; this material is covered quite well in
Oracle Database Administrator's Guide , Oracle Database Concepts manual, Oracle VLDB and Partitioning Guide ,
and, in particular, Oracle Database Data Warehousing Guide . This chapter's goal is to give you an understanding of
what class of problems parallel execution is and isn't appropriate for. Specifically, after looking at when to use parallel
execution, we will cover
• Parallel query : This is the capability of Oracle to perform a single query using many operating
system processes or threads. Oracle will find operations it can perform in parallel, such as full
table scans or large sorts, and create a query plan that does them in parallel.
• Parallel DML (PDML) : This is very similar in nature to parallel query, but it is used in
reference to performing modifications ( INSERT , UPDATE , DELETE , and MERGE ) using parallel
processing. In this chapter, we'll look at PDML and discuss some of the inherent limitations
associated with it.
• Parallel DDL : Parallel DDL is the ability of Oracle to perform large DDL operations in parallel.
For example, an index rebuild, creation of a new index, loading of data via a CREATE TABLE AS
SELECT , and reorganization of large tables may all use parallel processing. This, I believe, is the
sweet spot for parallelism in the database, so we will focus most of the discussion on this topic.