Parallel Query Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 234

Parallel execution improves performance for the following:

• Queries that perform a full table scan

• Fast full index scans

• The creation of large indexes

• DML operations doing bulk inserts, updates, and deletes

• Aggregations and copying

• Partitioned index scans

• Database recovery

Parallel execution benefits systems have the following characteristics:

• Sufficient I/O bandwidth

• Underutilization (in the sense that the CPUs are mostly idle)

• Sufficient memory to handle multiple requests including sorting, hashing, and additional I/O

buffers

Parallel processing requires additional resources to accommodate the additional processing requests. Lack of these
additional resources to accommodate these requests could potentially cause serialization or queuing, causing slower
performance. There are certain areas of the application where parallel execution will not be beneficial. For example

• Applications that have small singleton transactions, normally retrieving data from one table.

• Systems that have fewer resources available to take on this additional load of parallel

operation. Basically, systems that are already heavy on CPU usage may not be suitable for
parallel executions.

Whereas parallelism is used by the DBA for their DDL activity, larger benefits of parallelism in an everyday
production environment comes from query execution. In the next section, we discuss the parallel query architecture.

Parallel Query Architecture
When a query is executed in parallel, the process that initiates the parallel query is called the query coordinator (QC).
The QC is a server shadow process of the session running the PQ. The main function of the QC is to parse the query
and partition the work between the parallel server (PX) processes.
During the parse operation, both serial and parallel plans are prepared based on the degree of parallelism (DOP).
The QC then attempts to obtain the number of parallel server processes it wants to run. During these attempts, if it
is unable to find sufficient parallel servers, the QC decides to run the query serially or puts the query in a queue; and
when sufficient processes are available, they are released for processing.
If sufficient resources are available, and the QC is able to get the required number of parallel servers, the QC
sends instructions to the PX. The coordination between the QC and PX is done by the mechanism of process queues.
Process queues are also used for communication between two or more PX processes, and this is handled using queue
references.
Queue references are a representation of a link between two process queues. They are always organized in pairs,
one for the process at each end of the link. Each queue reference has four message buffers (three message buffers in
the case of a single instance), which is used to communicate between the processes. Every parallel operation is given
a unique serial number. All the processes involved as a sanity check on incoming messages use this serial number, as
all messages carry this number.