os: centos 7.4
db: oracle 12.1.0.2

insert，update，delete，merge 的dml操作都可以開啟并行，多個會話同時執行，同時每個會話(并發進程)都有自己的undo段，都是獨立的一個事務，這些事務要么由pdml協調器進程提交，要么都rollback。

在一個有充足I/o帶寬的多cpu主機中，對于大規模的dml，速度會有很大的提升，尤其是在大型的數據倉庫環境中。

版本

# cat /etc/centos-release CentOS Linux release 7.4.1708 (Core) # # cat /proc/cpuinfo |grep -i "processor" processor : 0 processor : 1 processor : 2 processor : 3 processor : 4 processor : 5 processor : 6 processor : 7 # # su - oracle Last login: Thu Oct 24 22:30:17 CST 2019 on pts/0 $ sqlplus / as sysdba;SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 24 22:46:02 2019Copyright (c) 1982, 2014, Oracle. All rights reserved.Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> set lines 200; SQL> select * from v$version;BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0

準備表，插入200W條數據

SQL> create table tmp_t0 ( c0 varchar2(100),c1 varchar2(100),c2 varchar2(100)); create table tmp_t1 ( c0 varchar2(100),c1 varchar2(100),c2 varchar2(100));SQL> insert into tmp_t0 select level as le,level as le2,level as le3 from dual CONNECT BY level <=2000000; insert into tmp_t1 select level as le,level as le2,level as le3 from dual CONNECT BY level <=2000000; commit;

方式一：enable parallel dml + 添加 hint /*+ parallel */

并行dml需要顯示執行 enable

SQL> set lines 200; set pages 200; set timing on;SQL> set autotrace traceonly; SQL> SQL> update tmp_t0 set c2=c1||c1;2000000 rows updated.Elapsed: 00:01:23.10Execution Plan ---------------------------------------------------------- Plan hash value: 3238932164----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1616K| 240M| 3023 (1)| 00:00:01 | | 1 | UPDATE | TMP_T0 | | | | | | 2 | TABLE ACCESS FULL| TMP_T0 | 1616K| 240M| 3023 (1)| 00:00:01 | -----------------------------------------------------------------------------Note ------ dynamic statistics used: dynamic sampling (level=2)

啟用并行DML，需要在 update 后面同時加并行 hint /*+ parallel */

SQL> rollback; SQL> alter session enable parallel dml; SQL> SQL> update/*+ parallel */ tmp_t0 set c2=c1||c1; 2000000 rows updated.Elapsed: 00:00:59.47Execution Plan ---------------------------------------------------------- ERROR: ORA-12838: cannot read/modify an object after modifying it in parallelSP2-0612: Error generating AUTOTRACE EXPLAIN report

出現 ORA-12838 ，說明確實用到了 parallel dml

方式二：enable parallel dml + table parallel

并行dml需要顯示執行 enable

SQL> rollback; SQL> alter session enable parallel dml; SQL> alter table tmp_t0 parallel 8; SQL> update tmp_t0 set c2=c1||c1;

方式三：force parallel dml

并行dml需要顯示執行 enable

SQL> rollback; SQL> alter session force parallel dml parallel 8; SQL> update tmp_t0 set c2=c1||c1;

disable parallel dml

disable 并行 dml

SQL> alter session disable parallel dml; SQL> alter session force parallel dml parallel 1;

參考:

總結

以上是生活随笔為你收集整理的oracle 并行之二: parallel dml的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。