BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter