Oracle 11.2中控制并行的新参数

吴正清

2016-04-22

在Oracle 11.2中引入了几个新的并行查询参数。对于数据仓库应用来说经常利用并行处理来快速有效地处理信息，尤其是查询非常大的表或加入了复杂的算式更应该使用并行查询。在Oracle之前的版本中，我们不得不或多或秒的来决定自动并行度。决定一个最佳并行度是非常困难的。真实最佳并行度依赖于数据块在磁盘上的物理位置以及服务器的CPU数量(cpu_count)，为了解决并行查询的这些问题，在Oracle11.2中引入了以下新的并行查询参数。

1.parallel_degree_policy
parallel_degree_policy参数可以被设置为manual,auto或limited在Oracle11.1中parallel_degree_policy缺省设置为manual(禁用了automatic degree of parallelism,statement queuing与in-memory parallel execution)
SQL> show parameter parallel_degree_policy;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string manual
SQL> set autotrace on
SQL> select count(*) from t1;

COUNT(*)
----------
22040576

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84998 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 21M| 84998 (1)| 00:00:06 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
469904 consistent gets
313229 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed

但我们可以手动指定并行度
SQL> show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string manual
SQL> set autotrace on
SQL> select /*+ parallel */ count(*) from t1;

COUNT(*)
----------
22040576

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47183 (1)| 00:00:04 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T1 | 21M| 47183 (1)| 00:00:04 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=5)
- automatic DOP: Computed Degree of Parallelism is 2

Statistics
----------------------------------------------------------
20 recursive calls
4 db block gets
470138 consistent gets
313225 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

parallel_degree_policy=auto就会启用以下新功能:
并行度(DOP)将会基于SQL语句中的操作类型和表的大小来自动计算。例如对大表排序的并行度(DOP)可能比对小表操作的并行度高。

如果请求或请求的并行度(DOP)因为并行服务进程正处于繁忙状态而不能获得满足，那么Oracle直到有足够的并行子进程可用之前将不会执行语句，而不是降低并行度或串行执行SQL语句。在11gr2之前的版本中，当没有足够的并行进程服务进程满足所请求的并行度(DOP)时，可以会出现以下三种情况中的一种:
SQL语句将会降低并行度(DOP)来以并行方式执行
SQL语句以串行方式来执行
如果parallel_min_percent被设置将收到"ORA-12827:insufficient parallel query slaves available"

Oracle并行子进程可能使用buffered IO而不是直接IO。例如"in-memory parallel execution"
SQL> show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO

SQL> select degree,instances from user_tables where table_name = 'T1';

DEGREE INSTANCES
-------------------- --------------------
1 1

Elapsed: 00:00:00.00
SQL> set autotrace on
SQL> select count(*) from t1;

COUNT(*)
----------
22040576

Elapsed: 00:00:18.50

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
469841 consistent gets
313226 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

parallel_degree_policy设置为limited
对某些语句启用自动并行度，但statement queuing与in-memory parallel execution被禁用。只会对访问使用parallel子句来设置DEFAULT并行度的表或索引应用自动并行度。
SQL> select degree,instances from user_tables where table_name = 'T1';

DEGREE INSTANCES
-------------------- --------------------
1 1

SQL> show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string LIMITED
SQL> set autotrace on;
SQL> select count(*) from t1;

COUNT(*)
----------
22040576

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
469898 consistent gets
313399 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

因为表的并行度是1,而不是default,现在使用parallel子句来修改表t1的并行度
SQL> alter table t1 parallel;

Table altered.

SQL> select degree,instances from user_tables where table_name = 'T1';

DEGREE INSTANCES
-------------------- --------------------
DEFAULT DEFAULT

SQL> set autotrace on
SQL> select count(*) from t1;

COUNT(*)
----------
22040576

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

Note
-----
- dynamic sampling used for this statement (level=5)
- automatic DOP: Computed Degree of Parallelism is 2

Statistics
----------------------------------------------------------
83 recursive calls
0 db block gets
470167 consistent gets
313413 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

2.parallel_min_time_threshold
parallel_min_time_threshold参数用来指定SQL语句是否并行执行一个阈值，也就是当优化器根据统计信息所估算的执行时间如果大于这个参数值就是使用并行，如果估算的执行时间小于这个参数值就会串行执行。这个参数值缺省值是10秒。并且自动并行度只要在parallel_degree_policy参数被设置为auto或limited时才会生效。从下面的信息可以看到到语句的执行时间小于10秒时，优化器以是串行而不是并行方式来执行的
SQL> show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> set autotrace on
SQL> select count(*) from t1;

