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

In Oracle Database 11g Release 2, Oracle has changed the database services functionality. In the previous release
of Oracle, database services could be defined on one or more instances in the cluster as preferred and available. This
feature is only available if the admin managed option is selected when defining the database; if the policy managed
option is selected, then database services can be either UNIFORM where the database service will be available on all
instances in the cluster or SINGLETON where it will be available only on one instance in the cluster. In Figure 8-7 , the
database service is defined with the UNIFORM clause, and the parallel execution servers/slaves can start on all or any of
the instances in the cluster.
Another method in which the database services and parallel queries could be controlled from running on all
instances in the cluster is to use the policy managed option and grouping nodes into server pools. Figure 8-8 has two
server pools; the database service SALES is defined as a UNIFORM service in sskypool1 . This means that the parallel
processes will also be confined to run within that sskypool1 pool.

SALES

Node 1 Node 2 Node 3 Node 4 Node 5 Node 6 e 3 QP

QP

2

QC

QP

SSKY1 SSKY2 SSKY3 SSKY4 SSKY5 SSKY6

QP

QP

QP

sskypool1 sskypool2

Figure 8-8. UNIFORM distribution within a server pool

PARALLEL_FORCE_LOCAL
This parameter is more geared toward a RAC environment where multiple instances can participate in a parallel
operation. The parameter controls this behavior by confining the operation to the instance where the statement was
initiated. The default value for this parameter is FALSE , meaning that the query can execute in any or all instances in
the cluster based on the server pool configuration and the database service definitions.

PARALLEL_SERVERS_TARGET
This parameter controls the number of parallel server processes that can be run before the optimizer decides that
there are none left and decides to queue the statement. It is calculated using the following formula:

4* CPU_COUNT * PARALLEL_TREADS_PER_CPU * ACTIVE_INSTANCES

When PARALLEL_DEGREE_POLICY is set to AUTO , statements that require parallel execution are queued if the
number of parallel processes currently in use is equal to or greater than PARALEL_SERVERS_TARGET . Under these
circumstances, statement queuing is used to ensure that each statement that requires parallel execution is allocated
the necessary parallel server resources. That is, statements are queued until such time the required DOP is available.
Statements queued are dequeued for processing using the First In, First Out (FIFO) method.