afte changing INSTANCE_GROUPS, queries no longer executed in parallel_750645.1

rongshiyuan發表於2014-04-18

After changing the init parameter INSTANCE_GROUPS, queries are no longer being executed in parallel. (Doc ID 750645.1)

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 [Release: 9.2 to 11.1]
Information in this document applies to any platform.

Symptoms

Changed the initialization parameter settings for the parameters INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP. Now the parameters are as follows:

*.instance_groups='MYRAC,MYRAC1,MYRAC2,MYRAC3'
MYRAC1.parallel_instance_group='MYRAC1'
MYRAC2.parallel_instance_group='MYRAC2'
MYRAC3.parallel_instance_group='MYRAC3'

After restarting the instances, parallel execution is disabled on all instances. Parallel query processes do not get spawned even when the execution plan shows parallel.

Changes

Changed the initialization parameter settings for INSTANCE_GROUPS

Cause

Background

The parameters INSTANCE_GROUPS and PARALLEL_INSTANCE_GROUP are used to control parallel execution in RAC instances. 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 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.

If PARALLEL_INSTANCE_GROUP is set to a value which is NOT found in INSTANCE_GROUPS, then parallel execution will be disabled.

In this case, it looks like the value of PARALLEL_INSTANCE_GROUP is part of the list of INSTANCE_GROUPS, so it's not clear why parallel execution is disabled.

However, in point of fact that is NOT the case - the value of PARALLEL_INSTANCE_GROUP is NOT part of the list of INSTANCE_GROUPS. This is due to the placement of single quotes in the value.

The problem is with this setting:

*.instance_groups='MYRAC,MYRAC1,MYRAC2,MYRAC3'

Due to the single quotes, what this setting does is create a SINGLE instance group named 'MYRAC,MYRAC1,MYRAC2,MYRAC3'

This setting created one instance group with a very long name. Then, when setting the
parallel_instance groups as follows:

MYRAC1.parallel_instance_group='MYRAC1'
MYRAC2.parallel_instance_group='MYRAC2'
MYRAC3.parallel_instance_group='MYRAC3'

these groups 'MYRAC1', 'MYRAC2', 'MYRAC3' don't match any of the existing instance groups --
since the only existing instance group is the awkwardly named group 'MYRAC,MYRAC1,MYRAC2,MYRAC3'.

Since the value of parallel_instance_group doesn't match any known instance group, parallel execution is disabled.

This issue is hard to spot because the setting for INSTANCE_GROUPS looks correct, but actually says something different than what was intended.

Solution

Unfortunately the parameter instance_groups is not dynamically modifiable, therefore this requires restart of each instance. However, multiple instances can have different values for INSTANCE_GROUPS, so the restarts can be staggered, thereby avoiding downtime.

1. change the value of instance_groups in the pfile or spfile
eg for spfile:
alter system set instance_groups='MYRAC','MYRAC1','MYRAC2','MYRAC3' SCOPE=SPFILE SID='*' ;

2. restart each instance one at a time (to avoid downtime)

You should now be able to execute queries in parallel again.

NB for 11g: With 11g, parallel query slaves are integrated with Services so that setting up INSTANCE_GROUPS and setting PARALLEL_INSTANCE_GROUP is no longer necessary.

You no longer have to set up the INSTANCE_GROUPS parameter in 11g. Instead of instance groups, you can use the service names directly, eg: ALTER SESSION SET PARALLEL_INSTANCE_GROUP = service_name . The INSTANCE_GROUPS parameter is deprecated in 11g; you can still use it as described in this note, but it is retained for backward compatibility only.

You also do not need to set PARALLEL_INSTANCE_GROUPS explicitly in order to restrict query servers to a subset of instances. If you execute a SQL statement in parallel, the default behaviour is that parallel processes only run on the instances that offer the service with which you originally connected to the database. This does not affect other parallel operations such as parallel recovery or the processing of GV$ queries. To override this behavior, set a value for the PARALLEL_INSTANCE_GROUP initialization parameter.

For more information, see "Administering Services" in Chapter 4 of the Oracle? Real Application Clusters Administration and Deployment Guide for 11.1 .

References

NOTE:201799.1 - Relevant Parallel Excution INIT.ora Parameters
NOTE:47331.1 - Init.ora Parameter "INSTANCE_GROUPS" Reference Note
NOTE:47351.1 - Init.ora Parameter "PARALLEL_INSTANCE_GROUP" Reference Note