【DB笔试面试594】在Oracle中，关键字NOLOGGING、APPEND和PARALLEL提高DML性能方面有什么差别？...

2021/4/16 2:31:16

编程Tag： table select ... alter XXX parallel NOLOGGING 594

本文主要是介绍【DB笔试面试594】在Oracle中，关键字NOLOGGING、APPEND和PARALLEL提高DML性能方面有什么差别？...，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

640?wx_fmt=gif

♣题目部分

在Oracle中，关键字NOLOGGING、APPEND和PARALLEL提高DML性能方面有什么差别？

♣答案部分

众所周知，表模式（LOGGING/NOLOGGING）、插入模式（APPEND/NOAPPEND）、数据库运行模式（归档（ARCHIVELOG）/非归档（NOARCHIVELOG））和并行模式与DML操作的效率息息相关，作者就此设计了一个实验，用来检测它们不同组合生成的Redo量、Undo量和用时长短的比较，实验结果参考表 3-22，该表数据经过多次实验得到，表中重要数据用加粗来表示。

由于篇幅原因，有关实验用到的建表语句、存储过程等脚本在本书中不再列出，具体脚本可以去我的BLOG下载，下载地址为：http://blog.itpub.net/26736162/viewspace-2125815/。

表 3-22 表模式、插入模式、运行模式和并行模式的组合效率

序号	DDL/DML OPERATIONS TYPES	DDL/DML OPERATIONS	DIRECT-PATH	NOLOGGING	PARALLEL	ARCHIVELOG MODE	NOARCHIVELOG MODE
REDO	Undo	USE_TIME	REDO	Undo	USE_TIME
1	CTAS	CREATE TABLE XXX AS SELECT * FROM YYY	Y	N	N	666131564	40996	23.9	334788	42936	13.34
2	CREATE TABLE XXX NOLOGGING AS SELECT * FROM YYY	Y	Y	N	329404	41120	21.79	329272	41120	12.17
3	CREATE TABLE XXX NOLOGGING PARALLEL 4 AS SELECT * FROM YYY	Y	Y	Y	713236	157200	7.39	710340	156708	7.27
4	CI	CREATE INDEX XXX	N	N	N	101420764	21336	12.24	267116	20896	17.84
5	CREATE INDEX XXX NOLOGGING	N	Y	N	267744	20896	14.08	267048	20896	17.41
6	CREATE INDEX XXX NOLOGGING PARALLEL 4	N	Y	Y	475836	110576	5.62	475624	111352	5
7	MOVE	ALTER TABLE XXX MOVE;	N	N	N	651251072	36048	14.58	418756	36048	18.05
8	ALTER TABLE XXX MOVE NOLOGGING;	N	Y	N	352980	36092	12.35	358256	37848	13.09
9	ALTER TABLE XXX MOVE NOLOGGING PARALLEL 4;	N	Y	Y	661096	134760	5.06	654360	132800	4.29
10	INSERT	INSERT INTO XXX SELECT * FROM YYY	N	N	N	661223364	21352708	21.86	661245624	21353812	25.23
11	ALTER TABLE XXX NOLOGGING; INSERT INTO XXX SELECT * FROM YYY;	N	Y	N	647831988	21334768	60.64	647827568	21334984	54.89
12	INSERT /+ APPEND / INTO XXX SELECT * FROM YYY	Y	N	N	666203072	2132	17.68	142232	2132	12.54
13	ALTER TABLE XXX NOLOGGING; INSERT /+ APPEND / INTO XXX SELECT * FROM YYY	Y	Y	N	132080	80	20.82	132036	80	17.65
14	ALTER TABLE XXX NOLOGGING; INSERT /+ PARALLEL(4) APPEND / INTO XXX SELECT * FROM YYY	Y	Y	Y	131948	80	11.92	131948	80	10.4
15	ALTER TABLE XXX NOLOGGING; ALTER SESSION ENABLE PARALLEL DML; INSERT /+ PARALLEL(4) APPEND / INTO XXX SELECT * FROM YYY	Y	Y	Y(PDML)	131992	80	11.73	131904	80	11.43
16	UPDATE	UPDATE XXX SET	N	N	N	20188804	7494096	20.44	6108008	2910892	13.81
17	UPDATE /+ PARALLEL(4) / XXX SET	N	N	Y(Queries)	6109168	2911640	24.57	6120040	2914976	25.77
18	ALTER TABLE XXX NOLOGGING; UPDATE XXX SET	N	Y	N	20434668	7570448	20.61	20694012	7651184	21.5
19	ALTER TABLE XXX NOLOGGING; UPDATE /+ PARALLEL(4) / XXX SET	N	Y	Y(Queries)	22259628	8139204	27.82	6119332	2914676	26.36
20	ALTER SESSION ENABLE PARALLEL DML; UPDATE /+ PARALLEL(4) / XXX SET	N	N	Y(PDML)	21960940	8046532	30.48	19796852	7371352	27.88
21	ALTER TABLE XXX NOLOGGING; ALTER SESSION ENABLE PARALLEL DML; UPDATE /+ PARALLEL(4) / XXX SET	N	Y	Y(PDML)	22318520	8157968	29.63	6120048	2914972	26.99
22	MERGE	ALTER TABLE XXX NOLOGGING; MERGE INTO XXX T USING YYY	N	Y	N	15790172	5582028	24.56	15790084	5581788	23.33
23	ALTER TABLE XXX NOLOGGING; MERGE /+ PARALLEL(4) / INTO XXX T USING YYY	N	Y	Y(Queries)	15793248	5582028	6.86	15791808	5581612	8.37
24	ALTER TABLE XXX NOLOGGING; ALTER SESSION ENABLE PARALLEL DML; MERGE /+ PARALLEL(4) / INTO XXX T USING YYY	N	Y	Y(PDML)	15793004	5582020	6.84	15792800	5581876	8.31
25	DELETE	DELETE XXX;	N	N	N	23517296	14352556	13.39	23508340	14349412	19.57
26	DELETE /+PARALLEL(4) / XXX;	N	N	Y(Queries)	23517240	14352612	5.05	23507248	14348364	4.47
27	ALTER TABLE XXX NOLOGGING; DELETE FROM XXX;	N	Y	N	23513944	14350336	13.61	23504352	14346304	19.31
28	ALTER TABLE XXX NOLOGGING; DELETE /+PARALLEL(4) / FROM XXX;	N	Y	Y(Queries)	23517240	14352440	5.07	23508668	14349436	4.63
29	ALTER SESSION ENABLE PARALLEL DML; DELETE /+PARALLEL(4) / FROM XXX;	N	N	Y(PDML)	23517256	14352464	5.44	23508668	14349444	7.68
30	ALTER TABLE XXX NOLOGGING; ALTER SESSION ENABLE PARALLEL DML; DELETE /+PARALLEL(4) / FROM XXX;	N	Y	Y(PDML)	23513320	14349892	5.66	23504200	14346304	4.52

根据表 3-6的数据可以得到如下的结论：

（一）关于效率的结论：

1、 INSERT INTO：在有APPEND提示的情况下，只要NOLOGGING或NOARCHIVELOG满足一个条件即可产生少量的Redo和Undo。另外，PARALLEL默认是以DIRECT的方式进行加载数据的，一般在并行情况下SQL执行速度有所提高。

2、 CTAS：CTAS本身就是一种DIRECT的操作，归档模式和NOLOGGING模式组合可以产生少量Redo。在并行模式下执行时间大幅度减少，但生成的Redo和Undo成倍增长。

3、 ALTER TABLE ... MOVE：归档模式和NOLOGGING模式组合可以产生少量Redo。在并行模式下执行时间大幅度减少，但生成的Redo和Undo成倍增长。

4、 CREATE INDEX：归档模式和NOLOGGING模式组合可以产生少量Redo。在并行模式下执行时间大幅度减少，但生成的Redo和Undo成倍增长。

5、 UPDATE：任何组合都会生成大量Undo、大量Redo。有关并行的性能需要查询执行计划再做定夺。

6、 DELETE：任何组合都会生成大量Undo、大量Redo。加上并行可以大幅度提高SQL的执行速度。

7、 MERGE：在关联更新的情况下，MERGE语句的非关联形式的性能比UPDATE要高，若加上并行性能更好。

8、总体而言，非归档比归档模式下性能高。

（二）关于属性NOLOGGING和并行度的结论：

1、对于形如：“CREATE TABLE TT NOLOGGING PARALLEL 4 AS SELECT * FROM DBA_OBJECTS;”或“CREATE INDEX IDNX11 ON TT(OBJECT_ID) NOLOGGING PARALLEL 4;”的SQL语句而言，创建的表或索引的并行度是4，日志模式是NOLOGGING，所以，生产库上对于重要的表和索引需要修改为LOGGING，并行度可以根据需要来修改，SQL语句为：“ALTER TABLE TT LOGGING NOPARALLEL;”或“ALTER INDEX IDNX11 LOGGING NOPARALLEL;”。

2、对于形如：“ALTER TABLE TT MOVE NOLOGGING PARALLEL 4;”或“ALTER INDEX IDNX11 REBUILD NOLOGGING PARALLEL 4;”的SQL语句而言，执行后的表的并行度依然为原来的并行度，但是索引的并行度是4，而日志模式都是NOLOGGING。

总之，若执行了如上形式的SQL语句后，最好都修改一下表或索引的并行度及其日志模式。

（三）APPEND使用注意事项：

1、建议不要经常使用APPEND，这样会使表一直处于高水位之上，除非这个表只插入数据而不删除数据。

2、以APPEND方式插入记录后，要执行COMMIT，才能对表进行查询。否则会出现错误：ORA-12838: 无法在并行模式下修改之后读/修改对象。

3、APPEND对INSERT INTO ... VALUES语句不起作用，需要使用Oracle 11gR2的APPEND_VALUES来提示才可以直接路径加载，注意：APPEND_VALUES对INSERT INTO ... SELECT也起作用。

4、APPEND使用高水位之上的块，减少了搜索FREELIST上块的时间。

5、在归档模式下，NOLOGGING和APPEND组合才会显著减少Redo数量。在非归档模式下，单独APPEND即可减少Redo数量。需要注意的是，在NOLOGGING加APPEND组合操作后，需要对全表做备份，否则如果后期在执行了RESTORE加RECOVER操作后，数据库会报“ORA-26040”的错误。

6、APPEND不会减少相关表的索引上产生的Redo数量。

7、APPEND的插入操作在表上加6级排它锁，会阻塞表上的所有DML语句。

8、每提交一次，就会取一个新的BLOCK存放，高水位就上推一个BLOCK，若在LOOP循环中，外部循环100W次，但是每循环一次只有一行符合条件的数据插入，这样，大量单条/*+ APPEND*/插入，就会使得表空间急剧增大，除对INSERT本身造成性能影响之外，对以后的SELECT、UPDATE、DELETE更是带来更巨大的性能影响。

（四）NOLOGGING使用注意事项：

1、NOLOGGING方式插入数据后最好对表做个备份。生产上重要的表不建议设置NOLOGGING属性。

2、如果库处在FORCE LOGGING模式下，那么此时的NOLOGGING方式是无效的。

3、NOLOGGING模式下，只有在如下几种情况下数据库操作才不产成Redo记录：

l 索引的创建和ALTER（重建）。对于形如“CREATE INDEX IND_TEST_LHR_LOG ON TEST_LHR(ID) NOLOGGING;”的SQL语句创建的索引，不管表的日志是处在NOLOGGING还是LOGGING状态下，都会产生很少的Redo日志。

l 表的批量INSERT（通过提示使用“直接路径插入”，或采用SQL*Loader直接路径加载），表数据不生成Redo，但是所有索引修改会生成Redo。

l LOB操作（对大对象的更新不必生成日志）。

l 通过CREATE TABLE AS SELECT创建表。

l 各种ALTER TABLE操作，例如MOVE和SPLIT等。

4、事实上，在NOLOGGING模式下，还是会生成一定数量的Redo。这些Redo的作用是保护数据字典，这是不可避免的。

5、NOLOGGING不能避免所有后续操作不生成Redo。例如创建表（CREATE TABLE）这个操作没有生成日志，但是所有后续的增、删、改操作（例如INSERT、DELETE和UPDATE等）还是会生成Redo日志，其它特殊的操作（例如使用SQL*Loader的直接路径加载，或使用INSERT直接路径插入）不生成日志。不过，一般而言，应用对这个表执行的操作都会生成日志。

（五）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;”。示例如下：

1SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE 10); 2 3Table altered. 4 5SYS@orclasm >  SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH'; 6 7DEGREE 8-------------------- 9        101011SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);1213Table altered.1415SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';1617DEGREE18--------------------19   DEFAULT2021SYS@orclasm >  ALTER TABLE  SH.SALES NOPARALLEL;2223Table altered.2425SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';2627DEGREE28--------------------29         13031SYS@orclasm > CREATE TABLE SCOTT.AA AS SELECT * FROM DUAL;3233Table created.3435SYS@orclasm >  SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='AA' AND OWNER='SCOTT';3637DEGREE38--------------------39         1ALTER TABLE SH.SALES PARALLEL (DEGREE 10);

 3Table altered.

 5SYS@orclasm >  SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';

 7DEGREE
 8--------------------


11SYS@orclasm > ALTER TABLE SH.SALES PARALLEL (DEGREE DEFAULT);

13Table altered.

15SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';

17DEGREE
18--------------------
  DEFAULT

21SYS@orclasm >  ALTER TABLE  SH.SALES NOPARALLEL;

23Table altered.

25SYS@orclasm > SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='SALES' AND OWNER='SH';

27DEGREE
28--------------------


31SYS@orclasm > CREATE TABLE SCOTT.AA AS SELECT * FROM DUAL;

33Table created.

35SYS@orclasm >  SELECT DEGREE FROM DBA_TABLES WHERE TABLE_NAME='AA' AND OWNER='SCOTT';

37DEGREE
38--------------------

在日常使用上，一般不建议在对象级别定义并行度，因为这会导致相关对象的操作都变为并行处理，而并行处理会占用大量的CPU资源，导致数据库整体性能失控。一般在会话或语句级别进行处理。

1. 一些参数

和并行相关的参数较多，下面给出几个常见的参数，其它参数请参考官方文档：

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：开启自动并行度，语句排队和内存并行执行。

2. 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，则可以使用如下的存储过程来收集：

1SET SERVEROUTPUT ON 2DECLARE 3   lat INTEGER; 4   iops INTEGER; 5   mbps INTEGER; 6BEGIN 7    --DBMS_RESOURCE_MANAGER.CALIBRATE_IO(, ,iops, mbps, lat); 8    DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat); 9   DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);10   DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);11   dbms_output.put_line('max_mbps = ' || mbps);12END;13/SET SERVEROUTPUT ON
 2DECLARE
  lat INTEGER;
  iops INTEGER;
  mbps INTEGER;
 6BEGIN
   --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);
12END;
13/

注意，DBMS_RESOURCE_MANAGER.CALIBRATE_IO的前两个参数分别为num_disks和max_latency是输入变量，并且有三个输出变量。

num_disks：为了获得最精确的结果，最好提供数据库所使用的真实物理磁盘数。如果是使用ASM来管理数据库文件，那么就是指存储数据的磁盘组，那么只有存储数据的磁盘组中的物理磁盘作为num_disks变量值，不包含FRA磁盘组中的物理磁盘。

latency：对数据库块I/O操作允许的最大延迟。

1. 并行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

2. 并行DML操作（PDML）

Oracle可以对DML操作使用并行执行。如果要让DML操作使用并行执行，那么必须显式地在会话里执行如下命令：

1ALTER SESSION ENABLE PARALLEL DML;ALTER SESSION ENABLE PARALLEL DML;

只有执行了这个命令，Oracle才会对之后符合并行条件的DML操作并行执行，如果没有这个设定，那么即使SQL中指定了并行执行，Oracle也会忽略它。

以下给出一个并行UPDATE的示例：

1LHR@TEST> CREATE TABLE TB_LHR20160518 AS  SELECT * FROM DBA_OBJECTS; 2Table created. 3LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL')); 4PLAN_TABLE_OUTPUT 5----------------------------------------------------------------------------------- 6Plan hash value: 2194116729 7----------------------------------------------------------------------------- 8| Id  | Operation             | Name           |    TQ  |IN-OUT| PQ Distrib | 9-----------------------------------------------------------------------------10|   0 | UPDATE STATEMENT      |                |        |      |            |11|   1 |  UPDATE               | TB_LHR20160518 |        |      |            |12|   2 |   PX COORDINATOR      |                |        |      |            |13|   3 |    PX SEND QC (RANDOM)| :TQ10000       |  Q1,00 | P->S | QC (RAND)  |14|   4 |     PX BLOCK ITERATOR |                |  Q1,00 | PCWC |            |15|   5 |      TABLE ACCESS FULL| TB_LHR20160518 |  Q1,00 | PCWP |            |16-----------------------------------------------------------------------------1712 rows selected.18LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';19Explained.20LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));21PLAN_TABLE_OUTPUT22--------------------------------------------------------------------------------23Plan hash value: 219411672924-----------------------------------------------------------------------------25| Id  | Operation             | Name           |    TQ  |IN-OUT| PQ Distrib |26-----------------------------------------------------------------------------27|   0 | UPDATE STATEMENT      |                |        |      |            |28|   1 |  UPDATE               | TB_LHR20160518 |        |      |            |29|   2 |   PX COORDINATOR      |                |        |      |            |30|   3 |    PX SEND QC (RANDOM)| :TQ10000       |  Q1,00 | P->S | QC (RAND)  |31|   4 |     PX BLOCK ITERATOR |                |  Q1,00 | PCWC |            |32|   5 |      TABLE ACCESS FULL| TB_LHR20160518 |  Q1,00 | PCWP |            |33-----------------------------------------------------------------------------3412 rows selected.35LHR@test> ALTER SESSION ENABLE  PARALLEL DML;36Session altered.37LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';38Explained.39LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));40PLAN_TABLE_OUTPUT41----------------------------------------------------------------------------------42Plan hash value: 372970611643-----------------------------------------------------------------------------44| Id  | Operation             | Name           |    TQ  |IN-OUT| PQ Distrib |45-----------------------------------------------------------------------------46|   0 | UPDATE STATEMENT      |                |        |      |            |47|   1 |  PX COORDINATOR       |                |        |      |            |48|   2 |   PX SEND QC (RANDOM) | :TQ10000       |  Q1,00 | P->S | QC (RAND)  |49|   3 |    UPDATE             | TB_LHR20160518 |  Q1,00 | PCWP |            |50|   4 |     PX BLOCK ITERATOR |                |  Q1,00 | PCWC |            |51|   5 |      TABLE ACCESS FULL| TB_LHR20160518 |  Q1,00 | PCWP |            |52-----------------------------------------------------------------------------5312 rows selected.CREATE TABLE TB_LHR20160518 AS  SELECT * FROM DBA_OBJECTS;
 2Table created.
 3LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
 4PLAN_TABLE_OUTPUT
 5-----------------------------------------------------------------------------------
 6Plan hash value: 2194116729
 7-----------------------------------------------------------------------------
 8| Id  | Operation             | Name           |    TQ  |IN-OUT| PQ Distrib |
 9-----------------------------------------------------------------------------
10|   0 | UPDATE STATEMENT      |                |        |      |            |
11|   1 |  UPDATE               | TB_LHR20160518 |        |      |            |
12|   2 |   PX COORDINATOR      |                |        |      |            |
13|   3 |    PX SEND QC (RANDOM)| :TQ10000       |  Q1,00 | P->S | QC (RAND)  |
14|   4 |     PX BLOCK ITERATOR |                |  Q1,00 | PCWC |            |
15|   5 |      TABLE ACCESS FULL| TB_LHR20160518 |  Q1,00 | PCWP |            |
16-----------------------------------------------------------------------------
rows selected.
18LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';
19Explained.
20LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
21PLAN_TABLE_OUTPUT
22--------------------------------------------------------------------------------
23Plan hash value: 2194116729
24-----------------------------------------------------------------------------
25| Id  | Operation             | Name           |    TQ  |IN-OUT| PQ Distrib |
26-----------------------------------------------------------------------------
27|   0 | UPDATE STATEMENT      |                |        |      |            |
28|   1 |  UPDATE               | TB_LHR20160518 |        |      |            |
29|   2 |   PX COORDINATOR      |                |        |      |            |
30|   3 |    PX SEND QC (RANDOM)| :TQ10000       |  Q1,00 | P->S | QC (RAND)  |
31|   4 |     PX BLOCK ITERATOR |                |  Q1,00 | PCWC |            |
32|   5 |      TABLE ACCESS FULL| TB_LHR20160518 |  Q1,00 | PCWP |            |
33-----------------------------------------------------------------------------
rows selected.
35LHR@test> ALTER SESSION ENABLE  PARALLEL DML;
36Session altered.
37LHR@TEST> EXPLAIN PLAN FOR UPDATE /*+ PARALLEL (T1,4) */ TB_LHR20160518 T1 SET OBJECT_NAME='LHR';
38Explained.
39LHR@TEST> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +PARALLEL'));
40PLAN_TABLE_OUTPUT
41----------------------------------------------------------------------------------
42Plan hash value: 3729706116
43-----------------------------------------------------------------------------
44| Id  | Operation             | Name           |    TQ  |IN-OUT| PQ Distrib |
45-----------------------------------------------------------------------------
46|   0 | UPDATE STATEMENT      |                |        |      |            |
47|   1 |  PX COORDINATOR       |                |        |      |            |
48|   2 |   PX SEND QC (RANDOM) | :TQ10000       |  Q1,00 | P->S | QC (RAND)  |
49|   3 |    UPDATE             | TB_LHR20160518 |  Q1,00 | PCWP |            |
50|   4 |     PX BLOCK ITERATOR |                |  Q1,00 | PCWC |            |
51|   5 |      TABLE ACCESS FULL| TB_LHR20160518 |  Q1,00 | PCWP |            |
52-----------------------------------------------------------------------------
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可以参考【3.2.16 RAC维护】。

这是一个Oracle 11g的RAC环境，下面建立一张测试表，建立过程中设置表的并行度：

1[ZFWWLHRDB1:oracle]:/oracle>ORACLE_SID=raclhr1 2[ZFWWLHRDB1:oracle]:/oracle>sqlplus / as sysdba 3SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 14:52:23 2016 4Copyright (c) 1982, 2013, Oracle.  All rights reserved. 5Connected to: 6Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 7With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, 8Data Mining and Real Application Testing options 9SYS@raclhr1> show parameter cluster10NAME                                 TYPE        VALUE11------------------------------------ ----------- ------------------------------12cluster_database                     boolean     TRUE13cluster_database_instances           integer     214cluster_interconnects                string15SYS@raclhr1> CREATE TABLE T_PARALLEL_LHR NOLOGGING PARALLEL 4 16  2   AS SELECT A.* FROM DBA_OBJECTS A, DBA_TABLES 17  3   WHERE ROWNUM <= 5000000;18Table created.19SYS@raclhr1> SELECT * FROM V$MYSTAT WHERE ROWNUM<=1;20       SID STATISTIC#      VALUE21---------- ---------- ----------22       167          0          023SYS@raclhr1> set autot on24SYS@raclhr1> SET LINESIZE 999925SYS@raclhr1> SET PAGESIZE 999926SYS@raclhr1> SELECT COUNT(*) FROM T_PARALLEL_LHR a,T_PARALLEL_LHR b where rownum<=1000000;27  COUNT(*)28----------29   100000030Execution Plan31----------------------------------------------------------32Plan hash value: 169178801333-------------------------------------------------------------------------------------------------------------------34| Id  | Operation                   | Name           | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |35-------------------------------------------------------------------------------------------------------------------36|   0 | SELECT STATEMENT            |                |     1 |  2057M  (5)|999:59:59 |        |      |            |37|   1 |  SORT AGGREGATE             |                |     1 |            |          |        |      |            |38|*  2 |   COUNT STOPKEY             |                |       |            |          |        |      |            |39|   3 |    PX COORDINATOR           |                |       |            |          |        |      |            |40|   4 |     PX SEND QC (RANDOM)     | :TQ10001       |    23T|  2057M  (5)|999:59:59 |  Q1,01 | P->S | QC (RAND)  |41|*  5 |      COUNT STOPKEY          |                |       |            |          |  Q1,01 | PCWC |            |42|   6 |       MERGE JOIN CARTESIAN  |                |    23T|  2057M  (5)|999:59:59 |  Q1,01 | PCWP |            |43|   7 |        PX BLOCK ITERATOR    |                |  4857K|  5396   (1)| 00:01:05 |  Q1,01 | PCWC |            |44|   8 |         TABLE ACCESS FULL   | T_PARALLEL_LHR |  4857K|  5396   (1)| 00:01:05 |  Q1,01 | PCWP |            |45|   9 |        BUFFER SORT          |                |  4857K|  2057M  (5)|999:59:59 |  Q1,01 | PCWP |            |46|  10 |         PX RECEIVE          |                |  4857K|  5396   (1)| 00:01:05 |  Q1,01 | PCWP |            |47|  11 |          PX SEND BROADCAST  | :TQ10000       |  4857K|  5396   (1)| 00:01:05 |  Q1,00 | P->P | BROADCAST  |48|  12 |           PX BLOCK ITERATOR |                |  4857K|  5396   (1)| 00:01:05 |  Q1,00 | PCWC |            |49|  13 |            TABLE ACCESS FULL| T_PARALLEL_LHR |  4857K|  5396   (1)| 00:01:05 |  Q1,00 | PCWP |            |50-------------------------------------------------------------------------------------------------------------------51Predicate Information (identified by operation id):52---------------------------------------------------53   2 - filter(ROWNUM<=1000000)54   5 - filter(ROWNUM<=1000000)55Note56-----57   - dynamic sampling used for this statement (level=4)58Statistics59----------------------------------------------------------60        112  recursive calls61          8  db block gets62      72078  consistent gets63      74257  physical reads64          0  redo size65        526  bytes sent via SQL*Net to client66        520  bytes received via SQL*Net from client67          2  SQL*Net roundtrips to/from client68          0  sorts (memory)69          4  sorts (disk)70          1  rows processed
 2[ZFWWLHRDB1:oracle]:/oracle>sqlplus / as sysdba
 3SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 30 14:52:23 2016
 4Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 5Connected to:
 6Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
 7With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
 8Data Mining and Real Application Testing options
 9SYS@raclhr1> show parameter cluster
10NAME                                 TYPE        VALUE
11------------------------------------ ----------- ------------------------------
12cluster_database                     boolean     TRUE
13cluster_database_instances           integer     2
14cluster_interconnects                string
15SYS@raclhr1> CREATE TABLE T_PARALLEL_LHR NOLOGGING PARALLEL 4 
  AS SELECT A.* FROM DBA_OBJECTS A, DBA_TABLES 
  WHERE ROWNUM <= 5000000;
18Table created.
19SYS@raclhr1> SELECT * FROM V$MYSTAT WHERE ROWNUM<=1;
      SID STATISTIC#      VALUE
21---------- ---------- ----------
         0          0
23SYS@raclhr1> set autot on
24SYS@raclhr1> SET LINESIZE 9999
25SYS@raclhr1> SET PAGESIZE 9999
26SYS@raclhr1> SELECT COUNT(*) FROM T_PARALLEL_LHR a,T_PARALLEL_LHR b where rownum<=1000000;
 COUNT(*)
28----------

