Oracle并行计算

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

【前言】现在 CPU 的发展已不仅朝着单个性能更好的方向了，而且还朝着多核数多核心的方向发展了。Oracle 数据库大部分也都是利用单线程的串行方式在运行。通过并行（Parallel）操作特性，充分应用 CPU 的多核心特点，提高对数据的操作效率，满足在特定场景下对海量数据操作的需求。

OLTP 系统最主要的核心还是数据的录入操作，而这些应用的场景并不适合于并行计算的方式。对于 OLAP 业务场景更适合并行的操作，并行也成为数据库仓库调优利器；

【使用类型和场景】

Oracle 并行处理（Parallel Processing）特性主要是针对 SQL 语句处理的并行。目前 Oracle 提供支持并行的操作包括如下类型：

• 并行查询操作；
• 并行 DDL，对数据对象的 DDL 操作；
• 并行 DML，进行并行的数据更新修改；

在具体的应用场景上，有如下场景：

• 数据表、索引范围扫描的查询；
• 批量增加、修改和删除操作；
• 数据表、索引对象的创建；
• “DBMS_STATS” 收集统计量；
• 备份恢复管理器 Recovery Manager 的使用；

【设置并行和并行度】

• Alter session force parallel query parallel n;（ALTER SESSION enable parallel query;）单个 SESSION 里面
• Alter table tab1 parallel n; 单个 TABLE
• Select /*+parallel(tab n)*/ from tab; Hint 设置单个 SQL

优先级：Hint > session > object

【并行度设置】并行度的设置是以多核 CPU 为核心的，所以并行度不能超过 CPU 的逻辑数量，Oracle 官方文档介绍如下

If the PARALLEL clause is specified but no degree of parallelism is listed, the object gets the default DOP. Default parallelism uses a formula to determine the DOP based on the system configuration, as in the following:

• For a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT
• For an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

【实验测试】

环境说明：

数据库信息：

SQL> select * from v$version where rownum<2;

BANNER

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

CPU 信息

SQL> show parameter cpu

NAME TYPE VALUE

–––––––––––––––––––––––––––––––––––– ––––––––––– –––––––––––––

cpu_count integer 8

parallel_threads_per_cpu integer 1

resource_manager_cpu_allocation integer 8

【实验步骤】

正常情况下的执行计划

SQL> alter session set STATISTICS_LEVEL=ALL;

Session altered.

SQL> select count(1) from edidc;

COUNT(1)

––––––––––

47262191

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, ‘ALLSTATS’));

PLAN_TABLE_OUTPUT

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

SQL_ID 8h0snkxwm7x0w, child number 0

–––––––––––––––––––––––––––––––––––––

select count(1) from sapsr3.edidc

Plan hash value: 2138151876

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

Id | Operation | Name | Starts | E–Rows | A–Rows | A–Time | Buffers |

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:15.52| 351K|

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:15.52| 351K|

| 2 | INDEX FAST FULL SCAN | EDIDC~1 | 1 | 46M | 47M |00:00:09.95| 351K|

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

修改 session 的方式

SQL> alter session set STATISTICS_LEVEL=ALL;

SQL> Alter session force parallel query;

SQL> select count(1) from edidc;

COUNT(1)

––––––––––

47262191

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, ‘ALLSTATS’));

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| Id | Operation | Name | E–Rows |

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| 0 | SELECT STATEMENT | | |

| 1 | SORT AGGREGATE | | 1 |

| 2 | PX COORDINATOR | | |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |

| 4 | SORT AGGREGATE | | 1 |

| 5 | PX BLOCK ITERATOR | | 46M |

|* 6 | INDEX FAST FULL SCAN| EDIDC~1 | 46M |

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

Predicate Information (identified by operation id):

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

6 – access(:Z>=:Z AND :Z<=:Z)

––––––––––––––––––––––––––––––––––––––––––––––––––––––

用 hint 的方式进行

SQL> select /*+ PARALLEL(4) */ count(1) from edidc;

COUNT(1)

––––––––––

47262191

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, ‘ALLSTATS’));

PLAN_TABLE_OUTPUT

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

SQL_ID gr4rp3q9c4qu3, child number 0

–––––––––––––––––––––––––––––––––––––

select /*+ PARALLEL(4) */ count(1) from sapsr3.edidc

Plan hash value: 152749150

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| Id | Operation | Name | Starts | E–Rows | A–Rows | A–Time | Buffers | Reads |

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:13.86 | 25 | 0 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:13.86 | 25 | 0 |

| 2 | PX COORDINATOR | | 1 | | 4 |00:00:13.86 | 25 | 0 |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

| 4 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:52.76 | 357K | 351K |

| 5 | PX BLOCK ITERATOR | | 4 | 46M | 47M |00:00:41.69 | 357K | 351K |

|* 6 | INDEX FAST FULL SCAN | EDIDC~1 | 136 | 46M | 47M |00:00:20.42 | 357K | 351K |

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

Predicate Information (identified by operation id):

–––––––––––––––––––––––––––––––––––––––––––––––––––

6 – access(:Z>=:Z AND :Z<=:Z)

PLAN_TABLE_OUTPUT

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

修改 table 的方式

SQL> ALTER TABLE edidc PARALLEL 2; #设置完成后需要更新统计信息

SQL> SELECT TABLE_NAME, degree FROM dba_tables WHERE TABLE_NAME=‘EDIDC’;

TABLE_NAME DEGREE

–––––––––––––––––––––––––––––– ––––––––––––––––––––––––––––––

EDIDC 2

SQL> alter session set STATISTICS_LEVEL=ALL;

Session altered.

SQL> select count(1) from edidc;

COUNT(1)

––––––––––

47262191

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(null, null, ‘ALLSTATS’));

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| Id | Operation | Name | Starts | E–Rows | A–Rows | A–Time | Buffers | Reads |

–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:12.93 | 25 | 0 |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:12.93 | 25 | 0 |

| 2 | PX COORDINATOR | | 1 | | 8 |00:00:12.93 | 25 | 0 |

| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 |

| 4 | SORT AGGREGATE | | 8 | 1 | 8 |00:01:39.17 | 359K| 351K|

| 5 | PX BLOCK ITERATOR | | 8 | 46M | 47M|00:01:23.50 | 359K| 351K|

|* 6 | INDEX FAST FULL SCAN | EDIDC~1 | 164 | 46M| 47M|00:00:54.01 | 359K| 351K|

––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

Predicate Information (identified by operation id):

–––––––––––––––––––––––––––––––––––––––––––––––––––

6 – access(:Z>=:Z AND :Z<=:Z)

PLAN_TABLE_OUTPUT

––––––––––––––––––––––––––––––––––––––––––––––––––– 

SQL>ALTER TABLE TABLE_NAME NOPARALLEL; 取消并行

以上是针对查询的操作，同样也是适用于 DML 操作的；

并行的使用并是简单的以上的几个语句的套用，在 OLTP 系统中使用需谨慎，用好了是一把调优的利器，用不好可能反而会被利器所伤。本文档也是是针对并行的一篇基础文章，后续会针对更深入的应用继续说明。

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-05/143387.htm