Oracle开启并行的几种方法

234次阅读

共计 8347 个字符，预计需要花费 21 分钟才能阅读完成。

并行执行是同时开启多个进程 / 线程来完成同一个任务，并行执行的每一个进程 / 线程都会消耗额外的硬件资源，所以并行执行的本质就是以额外的硬件资源消耗来换取执行时间的缩短。这里的额外硬件资源消耗是指对数据库服务器上多个 CPU、内存、从个 I / O 通道，甚至是 RAC 环境下多个数据库节点的额外利用。

下面总结一下 Oracle 里开启并行的几种方法

1、更改目标表的并行度

有两种方法修改目标表的并行度

alter table table_name parallel;

alter table table_name parallel n;

其中方法 1 是把指定表的并行度修改为默认值，方法 2 是把指定表的并行度修改为 n；

查看表 EMP 当前的并行度为 1

scott@TEST>select table_name,degree from user_tables where table_name=’EMP’;

TABLE_NAME DEGREE
—————————— ———-
EMP 1

想用默认的并行度去访问表 EMP

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;

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

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

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 中强制开启并行:

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;

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 自动来决定了。

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 | |
————————————————————————————————————–
……