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%.
During an instance recovery
process, Oracle rolls back uncommitted transactions. Oracle can use
fast-start parallel rollback to increase the efficiency of this
When using fast-start parallel
rollback, the background process SMON is used as a coordinator and
rolls back sets of transactions in parallel, using multiple server
processes. Setting the initialization parameter
fast_start_parallel_rollback controls the number of processes
involved in transaction recovery. Values can be set FALSE,
LOW, or HIGH.
Fast-start parallel rollback is
mainly useful when a system has transactions that run for a long
time before committing, especially parallel INSERT, UPDATE, and
DELETE operations. SMON decides when to begin parallel rollback and
then distributes the task among several parallel processes. Each if
these processes execute one transaction, but they are all done in
When parallel propagation is
utilized, replicated transactions are propagated using multiple,
parallel streams giving higher throughput. Oracle automatically
orders the execution of dependent transactions to preserve data
integrity when necessary. The parallelism parameter should be set to
a minimum of one, but usually higher, in the
dbms_defer_sys.schedule_push procedure in order to configure a
scheduled link with parallel propagation. As an alternative, the
replication management tool should be used to set the parallel
propagation processes control setting to one or higher in the Edit
Push Schedule dialog box.
Oracle RAC and Inter-Instance
Oracle RAC is a multi-instance
single database with a shared storage. Besides providing a very
good, high availability solution, RAC gives load balancing
capabilities and a high performance environment. With RAC, it is
possible for an intra-parallel operation to utilize the processors
across the nodes which gives an additional degree of parallelism
while executing in parallel. For instance, in a two node RAC
cluster, a parallel query can be set up with PARALLEL HINT to
utilize the CPUs from the both instances.
FULL(nydata) PARALLEL(nydata, 3,2) / count(*) FROM nysales;
In this example, the DOP is
three and uses two instances. It is executed with a total of six
processes, three on each instance.
Initialization Parameters at
Shown below are the parameters
that play a role in setting and performing the PE process.
* prallel_max_servers: This
parameter specifies the maximum number of parallel execution
processes and parallel recovery processes for an instance.
* parallel_min_servers: This
parameter is used at startup to specify for a single instance, the
number of processes to be started for parallel operations.
* large_pool_size or
shared_pool_size: When parallel_automatic_tuning is FALSE, Oracle
will allocate the query server processes from the shared pool. In
this case, tune the shared pool, otherwise size the large pool
* parallel_min_percent: Use this
parameter to allow users to wait on an acceptable DOP. Setting this
parameter causes Oracle to return an error if the requested DOP
cannot be satisfied at a given time. The Oracle recommended value
for this parameter is 0 (zero).
This parameter is used to specify the number of instances configured
in a RAC environment. This value is used by Oracle to compute values
for large_pool_size when parallel_automatic_tuning is set to TRUE.
parallel_execution_message_size: This specifies the upper limit for
the size of parallel execution messages. The default will be
specific to the operating system. The default value should be
adequate for most applications. The max recommended value for this
parameter is four KB. Oracle sets this based on
parallel_automatic_tuning. If this is TRUE, the default size is four
KB. When parallel_automatic_tuning is set to FALSE, the setting will
be slightly greater than two KB.
When this parameter is set to TRUE, it causes Oracle to use an
adaptive algorithm which automatically adjusts the requested DOP
based on the system load.
Setting this to TRUE allows Oracle to determine the default values
for parameters that control parallel execution.
When this is set to TRUE, the rows of the small tables involved in
parallel operations are broadcast to each slave.
Monitoring and Diagnosing the
There are quite a number of
dynamic performance tables/views, which helps monitor the PE
process. After a query or DML operation is run, the v$px_process,
v$sesstat, v$pq_slave, v$pq_sesstat, and v$pq_sysstat views can be
used to see the number of server processes used and other
information for the session and system.