博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
Oracle开启并行的几种方法
阅读量：6503 次
发布时间：2019-06-24

本文共 8972 字，大约阅读时间需要 29 分钟。

并行执行是同时开启多个进程/线程来完成同一个任务，并行执行的每一个进程/线程都会消耗额外的硬件资源，所以并行执行的本质就是以额外的硬件资源消耗来换取执行时间的缩短。这里的额外硬件资源消耗是指对数据库服务器上多个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优化》

官方文档:

      本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1908054，如需转载请自行联系原作者

你可能感兴趣的文章
flask框架
查看>>
《疯狂Java讲义》学习笔记（十）异常处理
查看>>
Lua(Codea) 中 table.insert 越界错误原因分析
查看>>
ELK 5.x日志分析 （二） Elasticserach 5.2 安装
查看>>
一次奇怪的AP注册异常问题处理
查看>>
TableStore: 海量结构化数据分层存储方案
查看>>
Unity 4.x游戏开发技巧集锦（内部资料）
查看>>
自适应网页设计
查看>>
获取BT节点信息bittorrent-discovery
查看>>
Centos 7使用vsftpd搭建FTP服务器
查看>>
linux下SVN不允许空白日志提交
查看>>
第2周第1课
查看>>
山寨c 标准库中的getline 函数
查看>>
shell时间
查看>>
pfSense book之2.4安装指南
查看>>
org.springframework.data.redis 一次连接获取特定key所有k-v(pipeline）
查看>>
[译稿]同步复制提议 2010-09
查看>>
windows 自动化目录大纲(各企业架构不一样，按需选择)
查看>>
我的友情链接
查看>>
【Visual C++】游戏开发笔记十三 游戏输入消息处理（二） 鼠标消息处理
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-01-19 15:07:34   当前IP: 3.144.117.164   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我