并行，想说爱你不容易
2021-01-20 10:52 AlfredZhao 阅读(393) 评论(0) 编辑 收藏 举报本文以Oracle数据库为例，说明在计算机的世界里，如果能用好并行这件利器，可以大幅提升性能；如果没用好，轻则达不到预期性能，重则会连带影响到整个系统的可用性，也正如本文标题所言：并行，想说爱你不容易。
下面，我们具体来看一些真实的测试场景，演示环境：Oracle RAC 11.2.0.4(3 nodes)。
1.并行insert无效果
测试用例：create table Z_OBJ tablespace TBS_1 as select * from dba_objects ;
insert /*+ append parallel(t0,16) */ into Z_OBJ t0 select /*+ parallel(t1,16) */ * from Z_OBJ t1;
commit;
--多次执行并查询大小
select owner,segment_name,bytes/1024/1024 from dba_segments where segment_name='Z_OBJ';
根据测试用例执行，发现实际并没有合理使用到并行度，效率很差（监控到I/O写入每秒只有百兆级别，正常应该是每秒千兆级别）。
查看执行计划：
SQL> explain plan for insert /*+ append parallel(t0,16) */ into Z_OBJ t0 select /*+ parallel(t1,16) */ * from Z_OBJ t1;
Explained.
SQL> set lines 1000 pages 200
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1886916412
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 91M| 17G| 23842 (1)| 00:00:01 | | | |
| 1 | LOAD AS SELECT | Z_OBJ | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 91M| 17G| 23842 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 4 | PX BLOCK ITERATOR | | 91M| 17G| 23842 (1)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| Z_OBJ | 91M| 17G| 23842 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
16 rows selected.
可以看到，只有查询部分用到了并行，insert部分并没有使用到并行，尽管我们指定了并行度的hint。
此时需要显示启用DML的并行：
alter session enable parallel dml;
再次查看执行计划，发现insert部分已经可以使用到并行：
SQL> explain plan for insert /*+ append parallel(t0,16) */ into Z_OBJ t0 select /*+ parallel(t1,16) */ * from Z_OBJ t1;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2135351304
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 91M| 17G| 23842 (1)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 91M| 17G| 23842 (1)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | Z_OBJ | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 91M| 17G| 23842 (1)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| Z_OBJ | 91M| 17G| 23842 (1)| 00:00:01 | Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
16 rows selected.
小结1：不仅仅是insert操作，其他DML操作的并行，都需要显示启用DML的并行：alter session enable parallel dml;
需要注意的是，虽然这里的并行DML测试性能提升的效果显著，但实际生产是需要慎重考虑是否使用并行DML的，因为要考虑TM锁的影响。之前就曾遇到过某客户在开启并行DML的同时，应用程序又大量并行调用，导致严重的TM锁等待，最终还是取消并行DML消除TM锁等待，反而提升了性能。
2.并行只在本地节点
默认情况下，并行操作会分发到RAC的各个节点，而很多生产数据库下，我们并不希望并行跨节点执行。此时就需要设置参数：alter system set parallel_force_local=true sid='*';
这样执行插入操作，在各个节点进行dstat监控，就会发现只有本地节点有每秒几百M的写入操作，说明parallel_force_local=true参数动态生效了：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 0 98 0 0 0| 163M 326M| 74k 61k| 0 0 | 17k 51k
2 0 98 0 0 0| 164M 325M| 479k 29k| 0 0 | 18k 51k
2 0 98 0 0 0| 165M 330M| 833k 1347k| 0 0 | 21k 54k
1 0 98 0 0 0| 167M 336M| 47k 58k| 0 0 | 18k 52k
1 0 98 0 0 0| 173M 340M| 507k 31k| 0 0 | 18k 53k
1 0 98 0 0 0| 176M 354M| 77k 546k| 0 0 | 18k 54k
1 0 98 0 0 0| 168M 341M| 43k 44k| 0 0 | 18k 53k
2 0 98 0 0 0| 177M 353M| 32k 42k| 0 0 | 18k 54k
2 0 98 0 0 0| 183M 362M| 65k 67k| 0 0 | 17k 54k
1 0 98 0 0 0| 163M 329M| 44k 44k| 0 0 | 16k 49k
1 0 98 0 0 0| 165M 328M| 39k 33k| 0 0 | 18k 51k
1 0 98 0 0 0| 161M 323M| 43k 56k| 0 0 | 17k 50k
2 0 98 0 0 0| 182M 360M| 44k 49k| 0 0 | 18k 55k
1 0 98 0 0 0| 166M 331M| 34k 52k| 0 0 | 18k 51k
2 0 98 0 0 0| 162M 327M| 25k 25k| 0 0 | 18k 51k
此时再结合1中的经验，启用dml的并行，可以发现效率大幅提升，本地节点有每秒几千M的写入操作：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
8 1 90 1 0 0|2927M 5882M| 771k 140k| 0 0 | 107k 157k
9 1 90 1 0 0|3134M 6266M| 759k 1484k| 0 0 | 108k 161k
8 1 90 1 0 0|3021M 6042M| 154k 178k| 0 0 | 104k 155k
9 1 90 0 0 0|3000M 6004M| 259k 266k| 0 0 | 106k 156k
9 1 90 0 0 0|2875M 5754M| 129k 142k| 0 0 | 102k 150k
9 1 90 0 0 0|3082M 6160M| 127k 135k| 0 0 | 108k 158k
9 1 90 0 0 0|3044M 6095M| 655k 642k| 0 0 | 107k 158k
9 1 89 0 0 0|2961M 5923M| 125k 134k| 0 0 | 105k 153k
9 1 90 0 0 0|2875M 5747M| 137k 168k| 0 0 | 102k 150k
9 1 90 0 0 0|3156M 6312M| 127k 135k| 0 0 | 109k 163k
9 1 90 1 0 0|3144M 6291M| 130k 138k| 0 0 | 109k 162k
9 1 90 1 0 0|3058M 6117M| 125k 143k| 0 0 | 106k 157k
9 1 90 0 0 0|3138M 6279M| 132k 139k| 0 0 | 108k 161k
9 1 90 0 0 0|3039M 6074M| 141k 143k| 0 0 | 106k 156k
4 1 95 0 0 0|1237M 2615M| 986k 61k| 0 0 | 68k 90k
小结2：可设置参数parallel_force_local=true强制让并行操作在本地节点执行，这是个动态参数：
3.增大并行度的效果
创建大表Z_OBJ_3，使用32个并行度插入数据：create table Z_OBJ_3 tablespace TBS_3 as select * from dba_objects ;
insert /*+ append parallel(t0,32) */ into Z_OBJ_3 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
commit;
实际花费25s的时间插入完成，并行度提升性能也进一步提升：
SQL> insert /*+ append parallel(t0,32) */ into Z_OBJ_3 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
867092478 rows created.
Elapsed: 00:00:25.52
此时dstat监控，每秒写操作达到8000M+：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 100 0 0 0|2489k 1036k| 0 0 | 0 0 | 10k 9766
13 1 83 2 0 0|3755M 7542M| 699k 1055k| 0 0 | 143k 210k
12 2 84 2 0 0|3634M 7407M| 447k 453k| 0 0 | 147k 209k
13 1 83 2 0 0|4202M 8402M| 535k 553k| 0 0 | 141k 215k
14 1 82 2 0 0|4168M 8339M| 539k 556k| 0 0 | 144k 214k
13 1 82 2 0 1|4109M 8224M| 546k 552k| 0 0 | 142k 210k
13 1 83 3 0 0|4209M 8419M| 311k 327k| 0 0 | 138k 213k
13 1 83 3 0 0|4237M 8483M| 114k 114k| 0 0 | 136k 210k
9 1 88 1 0 1|2709M 5703M| 64k 65k| 0 0 | 156k 203k
14 1 82 2 0 0|4189M 8383M| 91k 87k| 0 0 | 136k 205k
13 1 82 3 0 0|4237M 8478M| 95k 101k| 0 0 | 136k 208k
14 1 82 2 0 0|4242M 8485M| 95k 109k| 0 0 | 139k 208k
14 1 82 3 0 0|4202M 8412M| 835k 103k| 0 0 | 137k 208k
14 1 82 2 0 0|4288M 8563M|1143k 1930k| 0 0 | 139k 211k
14 1 82 2 0 0|4229M 8477M| 101k 97k| 0 0 | 138k 209k
再创建大表Z_OBJ_4，使用64个并行度插入数据：
create table Z_OBJ_4 tablespace TBS_4 as select * from dba_objects ;
insert /*+ append parallel(t0,64) */ into Z_OBJ_4 t0 select /*+ parallel(t1,64) */ * from Z_OBJ t1;
commit;
实际花费28s的时间插入完成，发现即使在CPU足够的前提下，并行度提升没有性能提升，说明I/O已达到瓶颈：
SQL> insert /*+ append parallel(t0,64) */ into Z_OBJ_4 t0 select /*+ parallel(t1,64) */ * from Z_OBJ t1;
867092478 rows created.
Elapsed: 00:00:28.61
此时dstat监控，每秒写操作接近8000M：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
14 2 81 4 0 1|3844M 7711M|3571k 2567k| 0 0 | 130k 197k
12 1 83 3 0 0|3810M 7602M| 535k 1885k| 0 0 | 115k 175k
13 1 82 3 0 0|3799M 7607M| 603k 654k| 0 0 | 116k 174k
14 1 82 3 0 0|3810M 7638M| 550k 602k| 0 0 | 119k 176k
13 1 83 3 0 0|3766M 7531M| 630k 651k| 0 0 | 114k 171k
13 1 81 4 0 0|3804M 7608M| 620k 669k| 0 0 | 117k 175k
13 1 82 3 0 0|3792M 7585M| 581k 616k| 0 0 | 117k 176k
13 1 82 3 0 0|3767M 7522M| 561k 612k| 0 0 | 116k 173k
12 1 82 3 0 0|3659M 7343M| 553k 601k| 0 0 | 115k 170k
13 1 82 3 0 0|3659M 7340M| 609k 668k| 0 0 | 121k 179k
13 1 82 3 0 0|3746M 7502M| 609k 644k| 0 0 | 117k 174k
13 1 82 3 0 0|3822M 7648M| 675k 773k| 0 0 | 118k 178k
13 1 83 3 0 0|3769M 7541M|1191k 632k| 0 0 | 115k 173k
13 1 83 3 0 0|3864M 7725M|1749k 2533k| 0 0 | 117k 177k
13 1 82 3 0 0|3741M 7481M| 613k 655k| 0 0 | 116k 172k
小结3：一般增大并行度可以提升操作返回速度，但同时也受限于整体的系统I/O能力。
4.所有节点并行测试
同时测试RAC的3个节点：--节点1
set time on
set timing on
drop table Z_OBJ_2 purge;
create table Z_OBJ_2 tablespace TBS_2 as select * from dba_objects where 1=2;
alter session enable parallel dml;
--INSERT Z_OBJ_2
insert /*+ append parallel(t0,32) */ into Z_OBJ_2 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
commit;
--节点2
set time on
set timing on
drop table Z_OBJ_3 purge;
create table Z_OBJ_3 tablespace TBS_3 as select * from dba_objects where 1=2;
alter session enable parallel dml;
--INSERT Z_OBJ_3
insert /*+ append parallel(t0,32) */ into Z_OBJ_3 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
commit;
--节点3
set time on
set timing on
drop table Z_OBJ_4 purge;
create table Z_OBJ_4 tablespace TBS_4 as select * from dba_objects where 1=2;
alter session enable parallel dml;
--INSERT Z_OBJ_4
insert /*+ append parallel(t0,32) */ into Z_OBJ_4 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
commit;
各节点同时观察插入耗时（单个执行时间变长，整体的I/O瓶颈导致）：
15:26:06 SQL> insert /*+ append parallel(t0,32) */ into Z_OBJ_2 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
867092478 rows created.
Elapsed: 00:00:48.53
15:25:23 SQL> insert /*+ append parallel(t0,32) */ into Z_OBJ_3 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
867092478 rows created.
Elapsed: 00:00:45.84
15:25:21 SQL> insert /*+ append parallel(t0,32) */ into Z_OBJ_4 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
867092478 rows created.
Elapsed: 00:00:47.63
各节点dstat同时观察：
--node1：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
7 1 82 9 0 0|2110M 4223M| 169k 230k| 0 0 | 78k 122k
7 1 82 9 0 0|2107M 4209M| 176k 178k| 0 0 | 79k 123k
9 1 81 9 0 0|2614M 5237M| 190k 195k| 0 0 | 96k 148k
8 1 81 10 0 0|2171M 4339M| 195k 232k| 0 0 | 84k 127k
7 1 83 9 0 0|1975M 3947M| 220k 184k| 0 0 | 76k 117k
7 1 82 9 0 0|2051M 4099M| 166k 169k| 0 0 | 78k 121k
7 1 82 10 0 0|2059M 4121M|1193k 170k| 0 0 | 79k 121k
7 1 83 9 0 0|2001M 4011M| 384k 1463k| 0 0 | 76k 118k
3 0 93 4 0 0| 802M 1570M| 148k 144k| 0 0 | 36k 53k
2 0 96 2 0 0| 355M 886M| 113k 137k| 0 0 | 47k 61k
8 1 82 9 0 0|2122M 4255M| 189k 202k| 0 0 | 79k 123k
7 1 83 9 0 0|2040M 4069M| 162k 164k| 0 0 | 76k 119k
8 1 82 9 0 0|2208M 4436M| 839k 843k| 0 0 | 83k 130k
9 1 83 7 0 0|2506M 5037M| 305k 307k| 0 0 | 94k 145k
4 0 93 2 0 0|1098M 2273M| 218k 233k| 0 0 | 49k 72k
--node2：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
6 1 82 11 0 0|2152M 4312M| 221k 224k| 0 0 | 79k 130k
7 1 82 10 0 0|2226M 4447M| 216k 218k| 0 0 | 81k 133k
10 1 81 8 0 0|2775M 5559M| 244k 214k| 0 0 | 100k 159k
7 1 83 9 0 0|2110M 4205M| 220k 221k| 0 0 | 77k 126k
7 1 83 10 0 0|2104M 4219M| 231k 266k| 0 0 | 76k 126k
7 1 83 10 0 0|2158M 4311M| 207k 207k| 0 0 | 78k 129k
7 1 83 10 0 0|2103M 4214M| 877k 849k| 0 0 | 76k 126k
7 1 82 10 0 0|2109M 4214M| 207k 209k| 0 0 | 76k 124k
10 1 81 8 0 0|2934M 5866M| 212k 216k| 0 0 | 102k 165k
7 1 82 10 0 0|2281M 4551M| 207k 227k| 0 0 | 82k 133k
7 1 83 10 0 0|2136M 4281M| 206k 205k| 0 0 | 79k 128k
6 1 84 10 0 0|1951M 3940M| 313k 341k| 0 0 | 73k 120k
4 0 92 4 0 0|1044M 2250M| 672k 642k| 0 0 | 56k 88k
0 0 99 0 0 0| 50M 116M| 258k 276k| 0 0 | 11k 14k
0 0 100 0 0 0| 323k 58k| 208k 202k| 0 0 |8385 10k
--node3：
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
6 1 83 10 0 0|2144M 4274M| 149k 156k| 0 0 | 77k 129k
6 1 82 11 0 0|2223M 4452M| 165k 189k| 0 0 | 80k 133k
6 1 82 11 0 0|2203M 4404M| 189k 198k| 0 0 | 79k 131k
7 0 83 10 0 0|2119M 4233M| 140k 211k| 0 0 | 75k 125k
7 1 83 10 0 0|2156M 4311M| 870k 731k| 0 0 | 78k 128k
7 1 82 10 0 0|2157M 4318M| 143k 149k| 0 0 | 79k 129k
7 1 83 9 0 0|2172M 4344M| 165k 170k| 0 0 | 79k 131k
7 1 83 10 0 0|2139M 4283M| 140k 141k| 0 0 | 78k 125k
7 1 83 10 0 0|2145M 4303M| 143k 151k| 0 0 | 78k 129k
7 1 83 10 0 0|2121M 4226M| 146k 450k| 0 0 | 76k 126k
7 1 82 10 0 0|2442M 4884M| 460k 155k| 0 0 | 87k 144k
6 0 83 10 0 0|2083M 4177M| 217k 156k| 0 0 | 76k 126k
4 0 88 7 0 0|1445M 2863M| 130k 126k| 0 0 | 54k 89k
2 0 94 3 0 0| 577M 1341M| 121k 124k| 0 0 | 53k 73k
7 1 82 10 0 0|2219M 4437M| 157k 193k| 0 0 | 81k 133k
测试到这里，还有一个疑惑，为什么不用create？我们来按测试用例试下create操作，很不如人意，只有300多M的写入速度，将近10分钟才创建完成。而上面的并行insert则有8000多M的写入速度，20s+就可以插入完成：
drop table Z_OBJ_2 purge;
create table Z_OBJ_2 tablespace TBS_2 as select /*+ parallel(t1,32) */ * from Z_OBJ t1;
Elapsed: 00:09:19.52
15:49:58 SQL> insert /*+ append parallel(t0,32) */ into Z_OBJ_2 t0 select /*+ parallel(t1,32) */ * from Z_OBJ t1;
867092478 rows created.
Elapsed: 00:00:25.24
很显然,create操作相当于没有用到并行，如何让create操作也用到并行度呢？这就需要将SQL语句改写如下：
--使用到并行，26s就完成了百G大小表的创建：
drop table Z_OBJ_2 purge;
create table Z_OBJ_2 tablespace TBS_2 parallel(degree 32) as select /*+ parallel(t1,32) */ * from Z_OBJ t1;
Elapsed: 00:00:26.76
--使用到并行+nologging，差距不大，只需25s就完成了百G大小表的创建：
drop table Z_OBJ_2 purge;
create table Z_OBJ_2 tablespace TBS_2 parallel(degree 32) nologging as select /*+ parallel(t1,32) */ * from Z_OBJ t1;
Elapsed: 00:00:25.77
小结4：我们在使用并行的时候，尤其要注意是否各部分都有效的使用到了并行。各节点同时并行操作的整体效率，同样受限于整体的系统I/O能力。
5.RMAN多通道的并行
现象：RMAN分配多个通道，但实际无法使用到并行。
构建测试用例：
create tablespace dbs_d_test;
alter tablespace dbs_d_test add datafile; --这里是11
alter tablespace dbs_d_test add datafile; --这里是12
alter tablespace dbs_d_test add datafile; --这里是13
alter database datafile 11,12,13 resize 1G;
5.1 RMAN多通道但未用到并行
使用RMAN备份run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as copy datafile 11 format '/tmp/incr/copy11.bak';
backup as copy datafile 12 format '/tmp/incr/copy12.bak';
backup as copy datafile 13 format '/tmp/incr/copy13.bak';
release channel c1;
release channel c2;
release channel c3;
}
使用下面SQL查询长操作：
select inst_id, sid, username, opname, target, sofar, totalwork, sofar * 100 / totalwork from gv$session_longops where sofar < totalwork;
发现上面这种备份写法，虽然分配了多个通道，但实际观察并没有使用到并行。3个文件的备份是串行操作的。这点从上面的长操作中可以看到，同时从RMAN输出日志中同样也可以看到：
RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> allocate channel c3 device type disk;
5>
6> backup as copy datafile 11 format '/tmp/incr/copy11.bak';
7> backup as copy datafile 12 format '/tmp/incr/copy12.bak';
8> backup as copy datafile 13 format '/tmp/incr/copy13.bak';
9>
10> release channel c1;
11> release channel c2;
12> release channel c3;
13> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=128 instance=jy1 devtype=DISK
allocated channel: c2
channel c2: sid=117 instance=jy1 devtype=DISK
allocated channel: c3
channel c3: sid=129 instance=jy1 devtype=DISK
Starting backup at 29-AUG-18
channel c1: starting datafile copy
input datafile fno=00011 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.615.985387387
output filename=/tmp/incr/copy11.bak tag=TAG20180829T002101 recid=13 stamp=985393279
channel c1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 29-AUG-18
Starting backup at 29-AUG-18
channel c1: starting datafile copy
input datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391
output filename=/tmp/incr/copy12.bak tag=TAG20180829T002127 recid=14 stamp=985393305
channel c1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 29-AUG-18
Starting backup at 29-AUG-18
channel c1: starting datafile copy
input datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395
output filename=/tmp/incr/copy13.bak tag=TAG20180829T002153 recid=15 stamp=985393330
channel c1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 29-AUG-18
released channel: c1
released channel: c2
released channel: c3
实际是串行操作，都是用的通道c1，这3个数据文件的copy备份消耗3个25s=75s。
5.2 备份语句改写使用到并行
改进写法，用到了并行：run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as copy datafile 11,12,13 format '/tmp/incr/copy_%u.bak';
release channel c1;
release channel c2;
release channel c3;
}
从日志看到：
RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> allocate channel c3 device type disk;
5>
6> backup as copy datafile 11,12,13 format '/tmp/incr/copy_%u.bak';
7>
8> release channel c1;
9> release channel c2;
10> release channel c3;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=129 instance=jy1 devtype=DISK
allocated channel: c2
channel c2: sid=127 instance=jy1 devtype=DISK
allocated channel: c3
channel c3: sid=119 instance=jy1 devtype=DISK
Starting backup at 29-AUG-18
channel c1: starting datafile copy
input datafile fno=00011 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.615.985387387
channel c2: starting datafile copy
input datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391
channel c3: starting datafile copy
input datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395
output filename=/tmp/incr/copy_14tbnq76.bak tag=TAG20180829T002302 recid=16 stamp=985393432
channel c1: datafile copy complete, elapsed time: 00:00:55
output filename=/tmp/incr/copy_15tbnq76.bak tag=TAG20180829T002302 recid=17 stamp=985393432
channel c2: datafile copy complete, elapsed time: 00:00:55
output filename=/tmp/incr/copy_16tbnq76.bak tag=TAG20180829T002302 recid=18 stamp=985393435
channel c3: datafile copy complete, elapsed time: 00:00:55
Finished backup at 29-AUG-18
released channel: c1
released channel: c2
released channel: c3
实际是并行操作，分别用的通道c1、c2、c3，这3个数据文件的copy备份消耗1个55s=55s。
那为什么并行没有成倍增加效率？跟上一篇提到的一样，系统的整体I/O能力达到瓶颈了。所以一味的增加并行度并不总是有意义的。
5.3 备份方式改变提高效率
如果数据文件很大，但实际使用的并不多，则可以考虑使用备份集的方式，减少备份对空间的占用，一般同时也会加快备份的速度：run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as compressed backupset datafile 11,12,13 format '/tmp/incr/datafile_%u.bak';
release channel c1;
release channel c2;
release channel c3;
}
从日志可以看到：
RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> allocate channel c3 device type disk;
5>
6> backup as compressed backupset datafile 11,12,13 format '/tmp/incr/datafile_%u.bak';
7>
8> release channel c1;
9> release channel c2;
10> release channel c3;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=128 instance=jy1 devtype=DISK
allocated channel: c2
channel c2: sid=134 instance=jy1 devtype=DISK
allocated channel: c3
channel c3: sid=116 instance=jy1 devtype=DISK
Starting backup at 29-AUG-18
channel c1: starting compressed full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00011 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.615.985387387
channel c1: starting piece 1 at 29-AUG-18
channel c2: starting compressed full datafile backupset
channel c2: specifying datafile(s) in backupset
input datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391
channel c2: starting piece 1 at 29-AUG-18
channel c3: starting compressed full datafile backupset
channel c3: specifying datafile(s) in backupset
input datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395
channel c3: starting piece 1 at 29-AUG-18
channel c1: finished piece 1 at 29-AUG-18
piece handle=/tmp/incr/datafile_17tbnqi9.bak tag=TAG20180829T002857 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c3: finished piece 1 at 29-AUG-18
piece handle=/tmp/incr/datafile_19tbnqia.bak tag=TAG20180829T002857 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:01
channel c2: finished piece 1 at 29-AUG-18
piece handle=/tmp/incr/datafile_18tbnqi9.bak tag=TAG20180829T002857 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:05
Finished backup at 29-AUG-18
released channel: c1
released channel: c2
released channel: c3
由于我这里这几个文件根本没有业务数据，所以效率提升尤为明显，只需要5s钟就完成了备份。
小结5：除了合理的并行使用，更要考虑当前是否有方案可以少做事，避免并行去做无用功，白白浪费计算资源。
关于并行，还有些有意思的场景，比如就曾遇到过有开发人员写错SQL并行度的hint导致oracle采用了自动DOP，即最大并行度执行，导致系统资源基本全被占用，进而其他操作无法高效运行导致性能故障。
看到这里，发现并行的使用的确是存在很多坑，但我们也不能因噎废食，只要认真掌握并行相关的知识点，就完全可以用好这把利器，使其在合适的场景下大放异彩，骄傲的说：“并行，想说爱你也容易。”
文中案例部分引用我之前blog中的文章：
AlfredZhao©版权所有「从Oracle起航，领略精彩的IT技术。」
