資料庫運維過程中，大家應該都有用到并行度，并行度有一個顯著特點就是可以最大限度的利用到多個CPU，在系統不忙或者是錯開高峰期的時候，應該是一個非常不錯的選擇，尤其是在OLAP系統，開啟并發是常事。下面我們一起讨論下，日常工作中，開啟并發需要注意的問題：

測試環境：

資料庫: oracle 12c (12.2.0.1)

作業系統：CentOS Linux release 7.3.1611 (Core)

并行DML無法生效

先來看查詢語句用并行度的情況，可以看出有利用到并行度，沒問題。

[oracle@hzdev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期二 11月 22 13:20:01 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

連接配接到: 
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show pdbs;

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 MCSDBPDB			  READ WRITE NO
SQL> alter session set container=mcsdbpdb;

會話已更改。

SQL>  create table parall_test as select * from dba_tables;

表已建立。

SQL> explain plan for select /*+parallel(a,4)*/ count(*) from parall_test a;

已解釋。
       
 SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3575377417

-----------------------------------------------------------------------------------------------------
| Id  | Operation	       | Name	     | Rows  | Cost (%CPU)| Time     |	  TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	     |	   1 |	   8   (0)| 00:00:01 |	      |      |		  |
|   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 |	     |	2072 |	   8   (0)| 00:00:01 |	Q1,00 | PCWC |		  |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|   6 |       TABLE ACCESS FULL| PARALL_TEST |	2072 |	   8   (0)| 00:00:01 |	Q1,00 | PCWP |		  |
----------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property

已選擇 18 行。

SQL>

接着看看DDL語句用并行度的情況，結果如下，有用到并行度。

SQL> explain plan for create table parall_test_2 parallel as select * from dba_tables;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 1665513533

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation						     | Name		| Rows	| Bytes | Cost (%CPU)| Time	|    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT					     |			|  2152 |  1952K|    88   (0)| 00:00:01 |	 |	|	     |
|   1 |  PX COORDINATOR 					     |			|	|	|	     |		|	 |	|	     |
|   2 |   PX SEND QC (RANDOM)					     | :TQ50001 	|  2162 |  1961K|    80   (0)| 00:00:01 |  Q5,01 | P->S | QC (RAND)  |
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)			     | PARALL_TEST_2	|	|	|	     |		|  Q5,01 | PCWP |	     |
|   4 |     PX RECEIVE						     |			|  2162 |  1961K|    80   (0)| 00:00:01 |  Q5,01 | PCWP |	     |
|   5 |      PX SEND ROUND-ROBIN				     | :TQ50000 	|  2162 |  1961K|    80   (0)| 00:00:01 |	 | S->P | RND-ROBIN  |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
|*  6 |       FILTER						     |			|	|	|	     |		|	 |	|	     |
|   7 |        PX COORDINATOR					     |			|	|	|	     |		|	 |	|	     |
|   8 | 	PX SEND QC (RANDOM)				     | :TQ40013 	|  2162 |  1961K|    80   (0)| 00:00:01 |  Q4,13 | P->S | QC (RAND)  |
|*  9 | 	 HASH JOIN RIGHT OUTER BUFFERED 		     |			|  2162 |  1961K|    80   (0)| 00:00:01 |  Q4,13 | PCWP |	     |
|  10 | 	  PX RECEIVE					     |			|   132 |  2376 |     2   (0)| 00:00:01 |  Q4,13 | PCWP |	     |
|  11 | 	   PX SEND BROADCAST				     | :TQ40010 	|   132 |  2376 |     2   (0)| 00:00:01 |  Q4,10 | P->P | BROADCAST  |
|  12 | 	    PX BLOCK ITERATOR				     |			|   132 |  2376 |     2   (0)| 00:00:01 |  Q4,10 | PCWC |	     |
|  13 | 	     TABLE ACCESS FULL				     | USER$		|   132 |  2376 |     2   (0)| 00:00:01 |  Q4,10 | PCWP |	     |
|* 14 | 	  HASH JOIN RIGHT OUTER 			     |			|  2162 |  1923K|    78   (0)| 00:00:01 |  Q4,13 | PCWP |	     |
....省略部分輸出
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  83 - filter("TYPE#"=:B1 AND "UE"."USER#"=:B2)
  87 - filter("UE"."TYPE#"=:B1 AND "UE"."USER#"=:B2)
  89 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
  90 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Note
