My Oracle Support Banner

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

Last updated on JUNE 04, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database - Standard Edition - Version 11.1.0.6 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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:

<RAC1>.instance_groups='<RAC>','<RAC1>'
<RAC2.>instance_groups='<RAC>','<RAC2>'
<RAC3.>instance_groups='<RAC>','<RAC3>'
<RAC1>.parallel_instance_group='<RAC1>' # executions started on node 1 runs only on node 1
<RAC2>.parallel_instance_group='<RAC2>' # executions started on node 2 runs only on node 2
<RAC3>.parallel_instance_group='<RAC3>' # 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

To view full details, sign in with your My Oracle Support account.

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

In this Document
Goal
 Background:
Solution
 Example
 Valid Values for PARALLEL_INSTANCE_GROUP
 11gR2: PARALLEL_FORCE_LOCAL
 Community Discussions
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.