|Oracle8i Parallel Server Administration, Deployment, and Performance
Release 2 (8.1.6)
Part Number A76970-01
This chapter discusses parallel execution and its use in Oracle Parallel Server environments. This chapter includes the following sections:
Although parallel execution does not require Oracle Parallel Server, some aspects of parallel execution described in this chapter apply only to Oracle Parallel Server environments.
When you set the degree of parallelism, consider all of the available CPUs in your cluster. If you use the parallel automatic tuning feature, Oracle sets the other parallel execution-related parameters for you.
Oracle8i Data Warehousing Guide for more information about Parallel Automatic Tuning and setting the degree of parallelism.
Two parameters affect parallel execution in Oracle Parallel Server environments:
Use these parameters together to control how parallel execution uses resources in Oracle Parallel Server environments.
Instance groups simplify administration and allow you to more effectively control which instances participate in parallel execution. Instance groups are sets of instances that you designate for specific purposes, such as parallel execution, OLTP, or data warehousing operations.
For example, you could create instance groups such that between 9 a.m. and 5 p.m. users can access instance group B, but after 5 p.m. they use group D. You could also have users access group C for normal OLTP inserts and updates but have users access group D for large parallel tasks to avoid interfering with OLTP performance.
You must define all instance groups before starting up your database. You cannot dynamically add or delete instances from groups. Because instance groups do not incur significant overhead, you can define an unlimited number of groups. You are also not required to use the instance groups once you define them. An instance can belong to more than one group and groups can overlap one another.
For parallel execution, if you do not use instance groups, Oracle determines which instances participate in parallel execution. Oracle does this based on disk affinity and the number of running instances. The instance from which you initiate a parallelized SQL statement need not be a member of the instance group processing the statement. However, the parallel coordinator runs on the instance from which you submit the SQL statement.
To specify the instance group or groups to which an instance belongs, set the INSTANCE_GROUPS initialization parameter. Do this by listing the names of the instance groups after the parameter and include these entries within the parameter files of each instance you wish to associate to the group or groups. Thus, the INSTANCE_GROUPS parameter simultaneously defines a group and adds the current instance to it.
For example, to define that instance 1 is a member of groups A and B, place the following entry in your database initialization file for instance 1:
To define that instance 2 is a member of groups A and C, place the following entry in your database initialization file for instance 2:
As a result, instances 1 and 2 both belong to instance group A, but they also belong to other groups.
Use the PARALLEL_INSTANCE_GROUP parameter to define which instance groups participate in parallel operations. As with the INSTANCE_GROUPS parameter, the default for PARALLEL_INSTANCE_GROUP is a group comprised of all running instances.
To use a particular instance group for a given parallel operation, specify the following in the common parameter file:
where GROUPNAME is the name of the instance group you designate for parallel operations.
All parallel operations initiated from the instance with this entry in its parameter file spawn processes only within the group defined by GROUPNAME. Unlike settings for INSTANCE_GROUPS, you can change the value for PARALLEL_INSTANCE_GROUP using an ALTER SESSION or ALTER SYSTEM statement. However, you can only use PARALLEL_INSTANCE_GROUP to refer to one instance group. The instance upon which you are running need not be a part of the instance group you are going to use for a particular operation.
In this example, instance 1 has the following settings in its parameter file:
Instance 2 has the following settings in its parameter file:
If you enter the following statements on instance 1, Oracle uses the instances in group GROUPB for parallel execution. Because both instances 1 and 2 are members of group GROUPB, Oracle spawns two server processes on instance 1 and two server processes on instance 2.
However, if you enter the following statements on instance 1, Oracle uses group GROUPC for parallel execution by spawning two server processes on instance 2 only.
This is because instance 1 is not a member of parallel instance group GROUPC.
To make all instances participate in parallel operations, use a blank within single quotes when declaring the parallel instance group. For example, if you enter the following statements on instance 1, Oracle uses the default instance group, or all currently running instances, for parallel processing. Two server processes spawn on instance 1, and two server processes spawn on instance 2.
To see the members of instance groups, query the GV$PARAMETER view and examine entries for the INSTANCE_GROUPS parameter.
Oracle8i Reference for complete information about initialization parameters and views.
The other features of parallel execution that optimize resource use are:
Parallel execution load balancing spreads server processes across instances to balance loads. This improves load balancing for parallel execution and parallel DML operations on multiple instances.
Although you cannot tune this particular aspect of the automated degree of parallelism, you can adjust the database scheduler values to influence the load balancing algorithm of automated parallel execution.
Affinity nodes have loads that are about 10 to 15 percent higher than non-affinity nodes. The load balancing feature uses your vendor-specific cluster manager software to communicate among instances. On Massively Parallel Processing systems, Oracle first populates affinity nodes before populating non-affinity nodes.
As workloads on Oracle Parallel Server systems change, the adaptive multi-user feature alters the degree of parallelism for in coming SQL statements based on the perceived workload across the entire cluster. Oracle adjusts the degree of parallelism based on the number of instances and the current workloads of each instance. To enable this feature, set the PARALLEL_ADAPTIVE_MULTIUSER parameter to TRUE.
If the degree of parallelism for an incoming SQL statement is small enough and if other instances are too busy to accommodate part of the SQL statement, Oracle attempts to place the workload onto one instance instead of dividing it among several. This is because when several instances cooperate to process a SQL statement, they can incur prohibitive intra-node communication costs because instances participating parallel execution must use resources to communicate with each other.
Oracle recommends using the parallel adaptive multi-user feature when users process simultaneous parallel execution operations. If you enable parallel automatic tuning, Oracle automatically sets PARALLEL_ADAPTIVE_MULTI_USER to TRUE.
To avoid disk contention during parallel table scans, stripe the tables across the instances. Do this using either operating system striping on the disks or by creating tablespaces that use files on multiple nodes.
Oracle8i Data Warehousing Guide for more information about parallel execution load balancing and the adaptive multi-user feature.
Use the following performance views to examine parallel execution activity within an Oracle Parallel Server environment:
Disk affinity is available only in systems using a shared nothing approach to disks, making such disks visible globally through an operating system-dependent software layer. Disk affinity determines which instance performs parallelized DML or query operations. Affinity is especially important for parallel DML in Oracle Parallel Server configurations. Affinity information that is consistent across statements improves buffer cache hit ratios and reduces forced reads/writes.
The granularity of parallelism for most parallel DML operations is by partition. For parallel execution, however, granularity is by rowid. Parallel DML operations need partition-to-instance mapping to implement affinity. The segment header of the partition is used to determine the affinity of the partition for Massively Parallel Processing systems. You can achieve improved performance by having nodes access local devices. This provides a better buffer cache hit ratio for every node.
For other Oracle Parallel Server configurations, a deterministic mapping of partitions to instances is used. Partition-to-instance affinity information is used to determine process allocation and work assignments for all Oracle Parallel Server/MPP configurations.