Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Oracle Parallel Query and Oracle SQL Tuning
Oracle Tips by Burleson

For Oracle SQL queries that perform large-table full-table scans, Oracle parallel query can greatly improve the access speed for data warehouse queries.

There are several init.ora parameters that are set when using Oracle parallel query. Many of these are default values and are set by Oracle when your database is created. Oracle parallel query can be turned on in several ways. You can turn it on permanently for a table, or you can isolate the parallel query to a single table.

Method 1 - Permanent Parallelism (not recommended)

Alter table customer parallel degree 35;

Method 2 - Single-Query Parallelism

select /*+ FULL(emp) PARALLEL(emp, 35) */

Note the use of the double hints in the preceding query. Most Oracle DBAs always use the FULL hint with the PARALLEL hint because they are both required to use Oracle parallel query.

WARNING: Setting a table for parallel query with the alter table xxx parallel degree nn command can be very dangerous to Oracle SQL performance. If you are using the CBO and you set table-level parallelism, the optimizer may reevaluate the execution plans for SQL statements and change many statements from index range scans to full-table scans. This can cause serious performance degradations for an entire database, and the parallel hint is a far better choice for implementing parallelism because you will never have unintended side effects.

Most Oracle DBAs identify those tables that perform full-table scans and then alter those tables to specify the degree of parallelism. This way, all full-table scans against the tables will invoke Oracle parallel query.

Parallel Query init.ora Parameters

There are several important init.ora parameters that have a direct impact on the behavior of Oracle parallel query:

  • 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 parameter, 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 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.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter