Finding the Number of CPUs on Your Database Server Oracle Tips by Burleson Consulting

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. If your server supports them, you can also use the top or glance utilities to see the number of CPUs on the 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

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 P 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 the table 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 coordinator.

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