Databases Reference
In-Depth Information
Oracle can also provide the benefits of parallelism to individual processing steps for
queries. Some of the specific features of query processing that may be parallelized
include:
• Table scans
• Nested loops
• Sort merge joins
• Hash joins
• Bitmap star joins
• Index scans
• Partition-wise joins
• Anti-joins (NOT IN)
• SELECT DISTINCT
• UNION and UNION ALL
• ORDER BY
• GROUP BY
• Aggregations
• Import
• User-defined functions
Degree of parallelism
An Oracle instance has a pool of parallel execution (PE) processes that are available to
the database applications. Controlling the number of active PE processes was an im‐
portant task in older Oracle Database releases; too many PE processes would overload
the machine, leading to resource bottlenecks and performance degradation. A high
degree of parallelism would also force full table scans, and this may or may not be
appropriate.
Figure 7-4
illustrates transparent parallelism within and between sets of
PE processes.
Determining the optimal degree of parallelism in the presence of multiple users and
varying workloads can be challenging. For example, a degree of 8 for a query would
provide excellent performance for 1 or 2 users, but what if 20 users queried the same
table? This scenario called for 160 PE processes (8 PEs for each of the 20 users), which
could overload the machine.
Setting the degree to a lowest common denominator value (for example, 2) provided
effective parallelism for higher user counts, but did not leverage resources fully when
fewer users were active.
