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

 

 


 

 

 

 

 

 

 

 Oracle Parallel Query Initialization Parameters

Oracle Tips by Burleson Consulting

There are several important init.ora parameters that have a direct impact on the behavior of Oracle parallel query. The values for these parameters are heavily dependent on the number of CPUs and the amount of RAM on your database server.

  • sort_area_sizeThe higher the value, the more memory is available for individual sorts on each parallel process. Note that the sort_area_size parameter allocates memory for every query on the system that invokes a sort. For example, if a single query needs more memory, and you increase the sort_area_size, all Oracle tasks will allocate the new amount of sort area, regardless of whether they will use all of the space. It is also possible to dynamically change the sort_area_size for a specific session with the alter session command. This technique can be used when a specific transaction requires a larger sort area than the default for the database.

  • parallel_min_serversThis value specifies the minimum number of query servers that will be active on the instance. There are system resources involved in starting a query server, and having the query server started and waiting for requests will accelerate processing. Note that if the actual number of required servers is less than the values of parallel_min_servers, the idle query servers will be consuming unnecessary overhead, and the value should be decreased.

  • parallel_max_serversThis value specifies the maximum number of query servers allowed on the instance. This parameter will prevent Oracle from starting so many query servers that the instance cannot service all of them properly.

  • optimizer_percent_parallelThis parameter defines 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.

Note: Cost-based optimization will always be used for any query that references an object with a nonzero degree of parallelism. Hence, you should be careful when setting parallelism if your default is optimizer_mode=RULE.

Setting the Optimal Degree of Parallelism

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 their order of importance:

  • The number of CPUs on the server

  • The number of physical disks that the table resides upon

  • 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. Remember, the proper degree of parallelism will always result in faster execution, provided you have a massively parallel server (Figure 10-2) with lots of CPUs.

Figure 2: Execution time as a function of parallel degree

In practice, the best method is a trial-and-error approach that is always verified by timing the query. 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.


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