-----
   - Degree of Parallelism is 16 because of table property

已選擇 130 行。

最後來看看DML更新語句用并行度的情況，發現用不到并行度

SQL> create table parall_test_2 as select * from dba_tables;

表已建立。

SQL> explain plan for insert /*+parallel(a,4)*/ into parall_test_2 a select * from parall_test  ;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 2059109207

------------------------------------------------------------------------------------------
| Id  | Operation		 | Name 	 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT	 |		 |  2072 |  2919K|    27   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | PARALL_TEST_2 |	 |	 |	      | 	 |
|   2 |   TABLE ACCESS FULL	 | PARALL_TEST	 |  2072 |  2919K|    27   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-----
   - dynamic statistics used: dynamic sampling (level=2)

已選擇 13 行。

SQL>

如果要使DML使用并行，怎麼實作？和DDL, select不同，DML要實作parallel, 需要在session 會話修改： alter session enable parallel dml;

SQL>  alter session enable parallel dml;

會話已更改。

SQL> explain plan for insert /*+parallel(a,4)*/ into parall_test_2 a select * from parall_test  ;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 549196191

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |	TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT		   |		   |  2072 |  2919K|	27   (0)| 00:00:01 |	    |	   |		|
|   1 |  PX COORDINATOR 		   |		   |	   |	   |		|	   |	    |	   |		|
|   2 |   PX SEND QC (RANDOM)		   | :TQ10001	   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)	|
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| PARALL_TEST_2 |	   |	   |		|	   |  Q1,01 | PCWP |		|
|   4 |     PX RECEIVE			   |		   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,01 | PCWP |		|
|   5 |      PX SEND ROUND-ROBIN	   | :TQ10000	   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,00 | S->P | RND-ROBIN	|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       PX SELECTOR		   |		   |	   |	   |		|	   |  Q1,00 | SCWC |		|
|   7 |        TABLE ACCESS FULL	   | PARALL_TEST   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,00 | SCWP |		|
---------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property

已選擇 19 行。

SQL>

另外一種情況，表本身設定了并行度，而不是用/*+parallel(a,4)*/之類 HINT，執行計劃是否也會顯示并行執行。這種情況和HINT是一樣的：非DML SQL語句可以直接用到并行度，DML語句，如果沒有加alter session enable parallel dml ，用不到并行度。

SQL> select degree from dba_tables where table_name='PARALL_TEST_2';

DEGREE
------------------------------------------------------------
	 4

  會話層關閉parallel DML:

SQL> alter session disable parallel dml;

會話已更改。

SQL> explain plan for insert /*+parallel(a,4)*/ into parall_test_2 a select * from parall_test  ;

已解釋。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2059109207

------------------------------------------------------------------------------------------
| Id  | Operation		 | Name 	 | Rows  | Bytes | Cost (%CPU)| Time	 |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT	 |		 |  2072 |  2919K|    27   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | PARALL_TEST_2 |	 |	 |	      | 	 |
|   2 |   TABLE ACCESS FULL	 | PARALL_TEST	 |  2072 |  2919K|    27   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic statistics used: dynamic sampling (level=2)

已選擇 13 行。

SQL> 
  
  會話層開啟parallel DML:

  SQL> alter session enable  parallel dml;

會話已更改。

SQL> explain plan for insert /*+parallel(a,4)*/ into parall_test_2 a select * from parall_test  ;

