RAC: How to Control Parallel Execution Across Database Instances 11g and Higher (Doc ID 1207190.1)

Last updated on FEBRUARY 13, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database - Standard Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Goal

How to control parallel execution across instances in versions 11g and higher

This note covers new 11g features. For 10g, please see Note 1260674.1.

 

Background:

In 10g, parallel execution in RAC instances is controlled with two initialization parameters: INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP.  Both must be used together in 10g.

INSTANCE_GROUPS can have multiple values but it is static; PARALLEL_INSTANCE_GROUP can be modified dynamically at the system level and can also be set at the session level.

In 10g, in order for parallel execution to occur in a session where you have PARALLEL_INSTANCE_GROUP set, the value for PARALLEL_INSTANCE_GROUP has to be found in the list of groups specified by INSTANCE_GROUPS for that instance.

For example, a 3-node RAC in 10gR2 might have the following settings in the spfile:

MYRAC1.instance_groups='MYRAC','MYRAC1'
MYRAC2.instance_groups='MYRAC','MYRAC2'
MYRAC3.instance_groups='MYRAC','MYRAC3'
MYRAC1.parallel_instance_group='MYRAC1' # executions started on node 1 runs only on node 1
MYRAC2.parallel_instance_group='MYRAC2' # executions started on node 2 runs only on node 2
MYRAC3.parallel_instance_group='MYRAC3' # executions started on node 3 runs only on node 3

See Note 1260674.1 for more on the use of INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP in 10g.



In 11g, you can still use INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP in the same way for backward compatability.  However, in 11g, it is no longer necessary to do so.

The INSTANCE_GROUPS parameter is deprecated in 11g and retained for backward compatability only.

The purpose of this note is to describe the new ways in which parallel execution across instances can be controlled in 11g.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms