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

 
 Home
 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
 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 parallel query parameters tips

Oracle Database Tips by Donald BurlesonApril 22, 2016

Question: What are the parameter for Oracle parallel query?

Answer:  The Oracle parallel query parameters vary widely by release of Oracle.  Here is a list of the parallel query parameters in Oracle 12c;

parallel_adaptive_multi_user

true

parallel_degree_limit

cpu_count x parallel_threads_per_cpu x active_instances

parallel_degree_policy

manual

parallel_degree_level 100

parallel_execution_message_size

16 kb

parallel_force_local

false

parallel_max_servers

cpu_count x parallel_threads_per_cpu x (2 if pga_aggregate_target > 0; otherwise 1) x 5

parallel_min_servers

0

parallel_min_percent

0

parallel_min_time_threshold

10 seconds

parallel_servers_target

4 x cpu_count x parallel_threads_per_cpu x active_instance

parallel_threads_per_cpu

2

Here are details on the parallel query parameters:

The parallel_degree_level parameter: (12c and beyond)

 The parallel_degree_level is a percentage, with a default of 100 and setting it to a value of ten (10%) decreases the calculated value to 10% while increasing it to 200 (200%)  doubles the calculated degree of parallelism. The auto automatic degree of parallelism calculations have gotten progressively better over the last couple of years, but the parallel_degree_level parameter is a throttle that can be used to pre-compute the optimal parallel query degree.

The parallel_min_servers parameter

This specifies the minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.

The parallel_max_servers parameter

Parallel_max_servers specifies the maximum number of parallel query servers or parallel recovery processes for an instance. Oracle will increase the number of query servers as demand requires from the number created at instance startup up to this value. The same value should be used for all instances in a parallel server environment.

Proper setting of the PARALLEL_MAX_SERVERS parameter ensures that the number of query servers in use will not cause a memory resource shortage during periods of peak database use.

If PARALLEL_MAX_SERVERS is set too low, some queries may not have a query server available to them during query processing.

Setting PARALLEL_MAX_SERVERS too high leads to memory resource shortages during peak periods, which can degrade performance. For each instance to which you do not want to apply the parallel query option, set this initialization parameter to zero.

If you have reached the limit of PARALLEL_MAX_SERVERS on an instance and you attempt to query a GV$ view, one additional parallel server process will be spawned for this purpose. This extra process will serve any subsequent GV$ queries until expiration of the PARALLEL_SERVER_IDLE_TIME, at which point the process will terminate. The extra process is not available for any parallel operation other than GV$ queries.

Note that if PARALLEL_MAX_SERVERS is set to zero for an instance, then no additional parallel server process will be allocated to accommodate a GV$ query.

The parallel_adaptive_multi_user  parameter

TRUE or FALSE, defaults to FALSE, when set to TRUE, enables an adaptive algorithm designed to improve performance in multi-user environments that use Parallel Query(PQ). It does this by automatically reducing the requested degree of parallelism based on the current number of active PQ users on the system. The effective degree of parallelism will be based on the degree of parallelism from the table or hint divided by the total number of PQ users. The algorithm assumes that the degree of parallelism provided has been tuned for optimal performance in a single user environment.

In Oracle 11g Release 2 and beyond, 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.

 

For earlier releases, here are the parallel query parameters

optimizer_percent_parallel

Specifies the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans.

Cost-based optimization will always be used for any query that references an object with a nonzero degree of parallelism. For such queries a RULE hint or optimizer mode or goal will be ignored. Use of a FIRST_ROWS hint or optimizer mode will override a nonzero setting of OPTIMIZER_PERCENT_PARALLEL.

The parallel_min_percent parameter

This specifies the minimum percent of threads required for parallel query. Setting this parameter ensures that a parallel query will not be executed sequentially if adequate resources are not available. The default value of 0 means that this parameter is not used.

If too few query slaves are available, an error message is displayed and the query is not executed. Consider the following settings:

PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10

In a system with 20 instances up and running, the system would have a maximum of 200 query slaves available. If 190 slaves are already in use and a new user wants to run a query with 40 slaves (for example, degree 2 instances 20), an error message would be returned because 20 instances (that is, 50% of 40) are not available.

parallel_server_idle_time

Specifies the amount of idle time after which Oracle terminates a process for parallel operations (parallel query, parallel DML, or parallel DDL). This value is expressed in minutes. The parameter must be set to 1 or greater for the query processes to terminate. 0 means the processes are never terminated.

parallel_execution_message_size

Specifies the size of messages for parallel execution (Parallel Query, PDML, Parallel Recovery, replication). The default value should be adequate for most applications. Typical values are 2148 or 4096 bytes. Larger values would require a larger shared pool.

parallel_min_message_pool

The parallel_min_message_pool parameter defaults to (cpus*parallel_max_servers*1.5*(OS message buffer size) or cpus*5*1.5*(OS message size)). Specifies the minimum permanent amount of memory which will be allocated from the SHARED POOL, to be used for messages in parallel execution.

This memory is allocated at startup time if PARALLEL_MIN_SERVERS is set to a non-zero value, or when the server is first allocated. Setting this parameter is most effective when PARALLEL_MIN_SERVERS is set to a non-zero value, because the memory will be allocated in a contiguous section.

This parameter should only be set if the default formula is known to be significantly inaccurate. setting this parameter too high will lead to a shortage of memory for the shared pool; setting it too low will lead to costlier memory allocation when doing parallel execution. This parameter cannot be set to a number higher than 90% of the shared pool.

parallel_broadcast_enabled

This defaults to FALSE and allows you to improve performance in certain cases involving hash and merge joins. When set to TRUE, if you are joining a very large join result set with a very small result set (size being measured in bytes, rather than number of rows), the optimizer has the option of broadcasting the row sources of the small result set, such that a single table queue will send all of the small set's rows to each of the parallel servers which are processing the rows of the larger set. The result is enhanced performance.


   
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 consulting and training

 

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