(一)Oracle中的并行
首先,Oracle会创建一个进程用于协调并行服务进程之间的信息传递,这个协调进程将需要操作的数据集(例如表的数据块)分割成很多部分,称为并行处理单元,然后并行协调进程给每个并行进程分配一个数据单元。例如有四个并行服务进程,它们就会同时处理各自分配的单元,当一个并行服务进程处理完毕后,协调进程就会给它们分配另外的单元,如此反复,直到表上的数据都处理完毕,最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果,返回给用户。并行处理的机制实际上就是把一个要扫描的数据集分成很多小数据集,Oracle会启动几个并行服务进程同时处理这些小数据集,最后将这些结果汇总,作为最终的处理结果返回给用户。
这种数据并行处理方式在OLAP系统中非常有用,OLAP系统的表通常来说都非常大,如果系统的CPU比较多,那么可以让所有的CPU共同来处理这些数据,效果就会比串行执行要好得多。对于OLTP系统,通常而言,并行并不合适,原因是OLTP系统上几乎在所有的SQL操作中,数据访问路径基本上以索引访问为主,并且返回结果集非常小,这样的SQL操作的处理速度一般非常快,不需要启用并行。
使用并行方式,不论是创建表,还是修改表、创建索引、重建索引,它们的机制都是一样的,那就是Oracle给每个并行服务进程分配一块空间,每个进程在自己的空间里处理数据,最后将处理完毕的数据汇总,完成SQL的操作。
1. 并行执行的使用范围
Oracle的并行技术在下面的场景中可以使用:
(1)PARALLEL QUERY(并行查询,简称PQ)。
(2)PARALLEL DDL(并行DDL操作,简称PDDL,例如建表、建索引等)。
(3)PARALLEL DML(并行DML操作,简称PDML,例如INSERT、UPDATE、DELETE等)。
2. 并行查询(PQ)
并行查询可以在查询语句、子查询语句中使用,但是不可以使用在一个远程引用的对象上(例如DBLINK)。当一条SQL语句发生全表扫描、全分区扫描及索引快速全扫描的时候,若优化器满足下面的条件之一就可以使用并行处理:
① 会话级别,会话设置了强制并行,例如,“ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4;”,执行“SELECT COUNT(*) FROM TB_PART_LHR;”,这里的TB_PART_LHR为分区表。
② 语句级别,SQL语句中有Hint提示,例如,使用PARALLEL或者PARALLEL_INDEX。如,“SELECT /*+ PARALLEL(T 4) */ FROM T;”。
③ SQL语句中引用的对象被设置了并行属性。在表和索引的定义中增加并行度属性,该属性可以在创建表和索引时设置,也可对已创建的表和索引的并行度属性进行修改。例如,“ALTER TABLE TB_NAME PARALLEL 4;”、“ALTER TABLE TB_NAME PARALLEL (DEGREE DEFAULT);”。取消表或索引的并行度的SQL为:“ALTER TABLE TB_NAME NOPARALLEL;”。示例如下:
SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE 10);
Table altered.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';
DEGREE
--------------------
10
SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);
Table altered.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';
DEGREE
--------------------
DEFAULT
SYS@orclasm > ALTER TABLE SH.SALES NOPARALLEL;
Table altered.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';
DEGREE
--------------------
1
SYS@orclasm > CREATE TABLE SCOTT.AA AS SELECT * FROM DUAL;
Table created.
SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='AA' AND OWNER='SCOTT';
DEGREE
--------------------
1
在日常使用上,一般不建议在对象级别定义并行度,因为这会导致相关对象的操作都变为并行处理,而并行处理会占用大量的CPU资源,导致数据库整体性能失控。一般在会话或语句级别进行处理。
3. 一些参数
和并行相关的参数较多,下面给出几个常见的参数,其它参数请参考官方文档:
l PARALLEL_MIN_SERVERS:默认值为0,确定实例上并行执行进程的最小数,该值是Oracle实例启动时创建的并行执行进程的数目,可以使用“ ps -ef|grep ora_p0”来查看。Oracle RAC多个实例可以有不同的值。若修改了该值,则只有当数据库实例重启的情况下后台进程数才会变化。
l PARALLEL_MAX_SERVERS:默认值为PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5。该参数确定一个实例并行执行进程和并行恢复进程的最大数。当需求增加时,Oracle数据库从实例启动时的进程数增加到该参数值。在默认值计算公式中,实例上赋予正在使用的concurrent_parallel_users的值和内存管理设置相关。如果自动内存管理被关闭(手工模式),那么concurrent_parallel_users为1。如果PGA自动内存管理被开启,那么concurrent_parallel_users的值为2.如果除了PGA自动内存管理,全局内存管理或SGA内存目标也被使用,那么,concurrent_parallel_users为4。Oracle RAC多个实例可以有不同值。
l PARALLEL_MIN_TIME_THRESHOLD:确定一个语句被考虑采用自动并行度前一个语句将用的最小执行时间。默认值为AUTO,表示10s。只有PARALLEL_DEGREE_POLICY被设置为AUTO或LIMITED时,自动并行度才被开启。
l PARALLEL_DEGREE_POLICY:该参数确定是否开启自动并行度,语句排队和内存并行执行。包括MANUAL、LIMITIED和AUTO,默认值为MANUAL。如果一个PARALLEL Hint在语句级被使用,那么无论PARALLEL_DEGREE_POLICY值被设置成什么,自动并行度都将被开启。注意:该参数尽量不要修改为AUTO,因为相关的Bug较多,一般使用MANUAL即可。
n MANUAL:关闭自动并行度,语句排队和内存并行执行。这恢复并行执行到11.2之前的行为。这是默认设置。
n LIMITED:对某些语句开启自动并行执行,但语句排队和内存并行执行被关闭。自动并行度仅仅适用那些存取显式用PARALELL语句标示默认并行度的表或索引的语句。并不存取这些被显式标示默认并行度的表或索引的语句将保持手工(MANUAL)行为。
n AUTO:开启自动并行度,语句排队和内存并行执行。
4. I/O Calibration和DOP的关系
从Oracle 11.2.0.2开始,只有当I/O Calibration(I/O 校准、I/O统计信息)被收集才能使用自动并行度(DOP,Automatic Degree of Parallelism)。当PARALLEL_DEGREE_POLICY被设置为AUTO时,Oracle数据库将会基于执行计划中操作的成本和硬件特性来判断是否使用并行。如果一个PARALLEL Hint在语句级被使用,那么无论PARALLEL_DEGREE_POLICY的值设置成什么,自动并行度都将被开启。
若没有收集I/O Calibration统计数据,则在执行计划的Note部分可以看到“automatic DOP: skipped because of IO calibrate statistics are missing”这样的信息。若使用了DOP,则可以在执行计划的Note部分可以看到类似于“automatic DOP: Computed Degree of Parallelism is 2”的信息。
Oracle提供了PL/SQL包DBMS_RESOURCE_MANAGER.CALIBRATE_IO来收集I/O Calibration的统计数据。收集I/O Calibration统计数据的持续时间由NUM_DISKS变量与RAC中节点数决定的。视图V$IO_CALIBRATION_STATUS可以查询是否收集了I/O Calibration统计数据。若没有收集I/O Calibration,则可以使用如下的存储过程来收集:
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
END;
/
注意,DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks和max_latency是输入变量,并且有三个输出变量。
num_disks:为了获得最精确的结果,最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件,那么就是指存储数据的磁盘组,那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值,不包含FRA磁盘组中的物理磁盘。
latency:对数据库块I/O操作允许的最大延迟。
5. 并行DDL操作(PDDL)
表或索引的CREATE或ALTER操作可以使用并行。例如,以下表操作可以使用并行执行:
l 建表:CREATE TABLE … AS SELECT(CTAS)
l 表移动:ALTER TABLE … MOVE
l 表分区移动:ALTER TABLE … MOVE PARTITION
l 表分区并行分解:ALTER TABLE … SPLIT PARTITION
l 表分区并行合并:ALTER TABLE … COALESCE PARTITION
l 创建和校验约束:ALTER TABLE … ADD CONSTRAINT
l 创建索引:CREATE INDEX
l 重建索引:ALTER INDEX … REBULD
l 重建索引分区:ALTER INDEX … REBULD PARTITION
l 索引分区的分解:ALTER INDEX … SPLIT PARTITION
6. 并行DML操作(PDML)
Oracle可以对DML操作使用并行执行。如果要让DML操作使用并行执行,那么必须显式地在会话里执行如下命令:
ALTER SESSION ENABLE PARALLEL DML;
只有执行了这个命令,Oracle才会对之后符合并行条件的DML操作并行执行,如果没有这个设定,那么即使SQL中指定了并行执行,Oracle也会忽略它。
以下给出一个并行UPDATE的示例:
LHR@TEST> CREATE TABLE TB_LHR20160518 AS SELECT * FROM DBA_OBJECTS;
Table created.
LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 2194116729
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | TB_LHR20160518 | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TB_LHR20160518 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
12 rows selected.
LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';
Explained.
LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2194116729
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | UPDATE | TB_LHR20160518 | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TB_LHR20160518 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
12 rows selected.
LHR@test> ALTER SESSION ENABLE PARALLEL DML;
Session altered.
LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';
Explained.
LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 3729706116
-----------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | UPDATE | TB_LHR20160518 | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| TB_LHR20160518 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------
12 rows selected.
通过执行计划可以看出,只有执行了“ALTER SESSION ENABLE PARALLEL DML;”后,UPDATE操作才真正地实现了并行操作,如果不执行该语句,那么只是执行了并发查询,并没有实现并发更新操作。
下表列出了这3种并行处理方式的开启及禁用语句:
类别 |
区别 |
|
并行查询(PQ) |
默认 |
开启 |
查询 |
SELECT D.PQ_STATUS FROM V$SESSION D WHERE D.SID=USERENV('sid'); |
|
启用、禁用 |
ALTER SESSION ENABLE PARALLEL QUERY; --启用 ALTER SESSION FORCE PARALLEL QUERY PARALLEL n; --强制开启 ALTER SESSION DISABLE PARALLEL QUERY; --禁用 |
|
并行DDL(PDDL) |
默认 |
开启 |
查询 |
SELECT D.PDDL_STATUS FROM V$SESSION D WHERE D.SID=USERENV('sid'); |
|
启用、禁用 |
ALTER SESSION ENABLE PARALLEL DDL; --启用 ALTER SESSION FORCE PARALLEL DDL PARALLEL n; --强制开启 ALTER SESSION DISABLE PARALLEL DDL; --禁用 |
|
并行DML(PDML) |
默认 |
关闭 |
查询 |
SELECT D.PDML_STATUS FROM V$SESSION D WHERE D.SID=USERENV('sid'); |
|
启用、禁用 |
ALTER SESSION ENABLE PARALLEL DML; --启用 ALTER SESSION FORCE PARALLEL DML PARALLEL n; --强制开启 ALTER SESSION DISABLE PARALLEL DML; --禁用 |
7. RAC中的并行
如果连接Oracle RAC数据库,那么一个节点上的并发操作可以分布到多个节点上同时执行。可以使用视图GV$PX_SESSION查询并行会话的进程。有关RAC可以参考【 REF _Ref2346 /n /h 3.2.16 REF _Ref2346 /h RAC维护】。
这是一个Oracle 11g的RAC环境,下面建立一张测试表,建立过程中设置表的并行度:
[ZFWWLHRDB1:oracle]:/oracle>ORACLE_SID=raclhr1
[ZFWWLHRDB1:oracle]:/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 14:52:23 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@raclhr1> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SYS@raclhr1> CREATE TABLE T_PARALLEL_LHR NOLOGGING PARALLEL 4
2 AS SELECT A.* FROM DBA_OBJECTS A, DBA_TABLES
3 WHERE ROWNUM <= 5000000;
Table created.
SYS@raclhr1> SELECT * FROM V$MYSTAT WHERE ROWNUM<=1;
SID STATISTIC# VALUE
---------- ---------- ----------
167 0 0
SYS@raclhr1> set autot on
SYS@raclhr1> SET LINESIZE 9999
SYS@raclhr1> SET PAGESIZE 9999
SYS@raclhr1> SELECT COUNT(*) FROM T_PARALLEL_LHR a,T_PARALLEL_LHR b where rownum<=1000000;
COUNT(*)
----------
1000000
Execution Plan
----------------------------------------------------------
Plan hash value: 1691788013
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2057M (5)|999:59:59 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
|* 2 | COUNT STOPKEY | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 23T| 2057M (5)|999:59:59 | Q1,01 | P->S | QC (RAND) |
|* 5 | COUNT STOPKEY | | | | | Q1,01 | PCWC | |
| 6 | MERGE JOIN CARTESIAN | | 23T| 2057M (5)|999:59:59 | Q1,01 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 4857K| 5396 (1)| 00:01:05 | Q1,01 | PCWC | |
| 8 | TABLE ACCESS FULL | T_PARALLEL_LHR | 4857K| 5396 (1)| 00:01:05 | Q1,01 | PCWP | |
| 9 | BUFFER SORT | | 4857K| 2057M (5)|999:59:59 | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 4857K| 5396 (1)| 00:01:05 | Q1,01 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10000 | 4857K| 5396 (1)| 00:01:05 | Q1,00 | P->P | BROADCAST |
| 12 | PX BLOCK ITERATOR | | 4857K| 5396 (1)| 00:01:05 | Q1,00 | PCWC | |
| 13 | TABLE ACCESS FULL| T_PARALLEL_LHR | 4857K| 5396 (1)| 00:01:05 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=1000000)
5 - filter(ROWNUM<=1000000)
Note
-----
- dynamic sampling used for this statement (level=4)
Statistics
----------------------------------------------------------
112 recursive calls
8 db block gets
72078 consistent gets
74257 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
4 sorts (disk)
1 rows processed
从执行计划可以看到,Oracle选择了并行执行。
新建立一个会话,在执行上面这个并行查询的同时查询GV$PX_SESSION(或GV$PX_PROCESS)视图:
SYS@raclhr1> SELECT * FROM GV$PX_SESSION WHERE QCSID=167;
INST_ID SADDR SID SERIAL# QCSID QCSERIAL# QCINST_ID SERVER_GROUP SERVER_SET SERVER# DEGREE REQ_DEGREE
---------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
1 07000100538364A0 199 35 167 5 1 1 1 1 4 4
1 0700010053894FC0 230 35 167 5 1 1 1 2 4 4
1 0700010053607480 10 37 167 5 1 1 2 1 4 4
1 070001005366F240 38 3 167 5 1 1 2 2 4 4
1 07000100537DAA60 167 5 167
2 070001005383F740 196 43 167 5 1 1 1 3 4 4
2 07000100536D3F20 67 9 167 5 1 1 1 4 4 4
2 07000100536168E0 5 5 167 5 1 1 2 3 4 4
2 07000100536784E0 35 113 167 5 1 1 2 4 4 4
9 rows selected.
很显然,并行查询的4个进程已经分布到两个节点上同时执行了,每个节点上创建4个并行从属进程。
& 说明:
有关Oracle中NOLOGGING、APPEND、ARCHIVE和PARALLEL下,Redo、Undo和执行速度的比较具体操作过程可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2125815/
有关什么是I/O Calibration的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2148709/
有关Oracle中并行的的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2149240/
真题1、在Oracle中,I/O Calibration和DOP有什么关系?
答案:从Oracle 11.2.0.2开始,只有当I/O Calibration(I/O 校准、I/O统计信息)被收集才能使用自动并行度(DOP,Automatic Degree of Parallelism)。当PARALLEL_DEGREE_POLICY被设置为AUTO时,Oracle数据库将会基于执行计划中操作的成本和硬件特性来判断是否使用并行。如果一个PARALLEL Hint在语句级被使用,那么无论PARALLEL_DEGREE_POLICY的值设置成什么,自动并行度都将被开启。
若没有收集I/O Calibration统计数据,则在执行计划的Note部分可以看到“automatic DOP: skipped because of IO calibrate statistics are missing”这样的信息。若使用了DOP,则可以在执行计划的Note部分可以看到类似于“automatic DOP: Computed Degree of Parallelism is 2”的信息。
Oracle提供了PL/SQL包DBMS_RESOURCE_MANAGER.CALIBRATE_IO来收集I/O Calibration的统计数据。收集I/O Calibration统计数据的持续时间由NUM_DISKS变量与RAC中节点数决定的。视图V$IO_CALIBRATION_STATUS可以查询是否收集了I/O Calibration统计数据。若没有收集I/O Calibration,则可以使用如下的存储过程来收集:
SET SERVEROUTPUT ON
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
--DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat);
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
dbms_output.put_line('max_mbps = ' || mbps);
END;
/
注意,DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks和max_latency是输入变量,并且有三个输出变量。
num_disks:为了获得最精确的结果,最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件,那么就是指存储数据的磁盘组,那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值,不包含FRA磁盘组中的物理磁盘。
latency:对数据库块I/O操作允许的最大延迟。
& 说明:
有关什么是I/O Calibration的更多内容可以参考我的BLOG:http://blog.itpub.net/26736162/viewspace-2148709/
1、PARALLEL_ADAPTIVE_MULTI_USER
参数类型 Boolean原文地址:Oracle 11.2中控制并行的新参数 作者:eric0435
在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 11gr2中的自动并行度 作者:eric0435
在Oracle 11.2.0.2中只有I/O统计数据被收集才能使用自动并行度。当parallel_degree_policy被设置为auto时,Oracle数据库将会基于执行计划中操作的成本和硬件特性来判断是否使用并行,当在语句级别使用parallel或parallel(auto)暗示不管parallel_degree_policy设置为何值都会使用自动并行。
IO Calibration
硬件特性包括IO Calibration统计数据,因此这些统计数据必须被收集否则Oracle数据库将不会使用自动并行这个功能。下面的执行计划是在没有收集IO Calibration统计数据时生成的,在执行计划的note部分可以看到"skipped because of IO calibrate statistics are missing"这样的信息
SQL> set long 900 SQL> set linesize 900 SQL> set autotrace traceonly explain SQL> select /*+ parallel */ * from emp; 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 | | -------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - automatic DOP: skipped because of IO calibrate statistics are missing
Oracle提供了PL/SQL包dbms_resource_manager.calibrate_io来收集IO Calibration的统计数据。收集IO Calibration统计数据的持续时间由num_disks变量与RAC中节点数决定的。
SQL> select * from V$IO_CALIBRATION_STATUS; STATUS CALIBRATION_TIME ------------- --------------------------------------------------------------------------- NOT AVAILABLE SET SERVEROUTPUT ON DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat); DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); dbms_output.put_line('max_mbps = ' || mbps); END; /
注意DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks,max_latency是输入变量,并且有三个输出变量。
num_disks:为了获得最精确的结果,最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件,那么就是指存储数据的磁盘组,那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值,不包含FRA磁盘组中的物理磁盘。
latency:对数据库块IO操作允许的最大延迟
SQL> set long 900 SQL> set linesize 900 SQL> SET SERVEROUTPUT ON DECLARE SQL> 2 lat INTEGER; 3 iops INTEGER; 4 mbps INTEGER; 5 BEGIN 6 --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat); 8 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 9 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 10 dbms_output.put_line('max_mbps = ' || mbps); 11 END; 12 / max_iops = 390 latency = 9 max_mbps = 112 PL/SQL procedure successfully completed.
为了验证是否IO Calibration统计信息收集成功,在执行dbms_resource_manager.calibrate_io后查询v$io_calibration_status
SQL> select * from V$IO_CALIBRATION_STATUS; STATUS CALIBRATION_TIME ------------- --------------------------------------------------------------------------- READY 13-APR-16 10.12.58.413 PM
再次执行看是否能使用自动并行度
SQL> set autotrace traceonly explain SQL> select /*+ parallel */ * from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2873591275 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Note ----- - automatic DOP: Computed Degree of Parallelism is 2
可以看到在收集IO Calibration统计信息后,执行计划使用自动并行度。
当使用自动并行度,可以还需要调整一些调整参数。parallel_servers_target参数应该总是比parallel_max_servers参数值小,parallel_servers_target总是处于parallel_max_servers的75%到50%。如果开始看到大量并行度下降,那么应该使用这两个参灵敏的差距增大。
Property | Description |
---|---|
Parameter type | Boolean |
Default value | true |
Modifiable | ALTER SYSTEM |
Range of values | true | false |
PARALLEL_ADAPTIVE_MULTI_USER, when set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
当PARALLEL_ADAPTIVE_MULTI_USER参数设置为TRUE,启用设计的适当算法,在多用户环境下使用并行执行提升性能。这个算法基于查询开始时的系统负载自动减少请求的并行度。有效的并行度是基于默认的并行度,或者来自表或HINT的并行度,通过减少系数进行分割。
The algorithm assumes that the system has been tuned for optimal performance in a single-user environment.
算法假定系统在单用户环境下按照最优性能被调整。
Tables and hints use the default degree of parallelism.
表和HINT使用默认的并行度。
Property | Description |
---|---|
Parameter type | Integer |
Default value | PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5 |
Modifiable | ALTER SYSTEM |
Range of values | 0 to 3600 |
Oracle RAC | Multiple instances can have different values. |
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle Database increases the number of processes from the number created at instance startup up to this value.
PARALLEL_MAX_SERVERS指定实例最大并行执行进程和并行恢复进程数。随着增长需求,Oracle数据库需要增加进程数,从实例启动时创建的数目到增长值。
In the formula, the value assigned to concurrent_parallel_users running at the default degree of parallelism on an instance is dependent on the memory management setting. If automatic memory management is disabled (manual mode), then the value of concurrent_parallel_users is 1. If PGA automatic memory management is enabled, then the value of concurrent_parallel_users is 2. If global memory management or SGA memory target is used in addition to PGA automatic memory management, then the value of concurrent_parallel_users is 4.
根据上面的公式,分配给concurrent_parallel_users的值,运行在实例的默认并行度依赖于内存管理设置。如果禁用自动内存管理(手动模式),那么concurrent_parallel_user的值是1,如果启用PGA自动内存管理,那么concurrent_parallel_users的值是2。如果除了PGA自动内存管理外,还使用了全局内存管理或者SGA内存target,那么concurrent_parallel_users的值是4。
If you set this parameter too low, then some queries may not have a parallel execution process available to them during query processing. If you set it too high, then memory resource shortages may occur during peak periods, which can degrade performance.
如果设置这个参数过小,那么某些查询在查询过程中可能没有并行执行进程活动。如果设置这个参数过大,那么在峰值期间内存资源可能不足,导致性能下降。
Property | Description |
---|---|
Parameter type | Integer |
Default value | 0 |
Modifiable | ALTER SYSTEM |
Range of values | 0 to value of PARALLEL_MAX_SERVERS |
Oracle RAC | Multiple instances can have different values. |
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.
PARALLEL_MIN_SERVERS指定实例并行执行进程数的最小值。这个值是实例在启动时,Oracle创建的并行执行进程数。
Property | Description |
---|---|
Parameter type | Integer |
Default value | Operating system-dependent, usually 2 |
Modifiable | ALTER SYSTEM |
Range of values | Any nonzero number |
Note:
This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_THREADS_PER_CPU specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.
PARALLEL_THREADS_PER_CPU指定实例默认的并行度,确定合适的并行和负载均衡算法。这个参数描述并行执行进程数,或者在并行执行期间CPU能处理的线程数。
The default is platform-dependent and is adequate in most cases. You should decrease the value of this parameter if the machine appears to be overloaded when a representative parallel query is executed. You should increase the value if the system is I/O bound.
默认值依赖于平台,在大多数情况下都是合适的。当执行一个典型的并行查询时,服务器出现过载的情况,应该减少这个参数的值。如果系统在I/O的边界应该增加这个值。
Select * from v$pq_syssstat;
Then: Get/save the value for row "Servers Highwater"
2009-08-22 17:16:08| 分类: 系统管理技术|举报|字号 订阅
About Me
.............................................................................................................................................
● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除
● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号:230161599(满)、618766405
● 微信群:可加我微信,我拉大家进群,非诚勿扰
● 联系我请加QQ好友(646634621),注明添加缘由
● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
.............................................................................................................................................
● 小麦苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书:http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群 小麦苗的微店
.............................................................................................................................................
在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 11.2.0.2中只有I/O统计数据被收集才能使用自动并行度。当parallel_degree_policy被设置为auto时,Oracle数据库将会基于执行计划中操作的成本和硬件特性来判断是否使用并行,当在语句级别使用parallel或parallel(auto)暗示不管parallel_degree_policy设置为何值都会使用自动并行。
IO Calibration
硬件特性包括IO Calibration统计数据,因此这些统计数据必须被收集否则Oracle数据库将不会使用自动并行这个功能。下面的执行计划是在没有收集IO Calibration统计数据时生成的,在执行计划的note部分可以看到"skipped because of IO calibrate statistics are missing"这样的信息
SQL> set long 900 SQL> set linesize 900 SQL> set autotrace traceonly explain SQL> select /*+ parallel */ * from emp; 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 | | -------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - automatic DOP: skipped because of IO calibrate statistics are missing
Oracle提供了PL/SQL包dbms_resource_manager.calibrate_io来收集IO Calibration的统计数据。收集IO Calibration统计数据的持续时间由num_disks变量与RAC中节点数决定的。
SQL> select * from V$IO_CALIBRATION_STATUS; STATUS CALIBRATION_TIME ------------- --------------------------------------------------------------------------- NOT AVAILABLE SET SERVEROUTPUT ON DECLARE lat INTEGER; iops INTEGER; mbps INTEGER; BEGIN --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat); DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat); DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); dbms_output.put_line('max_mbps = ' || mbps); END; /
注意DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks,max_latency是输入变量,并且有三个输出变量。
num_disks:为了获得最精确的结果,最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件,那么就是指存储数据的磁盘组,那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值,不包含FRA磁盘组中的物理磁盘。
latency:对数据库块IO操作允许的最大延迟
SQL> set long 900 SQL> set linesize 900 SQL> SET SERVEROUTPUT ON DECLARE SQL> 2 lat INTEGER; 3 iops INTEGER; 4 mbps INTEGER; 5 BEGIN 6 --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat); 7 DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat); 8 DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops); 9 DBMS_OUTPUT.PUT_LINE ('latency = ' || lat); 10 dbms_output.put_line('max_mbps = ' || mbps); 11 END; 12 / max_iops = 390 latency = 9 max_mbps = 112 PL/SQL procedure successfully completed.
为了验证是否IO Calibration统计信息收集成功,在执行dbms_resource_manager.calibrate_io后查询v$io_calibration_status
SQL> select * from V$IO_CALIBRATION_STATUS; STATUS CALIBRATION_TIME ------------- --------------------------------------------------------------------------- READY 13-APR-16 10.12.58.413 PM
再次执行看是否能使用自动并行度
SQL> set autotrace traceonly explain SQL> select /*+ parallel */ * from emp; Execution Plan ---------------------------------------------------------- Plan hash value: 2873591275 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM)| :TQ10000 | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | PX BLOCK ITERATOR | | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | PCWC | | | 4 | TABLE ACCESS FULL| EMP | 14 | 532 | 2 (0)| 00:00:01 | Q1,00 | PCWP | | -------------------------------------------------------------------------------------------------------------- Note ----- - automatic DOP: Computed Degree of Parallelism is 2
可以看到在收集IO Calibration统计信息后,执行计划使用自动并行度。
当使用自动并行度,可以还需要调整一些调整参数。parallel_servers_target参数应该总是比parallel_max_servers参数值小,parallel_servers_target总是处于parallel_max_servers的75%到50%。如果开始看到大量并行度下降,那么应该使用这两个参灵敏的差距增大。