Oracle 11.2中控制并行的新参数

268次阅读

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

在 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

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

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

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

——————————————————————————————————–
| 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=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=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 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-04/130545.htm

正文完

星哥玩云-微信公众号