Oracle Parallel Processing

Chapter 4. Using Parallel Execution

In Chapter 3, we introduced Oracle’s parallel execution features and talked about the concepts behind how they work. These features include:

• Parallel query

• Parallel DML

• Parallel DDL

• Parallel data loading

• Parallel recovery

• Parallel replication propagation

In this chapter, we’ll describe how to use each of these features and show you how to take advantage of them. Most of these features come with a list of restrictions and caveats that may affect your ability to use them in a given situation. We’ll describe those as well.

Throughout this chapter, we assume that you’re familiar with the basic Oracle features being discussed. This isn’t, for example, a tutorial on recovery or replication. The focus is always on the parallel features that can be applied to the task at hand. If you’re familiar with DML and DDL, for example, you won’t have any problem following the discussion of how to parallelize those types of statements. The same is true for the other types of operations covered in this chapter.

Parallel Query

Parallel query is the most commonly used of Oracle’s parallel execution features. It was the first parallel execution feature to be developed by Oracle and was introduced in Oracle7 (release 7.1) as the Oracle Parallel Query Option (PQO). Parallel execution can significantly reduce the elapsed time for large queries, but it doesn’t apply to every query. To parallelize a SELECT statement, the following conditions must be met:

• At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions.

• If the execution involves a full table scan, the statement must contain a PARALLEL hint specifying the corresponding table, or the corresponding table must have a parallel declaration in its definition.

• If the execution involves an index range scan spanning multiple partitions, the statement must contain a PARALLEL_INDEX hint specifying the corresponding index, or the corresponding index must have a parallel declaration in its definition.

The following two sections explain how the degree of parallelism is chosen for a SELECT statement and discuss restrictions on the use of the parallel query feature.

Setting the Degree of Parallelism

Once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules:

1. Oracle retrieves the DEGREE and INSTANCES specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings.

2. Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step.

You can use the PARALLEL and PARALLEL_INDEX hints to specify the degree of parallelism for a SELECT statement. You can use the NOPARALLEL and NOPARALLEL_INDEX hints to ensure that parallel execution is not performed for the SELECT.

The PARALLEL hint controls the degree of parallelism applied to tables accessed by a query and takes this form:

/*+ PARALLEL (table_name[, degree[, instances]]) */

where:

table_name

Is the name of the table on which to parallelize operations

degree

Specifies the number of parallel slave processes that each instance should use when performing operations on this table

instances

Is the number of instances to be used for parallel operations on the table

The scope of the PARALLEL hint is the SELECT statement in which it appears. Subsequent SQL statements are not affected.

The PARALLEL_INDEX hint does for indexes what the PARALLEL hint does for tables. It can be used with SELECT statements to enable parallelized index range scans for partitioned indexes. The hint takes this form:

/*+ PARALLEL_INDEX (table_name[, index_name
                   [, degree[, instances]]]) */

where:

table_name

Is the name of the table on which to parallelize operations

index_name

Is the name of the partitioned index on which to parallelize an index range scan

degree

Specifies the number of parallel slave processes that each instance should use when performing operations on this index

instances

Is the number of instances to be used for parallel operations on the index

The scope of the PARALLEL_INDEX hint is the SQL statement in which it appears. It also applies only to partitioned indexes and only when range scans are being performed on those indexes.

The values for degree and instances can be either integers or the keyword DEFAULT. DEFAULT tells Oracle to use the default value for the instance.

Let’s look at some examples that demonstrate how hints are used to specify the degree of parallelism. The following example shows a hint that tells Oracle to scan the orders table in parallel using four parallel slave processes on one instance only. The hint will override any DEGREE and INSTANCES specifications in the table definition and also will override the default degree of parallelism for the instance:

SELECT /*+ PARALLEL(orders,4,1) */
       COUNT(*)
FROM orders;

The next example uses a PARALLEL_INDEX hint that calls for a parallel index range scan of the partitioned index named orders_uk. This parallel range scan will be performed with four parallel slave processes, with two instances each running two parallel slave processes:

SELECT /*+ PARALLEL_INDEX(orders,orders_uk,2,2) */
       COUNT(*)
FROM orders;

Here’s an example that specifies only a degree value in the hint. Since no value is specified for instances, the default value from the table definition will be used. If the table definition does not have an INSTANCES specification or if the value is DEFAULT, then instances will pick up the instance-level default value. (See Section 3.2.2 in Chapter 3 for a discussion of statement-level, table-level, and instance-level default values.) For example:

SELECT /*+ PARALLEL(orders,4) */
       COUNT(*)
FROM orders;

In the following example, the PARALLEL hint tells Oracle to scan the orders table in parallel. Since no degree and instances values are specified, Oracle will pick up those values from the table’s definition. If they weren’t defined at the table level, Oracle will use the instance-level default values:

SELECT /*+ PARALLEL(orders) */
       COUNT(*)
FROM orders;

In the following example, the hint tells Oracle to scan the orders table in parallel using the default number of instances and using six slave processes on each instance. When you specify DEFAULT for either degree or instances in a hint, Oracle ignores the values in the table definition and uses the instance-level values. Since DEFAULT is specified in the example for instances, Oracle will use the instance-level value here:

SELECT /*+ PARALLEL(orders, 6, DEFAULT) */ COUNT(*)
FROM orders;

You can turn off parallel operations for a statement using the NOPARALLEL hint. The NOPARALLEL hint takes this form:

/*+ NOPARALLEL (table_name) */

where table_name is the name of the table.

The following example includes the NOPARALLEL hint, ensuring that the customers table will not be accessed in parallel:

SELECT /*+ NOPARALLEL(customers) */ customer_id, location, name
FROM customers;

If an index has a parallel specification in its definition, and you want to avoid a parallel index scan operation, you can use a NOPARALLEL_INDEX hint to achieve this goal. The NOPARALLEL_INDEX hint takes this form:

/*+ NOPARALLEL_INDEX (table_name[,index_name]) */

where:

table_name

Is the name of the table

index_name

Is the name of the index

Restrictions on Parallel Query

Oracle can parallelize individual SELECT statements, and it can parallelize subqueries that appear in SELECT statements, other DML statements, or DDL statements. Parallelizing a SELECT statement has the following restrictions:

• If the DDL or DML statement references a remote object, the query part can’t be parallelized.

• If a query involves joins and sorts on tables having object types, the query will not be parallelized if the object definitions do not have a MAP function.

• Queries on nested tables can’t be parallelized.

If you ask Oracle to execute a query in parallel, and it can’t run in parallel because of these restrictions, Oracle will run the query serially without raising any error or warning.