Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Parallel_max_servers tips

Oracle Tips by Burleson Consulting

January 25, 2013

Question I am tuning my database and I want to optimize parallel_max_servers to accommodate my SQL workload.  How do you arrive at an optimal setting for parallel_max_servers?

Answer:  Unless your database is a Decision Support System (DSS) or a Data Warehouse application, I always turn-on parallelism only at the SQL statement level, using a parallel hint.  Remember, parallelism ONLY works for large-table-full-table scans and OLTP databases like to use indexes instead of parallel query. 

There are many pitfalls with "automatic" parallelism, most notably, it influences the cost-based SQL optimizer into thinking that full-scans are "cheaper" than index scans.

The pre 11g default value for parallel_max_servers is the number of CPUs on your server.   The value for the parallel_max_servers parameter is derived from the values of cpu_count, pga_aggregate_target (sga_target, memory target), and parallel_threads_per_cpu.  

Starting in Oracle 11.2 and beyond there is a new method to compute the default value for parallel_max_servers .

parallel_max_servers =
   parallel_threads_per_cpu * cpu_count * concurrent_parallel_users * 5

You can change parallel_max_servers at the system level but you cannot change parallel_max_servers for an individual session.

SQL> alter system set parallel_max_servers = 36 scope=both;

System altered.

SQL> alter session set parallel_max_servers = 32;
alter session set parallel_max_servers = 32
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

parallel_max_servers and performance

This parameter controls the maximum number of OPQ factotum processes (p000, p001) that are spawned during a parallel query when parallel_automatic_tuning is enabled. As we know,

Oracle parallel query is most commonly used in data warehouse applications where there are lot’s of legitimate large-table full-table scans, and the DBA has set parallel_automatic_tuning to allow Oracle to determine the best degree of parallelism for a query.

If you are running multiple instances on the same server, this default may be too high, in which case you will see excessive server paging and high CPU utilization. The degree of parallelism is also dependent upon the number of partitions in the target table, so parallel_max_servers should be set high enough to allow Oracle to choose the best number of parallel query slaves for each query.

Again, it is imperative to remember that parallelism (including parallel_max_servers) only matters for operations that invoke large-table full-table scans, and well-indexed OLTP databases do not need to be concerned with parallel query except for specific SQL statements.

Note that the Servers Max statistic matches the parallal_max_servers initialization parameter. The Servers Highwater statistic shows the highest number of concurrent slave processes in use at any one time.
If the Servers Highwater value is near or equal to the Servers Max value, consider increasing the parallal_max_servers initialization parameter.
The Buffers Current Total value shows the maximum number of buffers in the PX message pool. The total number of buffers in use is Buffers Current Total – Buffers Current Free.
The maximum number of concurrent buffers used in the life of the instance is shown in the Buffers HWM value. If the Buffers HWM value is close to the Buffers Current Total value, this is another indicator that the appropriate SGA components, Shared Pool or Large Pool, need to be increased.
Many times when tuning applications, it is often beneficial to identify session details. Since this chapter is devoted to parallel operations, it is useful to determine the Query Coordinator process and its parallel slaves.


Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster