How to enable Parallelism for a Specific SQL Query

Problem

You have a slow-running query accessing data from a large table. You want to see if you can speed up the query by instructing Oracle to use multiple processes to retrieve the data.

Solution

There are two distinct types of hints to place in your SQL to try to speed up your query by using multiple processes, or parallelism. One type of hint is for data retrieval itself, and the other is to help speed the process of reading the indexes on a table.

Parallel Hints for Tables

First, you need to determine the degree of parallelism (DOP) desired for the query. This instructs Oracle how many processes it will use to retrieve the data. Second, place a parallel hint inside of the query specifying the table(s) on which to execute parallel SQL, as well the degree of parallelism to use for the query—for example:

SELECT /*+ parallel(emp,4) */ empno, ename
FROM emp;

If you use a table alias in your query, you must use it in your hint, else the Oracle optimizer will ignore the hint.

SELECT/*+ parallel(e,4) */ empno, ename
FROM emp e;

The hints in the preceding two queries result in four processes dividing the work of reading rows from the EMP table. Four processes working together will get the job done faster in terms of wall-clock time than one process doing all the work by itself.

Optionally, you can omit specifying a degree of parallelism within the hint. If you specify only the table name or alias in the hint, Oracle will derive the degree of parallelism based on the database initialization parameters, which may or may not give you the desired degree of parallelism:

SELECT/*+ parallel(e) */ empno, ename
FROM emp e;

Execution plan without use of parallel hint:

How It Works

In order to effectively use parallel hints, you need to take the following items into consideration:

• The number of tables in your query
• The size of table(s) in your query
• The number of CPUs on your system
• The filtering columns in your WHERE clause
• What columns are indexed, if any

You also must analyze and understand three key components of your system prior to using parallel hints in queries:

•  System configuration, such as amount of memory and CPUs, and even disk configuration

•  Database configuration parameters related to parallelism

•  The DOP specified on the objects themselves (tables and indexes)

Parallel SQL must be used with caution, as it is common to overuse, and can cause an over utilization of system resources, which ultimately results in slower rather than faster performance.

Overuse is a very common mistake in the use of parallelism. Depending on the number of tables in your query, you may want to place parallelism on one or more of the tables—depending on their size. A general rule of thumb is that if a table contains more than 10 million rows, or is at least 2 gigabytes in size, it may be a viable candidate for using parallelism. The degree of parallelism (DOP) should be directly related to the number of CPUs on your system. If you have a single-CPU system, there is little, if any, benefit of using parallel SQL, and the result could very well be returned slower than if no parallelism was used at all.