Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include:

Parallel execution of SQL statements is not beneficial in all circumstances. To gain a benefit from parallelization, one or more of the following must apply:

Let’s look at some examples that demonstrate the effect of parallel execution on performance. You will see that while parallel execution is not always beneficial when small amounts of data are involved, it can improve performance for queries involving a large amount of data.

The following example shows the DEGREE setting for the customers table, as well as the elapsed time needed to execute a query to count the number of records in the table. The DEGREE setting determines the number of parallel slave processes to be used to execute the query in parallel. Section 3.2.2 later in this chapter discusses the DEGREE setting (and how it is set) in more detail. For example:

SQL> SELECT degree 
  2  FROM user_tables 
  3  WHERE table_name = 'CUSTOMERS';
    DEGREE
----------
         1

SQL> SELECT COUNT(*) FROM customers;
  COUNT(*)
----------
       984
Elapsed: 00:00:00.09

The customers table is small and won’t be accessed in parallel because DEGREE is set to 1. The COUNT(*) query on this table returned a result in 0.09 seconds.

Now, let’s enable parallel query for the customers table and see what difference that makes on execution time. We’ll set the table’s DEGREE parameter to 4 (via the ALTER TABLE statement), thus requesting the use of four parallel slave processes. Notice the effect on elapsed execution time:

SQL> ALTER TABLE customers PARALLEL(DEGREE 4);
Table altered.

SQL> SELECT degree FROM user_tables 
                  WHERE table_name = 'CUSTOMERS';
    DEGREE
----------
         4

SQL> SELECT COUNT(*) FROM customers;
  COUNT(*)
----------
       984
Elapsed: 00:00:00.18

Enabling parallel execution for this small table actually hurts the performance of the query in this example. Using parallel execution and a DEGREE of 4, it takes 0.18 seconds to count the records in the table. That’s twice as long as the 0.09 seconds required to count the records when parallel execution was not used. In this case, because the query is so small, the overhead of parallelism is not justified.

Next, let’s look at an example in which we query a larger table. This example shows the benefit of parallelism in larger queries. The ORDERS table currently has a DEGREE setting of 1. Note the elapsed time for the COUNT(*) query with serial execution:

SQL> SELECT degree FROM user_tables 
  2  WHERE table_name = 'ORDERS';
    DEGREE
----------
         1

SQL> SELECT COUNT(*) FROM orders;
  COUNT(*)
----------
   2465726
Elapsed: 00:00:01.32

The serial COUNT(*) query takes 1.32 seconds to complete. Now, let’s alter the table definition to set DEGREE to 4, thereby requesting that Oracle use four parallel slave processes to execute a query on the table. Note the elapsed time for the parallel COUNT(*) query with DEGREE 4:

SQL> ALTER TABLE orders PARALLEL(DEGREE 4);
Table altered.

SQL> SELECT degree FROM user_tables 
  2  WHERE table_name = 'ORDERS';
    DEGREE
----------
         4

SQL> SELECT COUNT(*) FROM orders;
  COUNT(*)
----------
   2465726
Elapsed: 00:00:00.74

With serial execution, a COUNT(*) query on the orders table returns a result in 1.32 seconds. With parallel execution and with DEGREE set to 4, the same query takes 0.74 seconds. With DEGREE set to 4, 4 parallel processes are used by this query to produce the result. However, it’s interesting to notice that the improvement in performance (from 1.32 seconds to 0.74 seconds) is not fourfold. This is because time is spent in splitting the job into four smaller units, assigning each unit of work to a separate process and combining the results. This simple example illustrates the impact of parallelism on performance. However, queries taking only a few seconds to execute may not be ideal candidates for parallel execution in all situations. As discussed in the previous section, long-running queries are the ones that gain maximum advantage from parallel execution.