【前言】现在CPU的发展已不仅朝着单个性能更好的方向了，而且还朝着多核数多核心的方向发展了。Oracle数据库大部分也都是利用单线程的串行方式在运行。通过并行（Parallel）操作特性，充分应用CPU的多核心特点，提高对数据的操作效率，满足在特定场景下对海量数据操作的需求。
OLTP系统最主要的核心还是数据的录入操作，而这些应用的场景并不适合于并行计算的方式。对于OLAP业务场景更适合并行的操作，并行也成为数据库仓库调优利器；
【使用类型和场景】
Oracle并行处理（Parallel Processing）特性主要是针对SQL语句处理的并行。目前Oracle提供支持并行的操作包括如下类型：
- 并行查询操作；
- 并行DDL，对数据对象的DDL操作；
- 并行DML，进行并行的数据更新修改；
在具体的应用场景上，有如下场景：
【设置并行和并行度】
- 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
- 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系统中使用需谨慎，用好了是一把调优的利器，用不好可能反而会被利器所伤。本文档也是是针对并行的一篇基础文章，后续会针对更深入的应用继续说明。
文章和留言都翻到11页了 没有OOM
朋友，翻页到11页，及以后，会出现OOM，无法访问
搞个gitee的项目
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
