Setting the Optimal Degree of Parallelism Oracle Tips by Burleson Consulting

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 decreasing  order of importance:

• The number of CPUs on the server

• The number of physical disks that the tables resides on

• 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.

In practice, the best method is a trial-and-error approach. 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.

Finding the Number of CPUs on Your Database Server

Sometimes the Oracle Remote DBA does not know the number of CPUs on the database server. The following UNIX commands can be issued to report on the number of CPUs on the database server.

Windows NT

If you are using MS-Windows NT, you can find the number of CPUs by entering the Control Panel and choosing the System icon.

Linux

To see the number of CPUs on a Linux server, you can cat the /proc/cpuinfo file. In the example that follows, we see that our Linux server has four CPUs:

>cat /proc/cpuinfo|grep processor|wc -l
      4

Solaris

In Sun Solaris, the prsinfo command can be used to count the number of CPUs on the processor.

>psrinfo -v|grep "Status of processor"|wc -l
       24

IBM-AIX

The following example, taken from an AIX server, shows that the server has four CPUs:

>lsdev -C|grep Process|wc –l

       36

HP/UX

In HP UNIX, you can use the glance or top utilities to display the number of CPUs.

Note: Parallel hints will often speed up index creation even on single-processor machines. This is not because there is more processing power available, but because there is less I/O wait contention with multiple processes. On the other end of the spectrum, we generally see diminishing elapsed time when the degree of parallelism exceeds the number of processors in the cluster.

There are several formulas for computing the optimal parallelism. Oracle provides a formula for computing the optimal parallelism based on the number of CPUs and the number of disks that the file is striped onto. Assume that D is the number of devices that P is striped across (either SQL*loader striping or OS striping). Assume that C is the number of CPUs available:

         P = ceil(D/max(floor(D/C), 1))

Simply put, the degree of parallelism for a table should generally be the number of devices on which the table is loaded, scaled down so that it isn’t too much greater than the number of CPUs. For example, with ten devices and eight CPUs, a good choice for the degree of parallelism is ten. With only four CPUs, a better choice of parallelism might be five.

However, this complex rule is not always suitable for the real world. A better rule for setting the degree of parallelism is to simply use the number of CPUs:

P=(number of CPUs)-1

As a general rule, you can set the degree of parallelism to the number of CPUs on your server, minus one. This is because one processor will be required to handle the parallel query.

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