已解釋。

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 549196191

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation			   | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |	TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT		   |		   |  2072 |  2919K|	27   (0)| 00:00:01 |	    |	   |		|
|   1 |  PX COORDINATOR 		   |		   |	   |	   |		|	   |	    |	   |		|
|   2 |   PX SEND QC (RANDOM)		   | :TQ10001	   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,01 | P->S | QC (RAND)	|
|   3 |    LOAD AS SELECT (HYBRID TSM/HWMB)| PARALL_TEST_2 |	   |	   |		|	   |  Q1,01 | PCWP |		|
|   4 |     PX RECEIVE			   |		   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,01 | PCWP |		|
|   5 |      PX SEND ROUND-ROBIN	   | :TQ10000	   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,00 | S->P | RND-ROBIN	|

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
|   6 |       PX SELECTOR		   |		   |	   |	   |		|	   |  Q1,00 | SCWC |		|
|   7 |        TABLE ACCESS FULL	   | PARALL_TEST   |  2072 |  2919K|	27   (0)| 00:00:01 |  Q1,00 | SCWP |		|
---------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - Degree of Parallelism is 4 because of table property

已選擇 19 行。

SQL>

并行操作導緻表更新查詢失敗

繼續試驗，表開啟并行，執行插入操作失敗，提示ORA-12838

SQL> select degree from dba_tables where table_name='PARALL_TEST_2';

DEGREE
------------------------------------------------------------
	 4

SQL> insert into parall_test_2 a select * from parall_test  ;

已建立 2158 行。

SQL>  insert into parall_test_2 a select * from parall_test  ;
 insert into parall_test_2 a select * from parall_test
             *
第 1 行出現錯誤:
ORA-12838: 無法在并行模式下修改之後讀/修改對象

SQL> select count(*) from parall_test_2;
select count(*) from parall_test_2
                     *
第 1 行出現錯誤:
ORA-12838: 無法在并行模式下修改之後讀/修改對象

SQL> commit;

送出完成。

SQL> select count(*) from parall_test_2;

  COUNT(*)
----------
      4317

SQL>

如果表中隻是索引有并行度，會存在這樣的問題嗎，經過實驗得出結論，隻是表中索引有并行度，表不帶并行度，不會報錯。

SQL> alter table parall_test_2 noparallel;

表已更改。

SQL> drop index idx_object_id;

索引已删除。

SQL> create index  idx_table_name on parall_test_2(table_name);

索引已建立。

SQL> alter index idx_table_name parallel 4;

索引已更改。

SQL> select degree from  user_indexes  where table_name='PARALL_TEST_2';

DEGREE
------------------------------------------------------------------------------------------------------------------------
4

SQL> insert into PARALL_TEST_2 select * from PARALL_TEST;

已建立 2158 行。

SQL> commit;

送出完成。

SQL> alter session enable parallel dml;

會話已更改。

SQL>  insert into PARALL_TEST_2 select * from PARALL_TEST;

已建立 2158 行。

SQL>  insert into PARALL_TEST_2 select * from PARALL_TEST;

已建立 2158 行。

SQL> select count(*) from PARALL_TEST_2;

  COUNT(*)
----------
     12949

SQL>

結論

在并行操作中并行查詢和并行DDL操作是可以成功的，但是如果想讓并行DML能生效，需要執行： alter session enable parallel dml;
測試2可以得出，如果開啟并行的表更新未及時送出，在目前事務内，無論更新還是查詢都會失敗，是以表開啟了并行度，對業務存在潛在隐患。如果一定要開啟并行度，用HINT方式實作。

DBA日記之資料庫易錯問題彙集-并行執行陷阱

并行DML無法生效

并行操作導緻表更新查詢失敗

結論

繼續閱讀

蔬菜表情包圖檔第二波

「密碼學探秘」EVM鍊和并行執行交易

MySQL 原理與優化：Update 優化

一文了解 DeFi 原生公鍊 Sei Network：通過并行執行實作高速鍊

android AsyncTask 隻能線上程池裡單個運作的問題

【區塊鍊最新論文速遞】NeuChain: A Fast Permissioned Blockchain System with Deterministic Ordering

Orcle 12c 新特性---增強并行語句隊列

TCN 簡述

聊聊ShardingSphere的兩個Statement聊聊ShardingSphere的兩個Statement

【一庫】妙啊！這個庫組織npm腳本簡直爆炸！

業務線上化=數字化轉型？認知的偏狹是缺少慘痛教訓的表現...

iterator和spliterator差別

Spark綜合學習筆記（二）Spark核心原理

Spark學習之RDDs（一）

華為軟體開發平台 DevCloud 和流水線（CloudPipeline）使用彙總