Parallel Execution of SQL Statements

最新推荐文章于 2024-09-12 23:51:52 发布

cracknight1982

最新推荐文章于 2024-09-12 23:51:52 发布

阅读量62

点赞数

文章标签：开发工具

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

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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9907339/viewspace-1047694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9907339/viewspace-1047694/