Parallel Execution of SQL Statements

Introduction to Parallel Execution

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 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,

Operations That Can Be Parallelized

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

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

[@more@]