Parallel execution of SQL statements is mostly transparent to end users. Oracle divides the task of executing a SQL statement into multiple smaller units, each of which is executed by a separate process. When parallel execution is used, the user’s shadow process takes on the role of the parallel coordinator. The parallel coordinator is also referred to as parallel execution coordinator or query coordinator. The parallel coordinator does the following:
Dynamically divides the work into smaller units that can be parallelized.
Acquires a sufficient number of parallel processes to execute the individual smaller units. These parallel processes are called parallel slave processes. They also are sometimes referred to as parallel execution server processes, parallel server processes, parallel query slaves, or simply slave processes. The most common of the terms, parallel slave processes and slave processes, are used throughout this book.
Assigns each unit of work to a slave process.
Collects and combines the results from the slave processes, and returns those results to the user process.
Releases the slave processes after the work is done.
Oracle maintains a pool of parallel slave processes for each instance. The parallel coordinator for a SQL statement assigns parallel tasks to slave processes from this pool. These parallel slave processes remain assigned to a task until its execution is complete. After that, these processes return to the pool and can be assigned tasks from some other parallel operation. A parallel slave process serves only one SQL statement at a time.
The following parameters control the number of parallel slave processes in the pool:
- PARALLEL_MIN_SERVERS
Specifies the minimum number of parallel slave processes for an instance. When an instance starts up, it creates the specified number of parallel slave processes. The default value for this parameter is 0, meaning that no slave processes would be created at startup.
- PARALLEL_MAX_SERVERS
Specifies the maximum number of parallel slave processes that an instance is allowed to have at one time. The default value for PARALLEL_MAX_SERVERS is platform-specific.
- PARALLEL_SERVER_IDLE_TIME
Sets a limit on the amount of time that a slave process can remain idle before it is terminated. This is specified in minutes. Oracle terminates any parallel slave processes that remain idle for the period of time specified by this parameter. The default value for PARALLEL_SERVER_IDLE_TIME is platform-specific.
Oracle manages parallel slave processes within the limits specified by these three parameters. The number of slave processes in the pool will always be at least that specified by PARALLEL_MIN_SERVERS. More slave processes will be created as necessary, until the maximum specified by PARALLEL_MAX_SERVERS is reached. Excess slave processes will die off as their lifetime exceeds that specified by PARALLEL_SERVER_IDLE_TIME. However, regardless of the idle time setting, the number of slave processes will not be allowed to drop below PARALLEL_MIN_SERVERS.
Tip
The default value for PARALLEL_SERVER_IDLE_TIME is platform-specific, and this default value is adequate for most applications. In Oracle8i, this parameter has been made obsolete and the platform-specific default value is maintained internally by Oracle. Oracle terminates idle parallel slave processes based on this internally set threshold time.
It takes time and resources to create parallel slave processes. Since parallel slave processes can serve only one statement at a time, you should set PARALLEL_MIN_SERVERS to a relatively high value if you need to run lots of parallel statements concurrently. That way, performance won’t suffer from the need to constantly create slave processes.
You also need to consider how to set PARALLEL_MAX_SERVERS. Each parallel slave process consumes memory. Setting PARALLEL_MAX_SERVERS too high may lead to memory shortages during peak usage times. On the other hand, if PARALLEL_MAX_SERVERS is set too low, some operations may not get a sufficient number of parallel slave processes.
The number of parallel slave processes associated with an operation is called its degree of parallelism . Don’t confuse this term with the DEGREE keyword. They aren’t exactly the same thing. In Oracle, the degree of parallelism consists of two components—the number of instances to use and the number of slave processes to use on each instance. In Oracle’s SQL syntax, the keywords INSTANCES and DEGREE are always used to specify values for these two components as follows:
- INSTANCES
Specifies the number of instances to use
- DEGREE
Specifies the number of slave processes to use on each instance
INSTANCES applies only to the Oracle Parallel Server configuration. Unless you are using OPS, the value of INSTANCES should be set to 1; any other value is meaningless. Parallel SQL execution in an OPS environment is discussed in Chapter 13.
The degree of parallelism used for a SQL statement can be specified at three different levels:
- Statement level
Using hints or the PARALLEL clause
- Object level
Found in the definition of the table, index, or other object
- Instance level
Using default values for the instance
Oracle determines the degree of parallelism to use for a SQL statement by checking each item in this list in the order shown. Oracle first checks for a degree of parallelism specification at the statement level. If it can’t find one, it then checks the table or index definition. If the table or index definition does not explicitly specify values for DEGREE and INSTANCES, Oracle uses the default values established for the instance.
The values you specify for DEGREE and INSTANCES can be either integers or the keyword DEFAULT. The keyword DEFAULT tells Oracle to use the default value for the instance.
You can specify the degree of parallelism at the statement level by using hints or by using a PARALLEL clause. PARALLEL and PARALLEL_INDEX hints are used to specify the degree of parallelism used for queries and DML statements. However, DDL statements that support parallel execution provide an explicit PARALLEL clause in their syntax.
In the following example, the PARALLEL hint specifies the degree of parallelism in a SELECT statement. The hint asks Oracle to scan the orders table in parallel using four parallel slave processes, all running on one instance. The hint will override any DEGREE and INSTANCES settings in the table definition and also will override the default degree of parallelism for the instance:
SELECT /*+ PARALLEL(orders,4,1) */ COUNT(*) FROM orders;
Chapter 4, discusses hints and the PARALLEL clause in more detail.
You can specify the degree of parallelism to use for a table or an index when you create it. You do that by using the PARALLEL clause of the CREATE TABLE and CREATE INDEX statements. For example:
CREATE TABLE customers . . . PARALLEL (DEGREE 4 INSTANCES 2);
You also can specify a PARALLEL clause when you alter a table or an index. The following ALTER command changes the order_items table to have a default DEGREE value of 6 and a default INSTANCES value of 1:
ALTER TABLE order_items PARALLEL (DEGREE 6 INSTANCES 1);
When you specify DEGREE and INSTANCES values at the table or index level, those values are used for all SQL statements involving the table or index unless overridden by a hint.
Each instance has associated with it a set of default values for DEGREE and INSTANCES. The default DEGREE value is either the number of CPUs available or the number of disks upon which a table or index is stored, whichever is less. The default INSTANCES value is controlled by the PARALLEL_DEFAULT_MAX_INSTANCES initialization parameter.
Tip
The PARALLEL_DEFAULT_MAX_INSTANCES parameter is obsolete in Oracle8i. Oracle decides the default INSTANCES value based on PARALLEL_INSTANCE_GROUP. PARALLEL_INSTANCE_GROUP is discussed in Chapter 13.
Oracle will use the instance-level defaults whenever the keyword DEFAULT is used in a hint or in a table or index definition. Oracle also will use the instance-level defaults when there are no hints and when no degree of parallelism has been specified at the table or index level.
In previous sections, we discussed how you can specify the degree of parallelism. Oracle, however, doesn’t always honor the degree of parallelism that you request. While the parallel coordinator requests the specified number of parallel slave processes, the actual number of parallel processes used for an operation depends upon the availability of these slave processes. The PARALLEL_MAX_SERVERS parameter sets an upper limit on the number of parallel slave processes that an instance can have. Since you can’t create new parallel slave processes once that limit is reached, it’s possible that not enough of these parallel slave processes will be available to supply the number requested by a given statement. If a statement doesn’t get the requested number of parallel slave processes, one of the following will happen:
If no parallel slave processes are available, the statement will run serially.
If fewer than the requested number of slave processes are available, then Oracle will either return an error or execute the statement with fewer than the requested number of slave processes.
The PARALLEL_MIN_PERCENT parameter is the key to making the decision about whether to run a statement with fewer than the requested number of slave processes. PARALLEL_MIN_PERCENT specifies a minimum percentage of parallel slave processes that must be available in order for a statement to run with a reduced degree of parallelism. As long as the number of available slave processes exceeds this percentage, the statement will execute. Otherwise, the statement will fail and Oracle will return an error.
In a dedicated server architecture, a dedicated server process works on behalf of a user process. Under parallel execution, this shadow process takes the role of parallel coordinator. In a multithreaded server (MTS) architecture, a shared server process works on behalf of the user process. This shared server process then acts as the parallel coordinator during parallel execution, as illustrated in Figure 3.3. All other aspects of parallel execution are the same under MTS as in a dedicated server architecture.
A SQL statement may consist of more than one operation that can be parallelized. For example, if a statement doing a full table scan has an ORDER BY clause, there will be two parallel operations: the scan operation and the sort operation. Oracle not only allows an operation to be executed by multiple parallel processes, it also allows multiple operations from one SQL statement to be executed simultaneously. Execution of a single SQL operation using multiple parallel processes is referred to as intra-operational parallelism. Execution of multiple operations from one SQL statement simultaneously is referred to as inter-operational parallelism . Examples of SQL statements in which inter-operation parallelism occurs include those involving ORDER BY, GROUP BY, hash joins, and sort-merge joins.
Let’s look at an example that illustrates both inter-operational and intra-operational parallelism. The following statement contains an ORDER BY clause and has two operations that can be parallelized: the sort and the table scan. For example:
SELECT * FROM customers ORDER BY cust_name;
Assuming that the degree of parallelism is set to 4, Figure 3.4 depicts the inter-operational and intra-operational parallelism for this example. Four parallel slave processes are used for scanning the table, and four more are used to perform the sort. These two sets of parallel slave processes form a producer-consumer relationship. The parallel slave processes scanning the table act as producers, and the parallel slave processes sorting the rows act as consumers.
As soon as the producers start producing rows from the table scan, the consumers can start sorting those rows. Thus, the scanning and sorting operations can execute in parallel. The parallelization of the individual scan and sort operations using multiple slave processes is a case of intra-operational parallelism. However, performing multiple operations, such as scanning and sorting, in parallel is a case of inter-operational parallelism.
Note that the output of the producers gets redistributed among all the consumers. As Figure 3.4 illustrates, the output of all of the slave processes involved in the scan operation is fed collectively as input into the slave processes performing the sort. Oracle divides the sort operation into four ranges and assigns one range to each parallel slave process. Each slave process then takes the input that falls within its range and sorts it.
Tip
Don’t confuse the inter-query and intra-query parallelism discussed in Chapter 1, with the inter-operational and intra-operational parallelism discussed here. Oracle’s parallel execution is an implementation of intra-query parallelism.
When inter-operational parallelism is occurring, Oracle uses two sets of parallel slave processes to execute a SQL statement. Regardless parallel operations in the statement. The number of parallel processes, therefore, will be twice the specified degree of parallelism. If a SQL statement has three possible parallel operations, the parallel slave processes will execute the first two parallel operations using the two sets of slave processes. After the first operation is complete, the first set of slave processes will execute the third parallel operation.