Allowing Oracle to set the optimal degree of parallelism in a query

Oracle parallel query allows you to control the number of parallel query slave processes that service a table with the DEGREE clause.



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 server with lots of CPUs.



We can also let Oracle compute the optimal degree of parallelism. For example, we can create a script to gather the optimal degree of parallelism and pass this argument to the SQL.



The following script uses the IBM-AIX lsdev command to compute the number of CPUs on the server, and pass this value to the Oracle parallel query:


parallel_query.ksh


#!/bin/ksh
# Get the number of CPUs
num_cpu=`lsdev –C|grep mem|wc –l`
optimal_parallelism=`expr $num_cpu`-1

sqlplus system/manager<select /*+ FULL(employee_table)
PARALLEL(employee_table, $optimal_parallelism)*/
employee_name
from
employee_table;
exit
!



Here is how to get the number of CPUs on your Oracle server:





Windows NT - If you are using 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 this example, 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 ioscan command to find the number of CPUs on your server.



>ioscan -C processor | grep processor | wc -l
6



Oracle parallel query processes can be seen on the server because background processes will start when the query is serviced. These factotum processes are generally numbered from P000 through Pnnn and you can issue a UNIX command to watch them get created and destroyed:



ps -ef|grep ora|grep -i p0