DBA Tips Archive for Oracle


Using Parallel Query and Other Parallel Features

by Jeff Hunter, Sr. Database Administrator


  1. Overview
  2. Introduction to Parallel Execution


Oracle introduced the Parallel Query Option in release 7.1 to enable DBAs to parallelize full table scan operations. This feature would split a single (serial) query into multiple processes, each with distinct result sets. When the distinct result sets are completed, they are then merged and returned to the end user. With the introduction of Oracle8, Oracle now allows the DBA to parallelize DML statements and perform parallelized operations on table and index partitions. The ability to perform parallel operations can prove significant performance enhancements and should also be explored by the DBA.

Parallel execution is useful for many types of operations like:

This article provides a brief introduction into Parallel Query and several other Parallel Features offered in Oracle.

Introduction to Parallel Execution
Let's first talk about parallel operations. Parallel operations allow you to resolve a single SQL statement into multiple processes, and potentially multiple processors. The benefits are improved data-intensive operations, is dynamic (where the execution path is determined at run time), and can easily make use of all of your processors and disk drives. Keep in mind that there is a small price to pay for this feature (the extra resources), but for those long running queries, driven by SQL statements that can table blocks or data/index partitions, parallel operations will most certainly provide a solution to some of your most data-intensive reports.

Instead of using a single process for one statement, the work can be spread to multiple processes. This is called parallel execution or parallel processing. Parallel execution normally reduces response time when you are accessing large amount of data, but it is not always faster than a serial operation.

Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Tuesday, 14-Jan-2003 00:00:00 EST
Page Count: 9391