oracle多个instance,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的metalinkRAC: How to control parallel execution across instances in 10g? [ID 1260674.1]