The benefits of using parallel operations with an Oracle database have been well established, with the feature first being offered in version 7.1. Parallel execution of SQL statements on traditional Unix-based symmetric multiprocessor (SMP) architectures greatly increases utilization of the server and the speed of large resource-intensive operations. In a Real Application Clusters (RAC) architecture, the equivalent of a parallel SMP deployment is placed into effect and utilizes all the available servers (nodes) in the cluster. Use of parallel operations with RAC greatly enhances the scale-out cluster architecture.
Objectives of Parallel Operations
The objective of a parallel implementation is to use all available resources of the database platform architecture to increase overall processing potential. Resources included in this type of deployment are memory, processor, and I/O. Parallel operations that can be performed in any scale-up or single-system SMP image environment can also be performed in the scale-out RAC cluster environment. Operations that are included are as follows:
- Queries (based on full table scan)
- Create Table As
- Index builds/rebuilds
- DML operations (INSERT, UPDATE, DELETE) on partitioned tables
- Data loads
You can perform the first four operations referenced in this list with the use of SQL hints or by setting the degree of parallelism at the object level. Configure node groups to restrict parallel operations to specific nodes. Therefore, when implementing a large RAC architecture (more than two servers), allocate named servers to named groups to restrict or enable parallel operations.
RAC Parallel Usage Models
Several usage models are available for parallel execution with RAC. Because splitting a query across multiple nodes can degrade performance as well, take care when using the PARALLEL query with RAC! The models included are as follows:
- Standard Use of parallel query for large datasets. In this deployment, the degree of parallelism is usually defined to utilize all of the available resources in the cluster.
- Restricted This deployment restricts processing to specific nodes in the cluster. The referenced nodes can be logically grouped for specific types of operations.
- Parallel index builds/rebuilds In cases where large index builds are required, parallelism can be utilized to maximize the use of cluster node resources.
INIT.ORA Parameters
Several standard parameters can be set to implement parallel processes at the server level. The two general parallel parameters to consider are as follows:
The RAC-specific parameter is as follows:
V$ Views for Viewing Parallel Statistics
Several database views are used to obtain parallel operation statistics. The view names referenced here are prefaced with the GV$ identifier, which depicts the RAC-level statistics:
| View Name | Description |
| GV$PQ_SYSSTAT | All parallel-related statistics for the entire RAC configuration |
| GV$PQ_SESSTAT | Session-specific parallel statistics by session ID |
Parallel Configuration and Associated Baseline Test
The INIT.ORA parameters set in a test environment are listed next. For the examples outlined in this section, I utilized a two-node RAC architecture running under Red Hat Advanced Server 2.1 (I could have also used version 3 or 4). The “*” identifies these as global across all of the RAC instances.
*.PARALLEL_MAX_SERVERS=5
*.PARALLEL_MIN_SERVERS=2
The listing that follows indicates that the TEST1 and TEST2 instances each initiated two parallel background processes at database startup:
A query of the GV$PQ_SYSSTAT table shows the base status of the parallel processes within the Oracle kernel:
Leave a Reply