Category Archives: RAC

Parallel Execution on the same node where it started in RAC

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.