使用oracle并行提示的效果测试与分析

闲来无事，对含有子查询的insert语句的并行效果进行测试，语句比较简单，是对2个表（p1,s1,p1为分区表）进行关联，关联结果分别插入到另外一张空表(t3,t4)。
以下是操作过程。
SQL> select count(*) from p1 partition (p201101);
  COUNT(*)
----------
  14054489
SQL> select max(tran_date) from p1 partition (p201101);
MAX(TRAN_DATE)
--------------
         40573
SQL> select min(tran_date) from p1 partition (p201101);
MIN(TRAN_DATE)
--------------
         40543
SQL> select COLUMN_NAME from dba_part_key_columns where owner='VICTOR' and name='P1';
COLUMN_NAME
--------------------------
TRAN_DATE
SQL> select count(*) from p1 where tran_date  between 40543 and 40573;
  COUNT(*)
----------
  14054489
SQL> select count(*) from s1;
  COUNT(*)
----------
   4374514
SQL> set timing on
SQL> set time on
12:34:53 SQL> alter session enable parallel dml;
Session altered.
12:35:00 SQL> insert /*+ parallel(t3,4) */ into t3 select s1.TRAN_DATE,s1.ACC1,s1.ACC_BAL,s1.PAPER_NO,p1.OPEN_INST1,p1.SUMM_NO from p1,s1 where p1.acc1=s1.acc1 and p1.tran_date between 40543 and 40573;
14048969 rows created.
Elapsed: 00:02:06.57
12:37:24 SQL> commit;
Commit complete.
13:37:28 SQL> conn / as sysdba
Connected.
13:37:31 SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:01.44
13:37:35 SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.60
13:37:45 SQL> conn victor/coohoo
Connected.
13:37:51 SQL> insert into t4 select s1.TRAN_DATE,s1.ACC1,s1.ACC_BAL,s1.PAPER_NO,p1.OPEN_INST1,p1.SUMM_NO from p1,s1 where p1.acc1=s1.acc1 and p1.tran_date between 40543 and 40573;
14048969 rows created.
Elapsed: 00:02:21.65
13:40:42 SQL>
alter system flush buffer_cache;
alter system flush shared_pool;
conn victor/coohoo
13:46:32 SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.01
13:46:39 SQL> insert /*+ parallel(t5,4) */ into t5 select /*+ parallel(p1,4) parallel(s1,4) */ s1.TRAN_DATE,s1.ACC1,s1.ACC_BAL,s1.PAPER_NO,p1.OPEN_INST1,p1.SUMM_NO from p1,s1 where p1.acc1=s1.acc1 and p1.tran_date between 40543 and 40573;
14048969 rows created.
Elapsed: 00:00:29.13
13:47:16 SQL> commit;
13:50:59 SQL> insert into t6 select /*+ parallel(p1,4) parallel(s1,4) */ s1.TRAN_DATE,s1.ACC1,s1.ACC_BAL,s1.PAPER_NO,p1.OPEN_INST1,p1.SUMM_NO from p1,s1 where p1.acc1=s1.acc1 and p1.tran_date between 40543 and 40573;
14048969 rows created.
Elapsed: 00:00:57.52
13:54:02 SQL> conn / as sysdba
Connected.
alter system flush buffer_cache;
alter system flush shared_pool;
13:55:20 SQL> conn victor/coohoo
Connected.
13:55:27 SQL> alter session enable parallel dml;
Session altered.
Elapsed: 00:00:00.00
13:55:34 SQL> insert /*+ parallel(t7,8) */ into t7 select /*+ parallel(p1,8) parallel(s1,8) */ s1.TRAN_DATE,s1.ACC1,s1.ACC_BAL,s1.PAPER_NO,p1.OPEN_INST1,p1.SUMM_NO from p1,s1 where p1.acc1=s1.acc1 and p1.tran_date between 40543 and 40573;
14048969 rows created.
Elapsed: 00:00:23.26

结论：对于含有子查询的insert语句，仅在insert后面使用并行提示(hint)基本不能提升性能，仅在select后面使用并行提示，性能提升能达到2倍以上，而同时在insert和select后面使用并行提示，性能提升能达到4倍以上，并行度8与并行度4的差别很小。
测试环境：
硬件：2台IBM X3850 X5+1套EMC CX3-40(450G*45)
软件：RHEL4.8+ORACLE 10.2.0.4 RAC