Oracle中的INSTANCE_GROUPS和PARALLEL_INSTANCE_GROUP

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

These settings mean that executions starting on any node will only run on that node.

Here is another way to use the same instance groups to achieve different results:

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='MYRAC1' # execution started on node 2 runs only on node 1
MYRAC3.parallel_instance_group='MYRAC' # execution started on node 3 runs on all nodes

You can also set parallel_instance_group dynamically in the session.

For example, suppose that you connected to MYRAC1, and the spfile has the first set of settings above:

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

You can set parallel_instance_groups dynamically in your session:

alter session set parallel_instance_group='MYRAC';

Now, queries started in your session will run on all 3 of the nodes.

摘自Oracle的metalink RAC: How to control parallel execution across instances in 10g? [ID 1260674.1]