Parallel Execution on the same node where it started in RAC

By default, in an Oracle RAC environment, a SQL statement executed in parallel can run across all of the nodes in the cluster. For this cross-node or inter-node parallel execution to perform, the interconnection in the Oracle RAC environment must be size appropriately because inter-node parallel execution may result in a lot of interconnect traffic. If the interconnection has a considerably lower bandwidth in comparison to the I/O bandwidth from the server to the storage subsystem, it may be better to restrict the parallel execution to a single node or to a limited number of nodes. Inter-node parallel execution does not scale with an undersized interconnection.

10g:

Utilize the parameters instance_group and parallel_instance_group to limit this execution to particular node.

ex.,

Instance#1 parameter:

instance_groups='pqgrp1','pqallnodes';

Instance#2 parameter:

instance_groups='pqgrp2','pqallnodes';

Now, While running the batch process or any program that needs to be executed in only one instance then:

alter session set parallel_instance_group = 'pqgrp1'; -- This will make the following program executions to be used only Instance#1.

11g on-wards:

To limit inter-node parallel execution, you can control parallel execution in an Oracle RAC environment using the PARALLEL_FORCE_LOCAL initialization parameter. By setting this parameter to TRUE, the parallel server processes can only execute on the same Oracle RAC node where the SQL statement was started.