30Execution Plan
31----------------------------------------------------------
32Plan hash value: 1691788013
33-------------------------------------------------------------------------------------------------------------------
34| Id  | Operation                   | Name           | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
35-------------------------------------------------------------------------------------------------------------------
36|   0 | SELECT STATEMENT            |                |     1 |  2057M  (5)|999:59:59 |        |      |            |
37|   1 |  SORT AGGREGATE             |                |     1 |            |          |        |      |            |
38|*  2 |   COUNT STOPKEY             |                |       |            |          |        |      |            |
39|   3 |    PX COORDINATOR           |                |       |            |          |        |      |            |
40|   4 |     PX SEND QC (RANDOM)     | :TQ10001       |    23T|  2057M  (5)|999:59:59 |  Q1,01 | P->S | QC (RAND)  |
41|*  5 |      COUNT STOPKEY          |                |       |            |          |  Q1,01 | PCWC |            |
42|   6 |       MERGE JOIN CARTESIAN  |                |    23T|  2057M  (5)|999:59:59 |  Q1,01 | PCWP |            |
43|   7 |        PX BLOCK ITERATOR    |                |  4857K|  5396   (1)| 00:01:05 |  Q1,01 | PCWC |            |
44|   8 |         TABLE ACCESS FULL   | T_PARALLEL_LHR |  4857K|  5396   (1)| 00:01:05 |  Q1,01 | PCWP |            |
45|   9 |        BUFFER SORT          |                |  4857K|  2057M  (5)|999:59:59 |  Q1,01 | PCWP |            |
46|  10 |         PX RECEIVE          |                |  4857K|  5396   (1)| 00:01:05 |  Q1,01 | PCWP |            |
47|  11 |          PX SEND BROADCAST  | :TQ10000       |  4857K|  5396   (1)| 00:01:05 |  Q1,00 | P->P | BROADCAST  |
48|  12 |           PX BLOCK ITERATOR |                |  4857K|  5396   (1)| 00:01:05 |  Q1,00 | PCWC |            |
49|  13 |            TABLE ACCESS FULL| T_PARALLEL_LHR |  4857K|  5396   (1)| 00:01:05 |  Q1,00 | PCWP |            |
50-------------------------------------------------------------------------------------------------------------------
51Predicate Information (identified by operation id):
52---------------------------------------------------
- filter(ROWNUM<=1000000)
- filter(ROWNUM<=1000000)
55Note
56-----
  - dynamic sampling used for this statement (level=4)
58Statistics
59----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed

从执行计划可以看到，Oracle选择了并行执行。

新建立一个会话，在执行上面这个并行查询的同时查询GV$PX_SESSION（或GV$PX_PROCESS）视图：

1SYS@raclhr1> SELECT * FROM GV$PX_SESSION WHERE QCSID=167; 2   INST_ID SADDR                   SID    SERIAL#      QCSID  QCSERIAL#  QCINST_ID SERVER_GROUP SERVER_SET    SERVER#     DEGREE REQ_DEGREE 3---------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- 4         1 07000100538364A0        199         35        167          5          1            1          1          1          4          4 5         1 0700010053894FC0        230         35        167          5          1            1          1          2          4          4 6         1 0700010053607480         10         37        167          5          1            1          2          1          4          4 7         1 070001005366F240         38          3        167          5          1            1          2          2          4          4 8         1 07000100537DAA60        167          5        167 9         2 070001005383F740        196         43        167          5          1            1          1          3          4          410         2 07000100536D3F20         67          9        167          5          1            1          1          4          4          411         2 07000100536168E0          5          5        167          5          1            1          2          3          4          412         2 07000100536784E0         35        113        167          5          1            1          2          4          4          4139 rows selected.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
 3---------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------
07000100538364A0        199         35        167          5          1            1          1          1          4          4
0700010053894FC0        230         35        167          5          1            1          1          2          4          4
0700010053607480         10         37        167          5          1            1          2          1          4          4
070001005366F240         38          3        167          5          1            1          2          2          4          4
07000100537DAA60        167          5        167
070001005383F740        196         43        167          5          1            1          1          3          4          4
07000100536D3F20         67          9        167          5          1            1          1          4          4          4
07000100536168E0          5          5        167          5          1            1          2          3          4          4
07000100536784E0         35        113        167          5          1            1          2          4          4          4
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/