并行执行是同时开启多个进程/线程来完成同一个任务，并行执行的每一个进程/线程都会消耗额外的硬件资源，所以并行执行的本质就是以额外的硬件资源消耗来换取执行时间的缩短。这里的额外硬件资源消耗是指对数据库服务器上多个CPU、内存、从个I/O通道，甚至是RAC环境下多个数据库节点的额外利用。
下面总结一下Oracle里开启并行的几种方法
1、更改目标表的并行度
有两种方法修改目标表的并行度
-
alter table table_name parallel;
-
alter table table_name parallel n;
其中方法1 是把指定表的并行度修改为默认值，方法2是把指定表的并行度修改为n；
查看表EMP当前的并行度为1
|
1
2
3
4
5
|
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name=
'EMP'
;
TABLE_NAME DEGREE
------------------------------ ----------
EMP 1
|
想用默认的并行度去访问表EMP
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
scott@TEST>
alter
table
emp parallel;
Table
altered.
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name=
'EMP'
;
TABLE_NAME DEGREE
------------------------------ ----------
EMP
DEFAULT
scott@TEST>
set
autotrace traceonly
scott@TEST>
select
*
from
emp;
14
rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
| TQ |
IN
-
OUT
| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 |
TABLE
ACCESS
FULL
| EMP | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
.....
|
从上面的执行计划中可以看出，走的是对表EMP的全表扫描，PX...表示的就是走的并行
默认并行度的算法如下：
默认并行度=parallel_threads_per_cpu*cpu_count
如果想对表开启8个并行度则执行:alter table emp parallel 8;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name=
'EMP'
;
TABLE_NAME DEGREE
------------------------------ ----------
EMP
DEFAULT
scott@TEST>
alter
table
emp parallel 8;
Table
altered.
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name=
'EMP'
;
TABLE_NAME DEGREE
------------------------------ ----------
EMP 8
|
2、使用并行Hint
有如下一些并行Hint可以用来控制是否启用并行及指定并行度
1) /*+ parallel(table[,degree]) */ #用于指定并行度去访问指定表，如果没有指定并行度degree，则使用Oracle默认并行度
2) /*+ noparallel(table) */ #对指定表不使用并行访问
3) /*+ parallel_index(table[,index[,degree]]) */ #对指定的分区索引以指定的并行度去做并行范围扫描
4) /*+ no_parallel_index(table[,index]) */ #对指定的分区索不使用并行访问
5) /*+ pq_distribute(table,out,in) */ #对指定表以out/in所指定的方式来传递数据，这里out/in的值可以是HASH/NONE/BROADCAST/PARTITION中的任意一种如/*+ pq_distribute(table,none,partition) */
把表EMP修改回并行度为1
|
1
2
3
4
5
6
7
8
9
|
scott@TEST>
alter
table
emp noparallel;
Table
altered.
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name=
'EMP'
;
TABLE_NAME DEGREE
------------------------------ ----------
EMP 1
|
使用并行Hint执行上之前的SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
scott@TEST>
select
/*+ parallel(emp) */*
from
emp;
14
rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
| TQ |
IN
-
OUT
| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 |
TABLE
ACCESS
FULL
| EMP | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
|
从上面的执行计划中可以看出，走的是并行
3、使用alter session命令
使用alter session命令，可以在当前session中强制启用并行查询或并行DML。如果强制启用了并行查询或者并行DML，那就意味着从执行alter session命令强制开启并行的那个时间点开始，在这个session中随后执行的所有SQL都将以并行的方式执行，有如下四种方法在当前session中强制开启并行
1) alter session parallel query
在当前session中强制开启并行查询，没有指定并行度，Oracle使用默认并行度
2) alter session parallel query parallel n
在当前session中强制开启并行查询，并且指定并行度为n
3) alter session parallel dml
在当前session中强制开启并行DML,没有指定并行度，Oracle使用默认并行度
4) alter session parallel dml parallel n
在当前session中强制开启并行DML，并且指定并行度为n
表EMP并行度仍为1，在session中强制开启并行:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name=
'EMP'
;
TABLE_NAME DEGREE
------------------------------ ----------
EMP 1
scott@TEST>
set
autotrace traceonly
scott@TEST>
alter
session
force
parallel query;
Session altered.
scott@TEST>
select
*
from
emp;
14
rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275
--------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
| TQ |
IN
-
OUT
| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 |
TABLE
ACCESS
FULL
| EMP | 14 | 1218 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
......
|
从执行计划中可以看出走的是并行。
取消当前session并行使用如下语句alter session disable parallel query;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
scott@TEST>
alter
session disable parallel query;
Session altered.
scott@TEST>
select
*
from
emp;
14
rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 |
TABLE
ACCESS
FULL
| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
......
|
4、11gR2的自动并行
Oracle在11gR2中引入了自动并行(Auto DOP)，自动并行的开启受参数parallel_degree_policy的控制，其默认值为MANUAL，即自动并行在默认情况下并没有开启。如果通过更改PARALLEL_DEGREE_POLICY的值而开启了自动并行，那么后面执行的SQL的执行方式是串行还是并行，以及并行执行的并行度是多少等，就都是由Oracle自动来决定了。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
scott@TEST>
select
table_name,degree
from
user_tables
where
table_name
in
(
'EMP'
,
'EMP_TEMP'
);
TABLE_NAME DEGREE
------------------------------------------------------------------------------------------ ------------------------------------------------------------
EMP 1
EMP_TEMP 1
scott@TEST>
alter
session
set
parallel_degree_policy=AUTO;
Session altered.
scott@TEST>
set
autotrace traceonly
scott@TEST>
select
*
from
emp;
14
rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
|
--------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 14 | 1218 | 3 (0)| 00:00:01 |
| 1 |
TABLE
ACCESS
FULL
| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
......
scott@TEST>
select
*
from
emp_temp;
1835008
rows
selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2661083444
--------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name
|
Rows
| Bytes | Cost (%CPU)|
Time
| TQ |
IN
-
OUT
| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 |
SELECT
STATEMENT | | 1835K| 66M| 1683 (1)| 00:00:21 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 1835K| 66M| 1683 (1)| 00:00:21 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 1835K| 66M| 1683 (1)| 00:00:21 | Q1,00 | PCWC | |
| 4 |
TABLE
ACCESS
FULL
| EMP_TEMP | 1835K| 66M| 1683 (1)| 00:00:21 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
......
|
从上面的输出可以看出表EMP和EMP_TEMP的并行度都为1，但是两个表的数据量相关很大，EMP只有14条数据，EMP_TEMP有1835008条数据。在执行时Oracle选择的执行方式就有不同，EMP是串行执行，而EMP_TEMP为并行执行。
参考《基于Oracle的SQL优化》
官方文档:http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2013.htm#i2231814
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1908054，如需转载请自行联系原作者
