Previous | Table of Contents | Next

Page 653

SQL Operations That Can Be Parallelized

When an SQL statement is submitted, it is first parsed and then executed. After parsing and before execution, the optimizer builds the execution plan. The query coordinator process examines the operations in the execution plan to determine whether the individual operations can be parallelized.

Prior to version 8, Oracle could parallelize only SQL statements having queries or DDL operations like create index, create table, as select, and so on, whose execution plan involved a full scan of an existing table. In version 8, Oracle introduced parallel execution for insert, update, and delete operations.

Oracle8 Server Concepts Manual mentions that Oracle8 allows the following operations to be parallelized:

As a rule of thumb, one can say that Oracle can parallelize any operation that involves a full table (partition) scan. Consider the following SQL statement:

select cust_id, sales_amt from sales_97 order by sales_amt;

Page 654

The execution plan consists of a full-table scan of the sales_97 table, assuming there is no index on these columns, followed by a sort on the sales_amt column. As shown in Figure 26.1, the scan and sort operations are divided and given to multiple processes. Both scan and sort are done simultaneously by different processes. Thus, PQO not only allows an operation to be done by multiple processes, but also enables multiple operations in the same SQL statement to be executed simultaneously rather than one after another. It is possible because of the creator and consumer nature of these operations. In the example, the rows fetched by the table scan operation are given to a sort operation without waiting for the scan to finish.

FIG. 26.1
Parallel execution involving a full-table scan followed by a sort.


Dividing an SQL operation among multiple server processes is generally referred to as intra-operation parallelism, whereas executing multiple operations simultaneously is known as inter-operation parallelism. In the previous example, scanning and sorting operations have their own server processes. Thus, inter-operation parallelism assigns different sets of servers to each operation. Oracle can execute two operations simultaneously, in parallel, for an SQL statement. If the SQL statement has three or more operations that can be parallelized, the third operation waits until the first one finishes. The query slaves used by the first operation are then recycled and used by the third operation.

Understanding the Degree of Parallelism

The number of parallel query slaves an SQL operation can use is called the degree of parallelism. Oracle determines the degree of parallelism by considering various user inputs, instance initialization parameters, number of files, number of CPU in the system, and so on.

Determining the Degree of Parallelism

Degree of parallelism is decided from the following, in the given order:

  1. 1. At SQL statement level: An SQL statement can specify the degree of parallelism through the use of parallel hint, as shown below :

select /*+ PARALLEL (sales_97, 3) */

cust_id, sales_amt from sales_97 order by sales_amt;

  1. In this example, Oracle uses three query slaves to perform the full table scan operation. The sort operation requires another three slaves. Thus the SQL statement uses seven processes—six parallel query slaves and one shadow process—in all.

Page 655

  1. At object level: You set the degree of parallelism associated with a table, cluster, partition, and so on through the corresponding create and alter commands. You can query the degree of parallelism of an existing object from the data dictionary from the USER_TABLES view's degree column.
    Alter table sales_97 parallel(degree,3);
    
    select degree from user_tables where table_name='SALES_97';
    

    The first statement will set the degree of parallelism for the table sales_97 to three. Second SQL statement queries the degree of parallelism for the sales_97 table. If multiple tables are involved in an SQL operation and each of them has a different degree of parallelism, the highest degree of parallelism becomes the degree of parallelism for that SQL operation. In the following example, the SQL operation will be performed with a parallel degree of 5:

    alter table sales_97 parallel (degree 3);
    alter table warehouse parallel (degree 5);
    select /*+ parallel(s) parallel(w) */
    w.location, s.sales_date, s.sales_value
    from warehouse w, sales_97 s
    where c.w_id = s.w_id
    order by s.sales_value, w.location ;
    
  2. Instance level: If Oracle fails to get the degree of parallelism in the previous two steps (that is, if the degree of parallelism is not specified by the user), it uses the default degree of parallelism. The process of determining the default degree of parallelism has changed with version 7.3.

In version 7.3 and later, Oracle uses the number of available CPUs and the number of disks (files, if it cannot determine the disk affinity) to determine the default degree of parallelism as follows:

Default degree of parallelism = Min (#CPU in the system, #disk drives the tables is spread on)

If you are using a RAID disk and OS striping so that a datafile is striped across multiple disks, Oracle does not know the underlying disk striping and assumes the file to be on one disk.

In 7.1 and 7.2, Oracle uses the default degree of parallelism as the minimum of the following:

Table size in number of blocks / PARALLEL_DEFAULT_SCANSIZE

PARALLEL_DEFAULT_MAX_SCANS (init.ora parameter)

The degree of parallelism you should use is dependent on your machine resources (which include CPU, memory, and the I/O bandwidth of your system), how the data is spread, the number of SQL statements executed concurrently, and other loads on your system. You need to give considerable thought when deciding the degree of parallelism. It is important that you have enough system resources for the given degree of parallelism; otherwise you might introduce problems such as excessive paging, I/O bottlenecks, and so on that might be counter-productive.

Previous | Table of Contents | Next