Page 653
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.
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.
Degree of parallelism is decided from the following, in the given order:
select /*+ PARALLEL (sales_97, 3) */
cust_id, sales_amt from sales_97 order by sales_amt;
Page 655
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 ;
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.