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 







Oracle 11g R2 new parallel query management enhancements

Oracle parallel query Tips by Donald BurlesonSeptember 9,  2015

The database industry is clearly in the midst of massive server consolidation, an economic imperative whereby the old-fashioned one database/one server approach of the client-server days has been rendered obsolete. Today, single servers with 32 and 64 CPU's and hundreds of gigabytes of RAM can host dozens of large Oracle databases.

See here for a list of Oracle parallel query parameters.

While the 2nd age of mainframe computing came about to facilitate easier DBA management, there remain the impressive benefits of having a back of dozens of CPU's to perform full scans very quickly.  

When invoking Oracle parallel query, there are many perils and pitfalls:

  • Setting parallelism on at the table or system level influences the optimizer, and sometimes makes full-scan operations appear cheaper than they really are.

  • Determining the optimal degree of parallelism is tricky. The real optimal degree of parallelism depends on the physical placement of the data blocks on disk as well as the number of processors on the server (cpu_count).

To relieve these issues with parallel query, in Oracle 11g Release 2, the following new parallel query parameters are included:

  • The parallel_degree_policy parameter

  • The parallel_min_time_threshold parameter

  • The parallel_degree_limit parameter

  • The parallel_force_local parameter

Let's take a close look at these important enhancements to Oracle parallel query in 11g Release 2.

The parallel_degree_policy parameter

The parallel_degree_policy parameter is related to the amount of table data residing in the data buffer cache.  Using parallel_degree_policy allows Oracle to bypass direct path reads when Oracle determines that lots of the table data blocks already reside in the data buffer cache. 

In traditional 32-bit systems (limited by on ly a few gig of RAM for the SGA), direct path reads (which bypass the SGA were always faster than reading a large table through the data buffer.  However, with the advent of 64-bit servers with dozens of gigabytes for the db_cache_size, large tables are often be fully cached, negating the need to always perform direct path reads for parallel large-table full-table scans.

 Guy Harrison conducted some benchmark tests of parallel_degree_policy and we see details on how parallel_degree_policy evaluates the caching of large tables:

"If PARALLEL_DEGREE_POLICY is set to AUTO then Oracle might perform buffered IO instead of direct path IO. . . The documentation says that the optimizer decides whether or not to use direct path depending on the size of the table and the buffer cache and the likelihood that some data might be in memory."

The parallel_min_time_threshold parameter

The parallel_min_time_threshold parameter only allows parallel query to be invoked against large tables or indexes, those where the num_rows suggests that it will take more than nn seconds to scan the table.  The default for parallel_min_time_threshold is 30 (seconds), but you can now adjust this threshold according to your optimal definition of what constitutes a "large table".  This parameter appears to be related to the deprecated small_table_threshold parameter.

The parallel_degree_limit parameter

The parallel_degree_limit parameter seta a limit on the maximum degree of parallelism.  The default is cpu_count*2.

The parallel_force_local parameter

The parallel_force_local parameter prohibits "parallel parallelism", a case where parallel queries on a RAC node are limited only to the local instance node.

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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.