Basics: Parallelization Methods in Oracle

Hello,

When using parallel query, Oracle uses one of these methods of performing parallel queries or DML:

• Parallelize by block range
• Parallelize by partition
• Parallel processing using slave processes

go and read on

Parallelize By Block Range

When executing a parallel query that uses the parallelize by partition range method, Oracle divides the table into ranges of blocks. This range of blocks is also known as a ROWID range, and it is divided into a high and low ROWID range for the purposes of the parallel scans. Each individual parallel query process then works on processing these ranges of blocks. If a block range is being used on partitioned tables, no ROWID range can span more than one partition; however, each partition may consist of one or more range partitions within the partition boundaries. Also, a parallel query on a partitioned table might be able to take advantage of partition pruning, thus making it faster than an equivalent nonpartitioned block range query on the same object.

For a block range lookup, Oracle starts as many parallel query processes as it determines are needed, up to the maximum number configured in init.ora. The number of processes that are chosen is defined in this hierarchical order:

1. The number of processes defined in the hint associated with the SQL being executed.
2. In the absence of a hint, the default degree of parallelism established for the table.
3. In the absence of the first two, the default values as established by the Oracle server based on the number of CPUs or disks.

When Oracle uses parallelize by block range to process parallel queries on SELECT statements, the scan of one table in the query must be a full table scan. In addition, a parallel query on a SELECT statement occurs only if the table queried on has been defined with a parallel clause, or if a parallel hint is included in the SQL query.

The following operations on tables and indexes can be parallelized by using the block range method:

• Queries using table scans (including queries in DML and DDL statements)
• Partition operations: MOVE, SPLIT, and REBUILD index partitions
• CREATE INDEX on a nonpartitioned index
• CREATE TABLE...AS SELECT on nonpartitioned tables

Statements That Parallelize By Partition

When executing a query using the parallelize by partition method, Oracle requires that one object in the query be partitioned. The partition is the most granular unit that Oracle uses in a parallelized operation that involves partitioned objects. No separate parallelization operations take place within the partition itself.

When a parallelize by partition operation is executed on a partitioned object, a parallel server process is assigned to each partition of the table or index, which is needed for the query. Again,partition pruning may well reduce the numbers of partitions that need to be processed during the query, further speeding the process and reducing the number of parallel server processes needed.

The number of partitions may exceed the number of parallel server processes used in the query, so only one parallel server process accesses a single partition. However, that single server process may, in fact, process multiple partitions.

Oracle uses parallelize by partition for several operations, including:

• CREATE INDEX
• CREATE TABLE...AS SELECT
• UPDATE and DELETE operations
• INSERT...SELECT
• ALTER INDEX...REBUILD
• Queries that use range scans on a partitioned index

Parallelize By Parallel Server Processes

This option is available only for INSERT…SELECT on non partitioned tables. It allows the multiple parallel server processes to divide the work on an insert operation. The insert operation distributes part of the INSERT operation among the parallel server processes, which proceed to insert the information into the object.

-Thanks

Geek DBA