While parallelizing SQL statements in an OPS environment, Oracle doesn’t have to use all of the OPS instances. You can use instance groups to specify which instances to use.
We introduced instance groups in Chapter 7. One of the major applications of instance groups is to specify the instances to use for parallel execution. Assuming that you have used the INSTANCE_GROUPS initialization parameter to group your OPS instances, you can then use the PARALLEL_INSTANCE_GROUP initialization parameter to identify the group that you want Oracle to use when it executes a SQL statement in parallel. This parameter can be specified in the initialization parameter file as shown in this example:
PARALLEL_INSTANCE_GROUP = groupB
All parallel operations initiated from this instance will spawn parallel slave processes on the instances belonging to the group named groupB.
The PARALLEL_INSTANCE_GROUP parameter can be changed while an instance is running using the ALTER SESSION or ALTER SYSTEM command, for example:
ALTER SESSION SET PARALLEL_INSTANCE_GROUP = 'groupB'; ALTER SYSTEM SET PARALLEL_INSTANCE_GROUP = 'groupB';
ALTER SESSION changes the parameter for the current session only, whereas ALTER SYSTEM changes the setting for the current instance. Any changes to the setting using ALTER SYSTEM take effect immediately for all existing sessions of the instance.
The default setting for the PARALLEL_INSTANCE_GROUP parameter is a group composed of all the running instances of the OPS database. If you don’t set this parameter, all available instances will be used for parallel execution.
Let’s expand on the example of a four-instance OPS configuration by showing the effects of different settings for the PARALLEL_INSTANCE_GROUP parameter. Table 13.2 shows the settings for our four instances.
Table 13-2. Instance Group Settings for This Chapter’s Example
|
Instance |
INSTANCE_GROUPS |
PARALLEL_INSTANCE_GROUP |
|---|---|---|
|
1 |
g12, g14, g123 |
g12 |
|
2 |
g12, g23, g123 |
g123 |
|
3 |
g23, g123 |
None |
|
4 |
g14 |
g23 |
Here’s how the groups and instances relate:
When a user runs a parallel SQL statement from instance 1, the parallel slave processes will be spread over the instances in group g12. Group 12 includes instances 1 and 2.
When a user runs a parallel SQL statement from instance 2, group g123 will be used. Group g123 comprises instances 1, 2, and 3.
When a user runs a parallel SQL statement from instance 3, the default instance group consisting of all four instances will be used.
When a user runs a parallel SQL statement from instance 4, instance group g23 will be used, and Oracle will execute the statement using parallel slave processes on instances 2 and 3.
You need to be aware of these characteristics of the PARALLEL_INSTANCE_GROUP setting:
When the value of INSTANCES (whether from a hint or from a table or index definition) is 1, parallel operations always will be executed on the local instance. The PARALLEL_INSTANCE_GROUP setting, if there is one, will be ignored.
When the value of INSTANCES is less than the number of instances in the instance group pointed to by the PARALLEL_INSTANCE_GROUP parameter, Oracle will determine which of those instances actually are used for the parallel operation.
When the value of INSTANCES is more than the number of instances in the instance group pointed to by the PARALLEL_INSTANCE_GROUP parameter, Oracle will still use only the instances in the specified instance group to run the parallel operation.
A parallel operation may be initiated from an instance, and yet that instance may not be one of the instances on which the parallel slave processes for the operation would run. However, the parallel coordinator runs on the initiating instance.
When the parameter PARALLEL_INSTANCE_GROUP is not explicitly set or when the parameter is set to an empty string (” “), Oracle will select from all of the running instances to run the parallel operation.