This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
Modern relational database
systems are typically architected with parallel capable software
that is well suited to take advantage of the parallel architecture
of SMP systems. The Oracle database system is a multi-process
application in UNIX systems, and is a multi-threaded application
under the Windows architecture.
In general, Databases are
accessed by a large number of simultaneous users or connections.
Many users with their own data and instructions take advantage of
the multi-processor server availability to perform database
processing. Moreover, a single user task, such as a SQL query, can
be parallelized to achieve higher speed and throughput by using
The relational model involves
the utilization of structured tables with rows and columns. Usually
the SQL query aims at extracting or updating some target data, which
is again a set of rows and columns based on a condition. Typically,
any SQL database operation gets divided into multiple database
sub-operations such as selection, join, group, sort, projection,
etc. Thus, the sub-operations become excellent candidates for
simultaneous or parallel execution, making the RDBMS system ideal
parallel processing software.
Databases have a component
called the query optimizer which selects a sequence of inputs,
joins, and scans to produce the desired output table or data set.
The query optimizer is aware of the underlying hardware architecture
to utilize the suitable path for invoking parallel execution. Thus,
from the Database point-of-view, parallel execution is useful for
all types of operations that access significant amounts of data.
Generally, parallel execution
improves performance for:
* Creation of large indexes
* Bulk inserts, updates, and
* Aggregations and copying
Parallel Processing involves the
use of multiple processors to reduce the time needed to complete a
given task. Instead of one processor executing an entire task,
several processors work on separate tasks that are subordinate to
the main task. There are several architectural approaches for
multiple processor systems, they are:
* Symmetric Multi-Processors (SMP)
* Clustered Systems
* NUMA (or DSM - Distributed
Share Model) servers
* MPP (Massively Parallel
Types of Parallelism
There are two types of
parallelism that database users can utilize. They are inter-query
parallelism and intra-query parallelism. The differences between
these two types of parallelism will be covered next.
- Individual transactions are independent, and no transaction
requires the output of another transaction to complete. Many CPUs
can be kept busy by assigning each task or query to a separate CPU.
This type of parallelism, many separate independent queries active
at the same time, is called inter-query parallelism. In OLTP
environment, each query is fairly small, small enough to complete on
a single process utilizing a single CPU.
- To speed up execution of a large, complex query it must first be
decomposed into smaller problems and these smaller problems execute
concurrently (in parallel) by assigning each sub-problem
concurrently to its CPUs. This is called intra-query parallelism.
Decision support systems (DSS) need this kind of facility. Data
warehousing applications often deal with huge data sets, involving
data capture, analysis and summaries, so these operations also
require this capability.
More details about parallel
execution in oracle are covered in the chapter on Parallel