Parallel Query
Parallel Query is the most commonly used of Oracle’s parallel execution features. It was the first parallel execution feature to be developed by Oracle and was introduced in Oracle Release 7 as the Oracle Parallel Query Option (PQO).
To parallelize a SELECT statement, the following conditions must be met:
1. At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions.
2. If the execution involves a full table scan, the statement must contain a PARALLEL hint specifying the corresponding table, or the corresponding table must have a parallel declaration in its definition.
3. If the execution involves an index range scan spanning multiple partitions, the statement must contain a PARALLEL_INDEX hint specifying the corresponding index, or the corresponding index must have a parallel declaration in its definition.
The following two sections explain how the degrees of parallelism is chosen for a SELECT statement and discuss restrictions on the use of the parallel query feature. Setting the Degree of Parallelism – once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules:
1. Oracle retrieves the DEGREE and INSTANCES specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings.
2. Oracle checks the statement fro a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step.
You can use the PARALLEL and PARALLEL_INDEX hints to specify the degree of parallelism for a SELECT statement. You can use the NOPARALLEL and NOPARALLEL_INDEX hints to ensure that parallel execution is not performed.
Examples:
alter table ps_vcr_ody_dtl parallel (degree 4);
select degree from user_tables where table_name = ‘PS_VCR_ODY_DTL’;
select count(*) from ps_vcr_ody_dtl;
alter table ps_vcr_ody_dtl noparallel;
select /*+ PARALLEL(ps_vcr_ody_dtl,4) */ count(*) from ps_vcr_ody_dtl;
Parallel DML
Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up very large DML operations and is particularly advantageous in data warehousing environments where its necessary to maintain large summary or historical tables. In OLTP systems, parallel DML sometimes can be used to improve the performance of long-running batch jobs.
Deciding to Parallelize a DML Statement
When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that