COUNT(*)
----------
2755072

Elapsed: 00:00:02.66

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10627 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 2569K| 10627 (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
57150 consistent gets
39162 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

再次增加表t1的数据记录
SQL> insert into t1 select * from t1;

5510144 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> set autotrace on
SQL> select count(*) from t1;

COUNT(*)
----------
11020288

Elapsed: 00:00:09.05

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42507 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 11M| 42507 (1)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
223549 consistent gets
156619 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

可以看到执行时间为9.05秒，Oracle使用串行执行，继续向表t1增加记录
SQL> insert into t1 select * from t1;

11020288 rows created.

SQL> commit;

Commit complete.

SQL> alter system flush buffer_cache;

System altered.

SQL> show parameter parallel_degree_policy

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_degree_policy string AUTO
SQL> select * from V$IO_CALIBRATION_STATUS;

STATUS CALIBRATION_TIME
------------- ---------------------------------------------------------------------------
READY 13-APR-16 10.12.58.413 PM

Elapsed: 00:00:00.08
SQL> set autotrace on
SQL> select count(*) from t1;

COUNT(*)
----------
22040576

Elapsed: 00:00:18.50

Execution Plan
----------------------------------------------------------
Plan hash value: 3110199320

Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: Computed Degree of Parallelism is 2

现在可以看到当parallel_degree_policy=auto,parallel_min_time_threshold=auto时，sql执行时间超长10秒时就会使用自动并行。

3.parallel_degree_limit
使用自动并行度时，Oracle会自动决定是否以并行方式来执行SQL语句以及所使用的并行度。优化根据语句所请求的资源来决定一个语句的并行度。然而优化器所使用的并行度是受限制的以防止并行进程击垮系统。也就是是系统中所能使用的并行度的上限为parallel_degree_limit参数值。它有三个参数值可以选择：
CPU
最大并行度由系统中的CPU数量来限制。其计算公式为parallel_degree_limit=parallel_thread_per_cpu*cpu_count
当然，你也可以将parallel_degree_limit的值设置为一个具体的值，以达到明确控制实际并行度的目的。

IO
优化器能使用的最大并行度由系统的I/O能力来限制。这个值等于系统总吞吐量除以每个进程的最大I/O带宽。但在Oracle 11.2中为了将parallel_degree_limit设置为IO必须执行dbms_resource_manager.calibrate_io过程来收集系统的I/O统计信息。这个过程将会计算系统的总吞吐量与每个进程的最大IO带宽。

具体数字
当自动并行度被激活时，指定一个SQL语句所能使用的最大并行度。这个参数只有当parallel_degree_policy设置为auto或limited时才生效。

4.parallel_force_local
parallel_force_local参数控制RAC环境中的并行执行。缺省情况下，优化器可以从RAC中的任何节点或所有节点中选择并行执行SQL语句的并行进程。当parallel_force_local设置为true时，那么并行进程就只能是与查询协调者(执行sql语句的节点)在同一个RAC节点中，也就是说并行进程是不能跨节点的.

oracle 并行处理

安科网

Oracle 11.2中控制并行的新参数

吴正清

吴正清

相关推荐

关于使用PLSQL Developer时出现报错ora-12514的问题

oracle锁表该如何解决

Oracle表字段有Oracle关键字出现异常解决方案

甲骨文推出Oracle Exadata云服务X8M

Oracle云观测和管理平台正式推出

Oracle员工关怀工具包以三大重点帮助员工适应工作环境

oracle备份之备份测试脚本的方法（冷备、热备、rman)

限制ip访问Oracle数据库的方法步骤

oracle merge into

Oracle和MySQL的数据导入为何差别这么大

Oracle数据库的实例/表空间/用户/表之间关系简单讲解

浅谈入门级oracle数据库数据导入导出步骤

oracle数据库的删除方法详解

oracle 日期操作语句总结

oracle调试存储过程的过程详解

Oracle如何使用PL/SQL调试存储过程

Oracle通过LogMiner实现数据同步迁移

CMD操作oracle数据导库过程图解

ORACLE数据库中Rownum用法详解

oracle数据库关于索引建立及使用的详细介绍

吴正清