Using parallel operations enables multiple processes (and potentially processors) to work together simultaneously to resolve a single SQL statement. This feature improves data-intensive operations, is dynamic (the execution path is determined at run time), and (when wisely implemented) makes use of all of your processors and disk drives. There are some overhead costs and administrative requirements, but using parallel operations can improve the performance of many queries.
Consider a full table scan. Rather than have a single process execute the table scan, Oracle can create multiple processes to scan the table in parallel. The number of processes used to perform the scan is called the degree of parallelism (DOP). The degree can be set in a hint at the time of table creation or as a hint in the query. Figure 1 shows a full table scan of the EMP table broken into four separate parallel query server processes. (The degree of parallelism is four.) A fifth process, the query coordinator, is created to coordinate the four parallel query server processes.
Figure 1. A simple full table scan with parallel execution (disk access not shown)
TIP
Parallel processes commonly involve disk accesses. If the data is not distributed across multiple disks, using the Parallel Execution Option (PEO) may lead to an I/O bottleneck.
If the rows returned by the full table scan shown in Table 1 also need to be sorted, the resulting operation will look like Figure 2 instead. Now Oracle may use one process to coordinate the query, four processes to run the query, and four processes to sort the query. The total is now nine processes, although the degree of parallelism is still 4. If you have nine processors (CPUs), your machine can use all nine processors for the operation (depending on your system setup and other operations that are being performed at the same time). If you have fewer than nine processors available, you may encounter some CPU bottleneck issues as Oracle manages the query.
Because the query coordination parts of the operation take resources, fast-running queries are not usually enhanced (and may be degraded) with the use of parallel operations.
Figure 2. A simple full table scan requiring a sort with parallel execution (SGA not shown)
TIP
Using parallel operations on very small tables or very fast queries can also degrade performance because the query coordination may also cost performance resources. You should evaluate whether the parallel cost exceeds the nonparallelized cost.
Both queries in Table 1 and Table 2 require access to the physical disks to retrieve data, which is then brought into the SGA. Balancing data on those disks based on how the query is “broken up” makes a large I/O difference.
TIP
When the parallel degree is set to N, it is possible to use (2*N) + 1total processes for the parallel operation. Although parallel operations deal with processes and not processors, when a large number of processors are available, Oracle generally uses the additional processors to run parallel queries, often enhancing the performance of the query.
Leave a Reply