Oracle Parallel Query Initialization Parameters Oracle Tips by Burleson Consulting

There are several important init.ora parameters that have a direct impact on the behavior of Oracle parallel query. The values for these parameters are heavily dependent on the number of CPUs and the amount of RAM on your database server.

• sort_area_size The higher the value, the more memory is available for individual sorts on each parallel process. Note that the sort_area_size parameter allocates memory for every query on the system that invokes a sort. For example, if a single query needs more memory, and you increase the sort_area_size, all Oracle tasks will allocate the new amount of sort area, regardless of whether they will use all of the space. It is also possible to dynamically change the sort_area_size for a specific session with the alter session command. This technique can be used when a specific transaction requires a larger sort area than the default for the database.

• parallel_min_servers This value specifies the minimum number of query servers that will be active on the instance. There are system resources involved in starting a query server, and having the query server started and waiting for requests will accelerate processing. Note that if the actual number of required servers is less than the values of parallel_min_servers, the idle query servers will be consuming unnecessary overhead, and the value should be decreased.

• parallel_max_servers This value specifies the maximum number of query servers allowed on the instance. This parameter will prevent Oracle from starting so many query servers that the instance cannot service all of them properly.

• optimizer_percent_parallel This parameter defines the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full-table scan operation.

Note: Cost-based optimization will always be used for any query that references an object with a nonzero degree of parallelism. Hence, you should be careful when setting parallelism if your default is optimizer_mode=RULE.

Setting the Optimal Degree of Parallelism

Determining the optimal degree of parallelism for Oracle tasks is not easy. Because of the highly volatile nature of most SMP systems, there is no general rule that will apply to all situations. As you may know, the degree of parallelism is the number of operating system processes that are created by Oracle to service the query.

Oracle states that the optimal degree of parallelism for a query is based on several factors. These factors are presented in their order of importance:

• The number of CPUs on the server

• The number of physical disks that the table resides upon

• For parallelizing by partition, the number of partitions that will be accessed, based upon partition pruning (if appropriate)

• For parallel DML operations with global index maintenance, the minimum number of transaction freelists among all the global indexes to be updated. The minimum number of transaction freelists for a partitioned global index is the minimum number across all index partitions. This is a requirement in order to prevent self-deadlock.

For example, if your system has 20 CPUs and you issue a parallel query on a table that is stored on 15 disk drives, the default degree of parallelism for your query is 15 query servers.

There has been a great deal of debate about what number of parallel processes results in the fastest response time. As a general rule, the optimal degree of parallelism can be safely set to N-1 where N is the number of processors in your SMP or MPP cluster. Remember, the proper degree of parallelism will always result in faster execution, provided you have a massively parallel server (Figure 10-2) with lots of CPUs.

Figure 2: Execution time as a function of parallel degree

In practice, the best method is a trial-and-error approach that is always verified by timing the query. When tuning a specific query, the Remote DBA can set the query to force a full-table scan and then experiment with different degrees of parallelism until the fastest response time is achieved.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.