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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle 11g R2 new parallel query management enhancements

IT Tips by Burleson Consulting
September 9,  2009

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.

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.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



 

 

??
 
  
 
 
 

 
 
 

Oracle training Excel
 
Oracle performance tuning software 
 

 

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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.