Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle and Parallelism

The ability to parallelize operations is one of the most important features of the Very Large Database (VLDB). Database servers with multiple CPUs, which are called symmetric multiprocessing (SMP) machines, are the norm today for most database servers. As performance demands increase and data volumes continue to grow, you will increasingly need to use multiple processors and disks to reduce the time needed to complete a given task. Oracle supports parallelism within a single SMP server as well as parallelism across multiple nodes, using Oracle Parallel Server/Real Application Clusters. Executing a SQL statement in parallel will consume more of all three machine resources—CPU, memory, and disk I/O—but the main potential performance gains fall in the area of disk I/O. Parallelism affects the amount of memory and CPU resources used to execute a given task in a fairly linear fashion. Each parallel execution process has a Program Global Area (PGA) that consumes memory and performs work, which in turn consumes the CPU resources, but disk I/O is the place in which bottlenecks can most readily appear. Parallelism can help reduce these bottlenecks.

Two types of parallelism are possible within an Oracle database:

Block-range parallelism

Driven by ranges of database blocks

Partition-based parallelism

Driven by the number of partitions or subpartitions involved in the operation

The following sections describe these types of parallelism.

Block-Range Parallelism

In 1994, ...