Parallelismis the idea of breaking down a task so that, instead of one process doing all of the work in a query, many processes do part of the work at the same time. An example of this is when 12 processes handle 12 different months in a year instead of one process handling all 12 months by itself. The improvement in performance can be quite high.

Parallel execution is useful for many types of operations that access significant amounts of data. Parallel execution improves performance for:

• Queries
• Creation of large indexes
• Bulk inserts, updates, and deletes
• Aggregations and copying

How Parallel Execution Works

Parallel execution performs these operations in parallel using multipleparallelprocesses. One process, known as theparallelexecutioncoordinator, dispatches the execution of a statement to severalparallelexecutionserversand coordinates the results from all of the server processes to send the results back to the user

Parallel Query Intra- and Inter-Operation Example

SQL> l
1 select /*+ parallel(emp 4) parallel(dept 4) use_hash(emp,dept) ordered */
2 max(sal),avg(sal)
3 from emp,dept
4 where emp.deptno=dept.deptno
5* group by emp.deptno
SQL> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=6 Card=82 Bytes=3198
)

1 0 SORT* (GROUP BY) (Cost=6 Card=82 Bytes=3198) :Q21002
2 1 SORT* (GROUP BY) (Cost=6 Card=82 Bytes=3198) :Q21001
3 2 HASH JOIN* (Cost=2 Card=82 Bytes=3198) :Q21001
4 3 TABLE ACCESS* (FULL) OF 'EMP' (Cost=1 Card=82 Bytes= :Q21001
2132)

5 3 INDEX* (FAST FULL SCAN) OF 'PK_DEPT' (UNIQUE) (Cost= :Q21000
1 Card=82 Bytes=1066)



1 PARALLEL_TO_SERIAL SELECT /*+ CIV_GB */ A1.C0,AVG(SYS_OP_CSR(A1
.C1,0)),MAX(SYS_OP_CSR(A1.C1,1)) FRO

2 PARALLEL_TO_PARALLEL SELECT /*+ PIV_GB */ A1.C0 C0,SYS_OP_MSR(AVG
(A1.C1),MAX(A1.C1)) C1 FROM (SELECT

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_TO_PARALLEL SELECT /*+ INDEX_RRS(A1 "PK_DEPT") */ A1."DE
PTNO" C0 FROM "DEPT" PX_GRANULE(0,

SQL Operations That Can Be Parallelized

Most operations can be parallelized. The following are commonly parallelized to improve performance:

• Parallel Query
• Parallel DDL
• Parallel DML
• SQL*Loader

Parallel Query

To achieve parallelism for SQL query statements, one or more of the tables being scanned should have a parallel attribute.

Parallel DDL

To achieve parallelism for SQL DDL statements, the parallel clause should be specified.

SQL> create table test parallel 5 as select * from emp;

Table created.

Parallel DML

Due to the differences in locking between serial and parallel DML, you must explicitly enable parallel DML before you can use it. To achieve parallelism for SQL DML statements, you must first enable parallel DML in your session:

ALTER SESSION ENABLE PARALLEL DML;

Then any DML issued against a table with a parallel attribute will occur in parallel, if no PDML restrictions are violated. For example:

INSERT INTO mytable SELECT * FROM origtable;

可见，只有对DML操作进行并行执行时，才需要修改配置，执行

ALTER SESSION ENABLE PARALLEL DML;

测试如下：

SQL> create table test parallel 5 as select * from emp;
 
Table created.
 
SQL> insert into test select * from emp;
 
16 rows created.

此时，查看进程，没有发生并行：

[oracle@REDHATORA1 oracle]$ ps -ef|grep ora_p
oracle    1585     1  0 13:45 ?        00:00:00 ora_pmon_ms1
oracle    1644  1539  0 13:51 pts/4    00:00:00 grep ora_p

设置session的并行配置后，再进行insert

SQL> alter session enable parallel dml;
 
Session altered.
 
SQL> insert into test select * from emp;
 
16 rows created.

查看进程，出现了并行进程：

[oracle@REDHATORA1 oracle]$ ps -ef|grep ora_p
oracle    1585     1  0 13:45 ?        00:00:00 ora_pmon_ms1
oracle    1650     1  0 13:52 ?        00:00:00 ora_p000_ms1
oracle    1652     1  1 13:52 ?        00:00:00 ora_p001_ms1
oracle    1654     1  1 13:52 ?        00:00:00 ora_p002_ms1
oracle    1656     1  1 13:52 ?        00:00:00 ora_p003_ms1
oracle    1658     1  1 13:52 ?        00:00:00 ora_p004_ms1
oracle    1660  1539  0 13:52 pts/4    00:00:00 grep ora_p