Author: Don Burleson

Oracle provides a wealth of distributed parallel query options. Each of these has its own unique characteristics. In this article I will explores the secrets of Oracle Parallel Query.

In Oracle we find several types of parallel queries. These include

Rollup and Aggregation processes:

A common requirement of Oracle data warehouses are rollup and aggregation processes whereby super-large tables are read, end-to-end, computing summary and average values.  Some data warehouse queries also have ORDER BY or GROUP BY clauses, requiring Oracle to retrieve and sort a very-large result set. 

Single-instance Oracle Parallel Query:

In a single-instance environment we are able to dedicate as many resources as we desire.  In the example below we use 15 PQ slave (factotum) processes and allocate 16 gigabytes to sort the result set in RAM. As each factotum process ends, they pass the result set to the PQ coordinator and the sort is performed within the 16 gig RAM sorting region.

Oracle parallel query for RAC:

The foundation of Oracle Real Application Clusters revolves around parallelism. The original name for RAC was OPS, for Oracle Parallel Server. 

With RAC, it is possible for an intra-parallel operation to utilize the processors across the nodes, using the second argument in the Oracle PARALLEL hint.  This gives an additional degree of parallelism while executing in parallel.

Because each node requires a parallel query coordinator, many shops use n-1 parallelism, to reserve a CPU for the query coordinator.  If we have four instances, each with 4 CPU's, our query might use a degree of parallelism (DOP) of 3 (n-1, to reserve a CPU for the query coordinator process).  The query with a DOP of three look like this

RAC implementation of the query might run slower than an equivalent query on a monolithic server with the same hardware.  The differences between RAC query and the vanilla Oracle parallel query on the monolithic server are

• One-fourth fewer processes reading the table rows
• Overhead on the cache fusion layer
• Slower sorting

Oracle Parallel Query in a distributed environment:

In a distributed environment, pieces of a table may reside on many remote servers. You can access all of the remote rows in a single query, using inter-instance parallel execution in a distributed architecture where local customer tables are kept on each instance. Below example executes query from the north_carolina instance, accessing two remote instances in-parallel. The north_carolina instance drives the distributed parallel query and it is the north_carolina instance that must gather and sort the result set.

select customer_name, sum(purchase_amount) from sales
union
select customer_name, sum(purchase_amount) from sales@san_francisco
union
select customer_name, sum(purchase_amount) from sales@new_york
group by customer_name;

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle: Achieving performance goal - Part I
   Oracle Tips for Monitoring Oracle Import Speed!!
   Oracle Management Goals: Scalable Oracle Web Systems
   Expert Tips on Drop Temporary Tablespace Hangs!!