Oracle RAC also has the dual benefit of scalability along with the role it plays in an Maximum Availability Architecture (MAA) architecture. In this article, I’ll focus on scaling within a single session.
The concept of splitting tasks into smaller parts and executing them in parallel can be implemented on one machine with multiple CPUs, for example, but Oracle RAC goes a step further and gives you the opportunity to extend this concept over several machines. Operations can run in parallel, using one, a subset, or all nodes of a cluster, depending on the workload, the characteristics, and the importance of the query. When a SQL statement is in the processes of being parsed, it gets optimized and parallelized. Once the optimization and parallelization have been completed, the original process becomes the query coordinator. Parallel execution servers (PX servers) are allocated from the pool of PX servers on one or more nodes and start working in parallel on the operation. After the statement has been processed completely, the PX servers return to the pool.
When Parallel Automatic Tuning is enabled, Oracle takes care of all relevant database settings for parallel execution. This is usually at least as efficient as manually derived parameters. However, you can adjust any of the settings, which gives you the flexibility to react to specific situations.
Types of parallelism
Parallel query is the most common of parallel operations but is not the only type. For example, in data warehouse environments, where indexes often need to be rebuilt or large objects have to be created, these operations can be executed as parallel DDL for both partitioned and nonpartitioned objects. Parallel Data Manipulation Language (DML) operations (such as parallel insert, update, and delete) are mainly used to speed up DML operations against large database objects.
This covers the parallel SQL execution, but Oracle uses parallelism for other database operations as well. Parallel recovery and parallel load (SQL*Loader) are two examples of this. When a parallel operation is executed in a service that is allowed to be running only on a subset of the instances, the parallel query slaves will be generated only on the allowed instances. If, for example, a service runs on three out of five nodes, and you execute an operation in parallel, by default the PQ slaves are generated only on the instances where the services are running. This is important to mention, because it is a new feature in Oracle Database 11g Release 1. In 10g Release 2, for example, you could control PQ slaves across instances only via the parameter PARALLEL_INSTANCE_GROUPS. In Oracle Database 11g, the value of the SERVICE_NAMES parameter automatically becomes INSTANCE_GROUPS in which slaves can run. When a session is connected to a particular service_name, and an operation requires a parallel operation, the instance group with the same name as the service name is selected. It is still possible to use the PARALLEL_INSTANCE_GROUP parameter to override this setting. You may also want to consider tuning the PARALLEL_EXECUTION_MESSAGE_SIZE parameter (see the “Interconnect Performance” section later in this chapter) as increasing this parameter value can have a positive impact on the performance of parallelism.
NOTE
In conjunction with parallel execution, Oracle partitioning can be used to improve performance in large environments.
Leave a Reply