在数据泵导入时默认情况下创建索引是不使用并行的，所以这一步会消耗较长的时间，除了人工写脚本并行创建索引外，数据泵在导入时也可以并行创建索引，本文详细介绍了整个测试过程并得出测试结论。

测试环境为11204单实例

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

首先创建测试表和测试索引：

SQL> create table a_impdp_test as select * from dba_objects;

Table created.
SQL> insert into a_impdp_test select * from a_impdp_test;

SQL> select owner,segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024 from dba_segments where segment_name=upper('a_impdp_test') group by owner,segment_name,segment_type,tablespace_name

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ ------------------------------ ------------------ ------------------------------ --------------------
AWEN A_IMPDP_TEST TABLE USERS 24

SQL> create index ind_ait_objid on A_IMPDP_TEST(object_id) ;

Index created.

然后并行导出，在导入时指定parallel等于4并设置sqlfile参数，查看生成的sql文本：

[oracle@localhost dump]$ expdp awen/oracle dumpfile=ait_%U.dmp logfile=1.log parallel=4 tables=A_IMPDP_TEST

Export: Release 11.2.0.4.0 - Production on Sun Mar 27 16:16:51 2016

[oracle@localhost dump]$ impdp awen/oracle dumpfile=ait_%U.dmp logfile=2.log parallel=4 tables=A_IMPDP_TEST sqlfile=impsql.log

Import: Release 11.2.0.4.0 - Production on Sun Mar 27 16:18:19 2016

发现sql文本中并未使用并行：

-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "AWEN"."IND_AIT_OBJID" ON "AWEN"."A_IMPDP_TEST" ("OBJECT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "AWEN"."IND_AIT_OBJID" NOPARALLEL;

接下来为了更直接的看出整个过程先开启系统级别的10046事件，再指定parallel等于4导入数据

SQL> alter system set events '10046 trace name context forever,level 4';

System altered.

[oracle@localhost log]$ impdp awen/oracle dumpfile=ait_%U.dmp logfile=2.log parallel=4 tables=A_IMPDP_TEST

Import: Release 11.2.0.4.0 - Production on Sun Mar 27 16:53:12 2016

此时，在v$pq_slave视图中已经可以查看到并行进程，且并行度为8，并且状态为BUSY，几秒钟之后变成IDLE：

SQL> /

SLAV STAT SESSIONS
---- ---- ----------
P000 BUSY 3
P001 BUSY 3
P002 BUSY 2
P003 BUSY 2
P004 BUSY 2
P005 BUSY 2
P006 BUSY 2
P007 BUSY 2

8 rows selected.

SQL> /

SLAV STAT SESSIONS
---- ---- ----------
P000 IDLE 3
P001 IDLE 3
P002 IDLE 2
P003 IDLE 2
P004 IDLE 2
P005 IDLE 2
P006 IDLE 2
P007 IDLE 2

8 rows selected.

同时会产生8个并行进程的trace文件：

-rw-r----- 1 oracle dba 62 Mar 27 16:40 orasql_m001_3173.trm
-rw-r----- 1 oracle dba 1149 Mar 27 16:40 orasql_m001_3173.trc
-rw-r----- 1 oracle dba 485 Mar 27 16:53 orasql_m000_3210.trm
-rw-r----- 1 oracle dba 24404 Mar 27 16:53 orasql_m000_3210.trc
-rw-r----- 1 oracle dba 83 Mar 27 16:53 orasql_p007_3233.trm
-rw-r----- 1 oracle dba 2628 Mar 27 16:53 orasql_p007_3233.trc
-rw-r----- 1 oracle dba 83 Mar 27 16:53 orasql_p006_3231.trm
-rw-r----- 1 oracle dba 2637 Mar 27 16:53 orasql_p006_3231.trc
-rw-r----- 1 oracle dba 83 Mar 27 16:53 orasql_p005_3229.trm
-rw-r----- 1 oracle dba 2629 Mar 27 16:53 orasql_p005_3229.trc
-rw-r----- 1 oracle dba 83 Mar 27 16:53 orasql_p004_3227.trm
-rw-r----- 1 oracle dba 2632 Mar 27 16:53 orasql_p004_3227.trc
-rw-r----- 1 oracle dba 1710 Mar 27 16:53 orasql_p003_3225.trm
-rw-r----- 1 oracle dba 21671 Mar 27 16:53 orasql_p003_3225.trc
-rw-r----- 1 oracle dba 1265 Mar 27 16:53 orasql_p002_3223.trm
-rw-r----- 1 oracle dba 16820 Mar 27 16:53 orasql_p002_3223.trc
-rw-r----- 1 oracle dba 2376 Mar 27 16:53 orasql_p001_3221.trm
-rw-r----- 1 oracle dba 24073 Mar 27 16:53 orasql_p001_3221.trc
-rw-r----- 1 oracle dba 2585 Mar 27 16:53 orasql_p000_3219.trm
-rw-r----- 1 oracle dba 48988 Mar 27 16:53 orasql_p000_3219.trc
-rw-r----- 1 oracle dba 15685 Mar 27 16:53 orasql_dw00_3216.trm
-rw-r----- 1 oracle dba 4545067 Mar 27 16:53 orasql_dw00_3216.trc
-rw-r----- 1 oracle dba 502 Mar 27 16:53 orasql_q001_2905.trm
-rw-r----- 1 oracle dba 202594 Mar 27 16:53 orasql_q001_2905.trc
-rw-r----- 1 oracle dba 686 Mar 27 16:53 orasql_q000_2903.trm
-rw-r----- 1 oracle dba 252833 Mar 27 16:53 orasql_q000_2903.trc
-rw-r----- 1 oracle dba 14721 Mar 27 16:53 orasql_ora_3208.trm
-rw-r----- 1 oracle dba 5983504 Mar 27 16:53 orasql_ora_3208.trc
-rw-r----- 1 oracle dba 13739 Mar 27 16:53 orasql_dm00_3214.trm
-rw-r----- 1 oracle dba 5541435 Mar 27 16:53 orasql_dm00_3214.trc
-rw-r----- 1 oracle dba 2515 Mar 27 16:54 orasql_cs02_2882.trm
-rw-r----- 1 oracle dba 666160 Mar 27 16:54 orasql_cs02_2882.trc
-rw-r----- 1 oracle dba 1505 Mar 27 16:54 orasql_ora_3018.trm
-rw-r----- 1 oracle dba 819189 Mar 27 16:54 orasql_ora_3018.trc

查看dbwn进程的trace文件，显示创建索引时已经指定parallel为4：

orasql_dw00_3216.trc
=====================
PARSING IN CURSOR #3076930692 len=321 dep=2 uid=32 oct=9 lid=32 tim=1459068805029611 hv=3884167576 ad='45178ebc' sqlid='c7p05ngms7ccs'
CREATE INDEX "AWEN"."IND_AIT_OBJID" ON "AWEN"."A_IMPDP_TEST" ("OBJECT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 4
END OF STMT
PARSE #3076930692:c=2999,e=4013,p=0,cr=13,cu=1,mis=1,r=0,dep=2,og=3,plh=166871857,tim=1459068805029608

如下是p004进程的详细内容：

orasql_p004_3227.trc
=====================
Trace file /oracle/diag/rdbms/orasql/orasql/trace/orasql_p004_3227.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u02/product/database
System name: Linux
Node name: localhost.localdomain
Release: 2.6.18-164.el5
Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine: i686
Instance name: orasql
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 3227, image: oracle@localhost.localdomain (P004)

*** 2016-03-27 16:53:25.236
*** SESSION ID:(146.195) 2016-03-27 16:53:25.236
*** CLIENT ID:() 2016-03-27 16:53:25.236
*** SERVICE NAME:(SYS$BACKGROUND) 2016-03-27 16:53:25.236
*** MODULE NAME:(Data Pump Worker) 2016-03-27 16:53:25.236
*** ACTION NAME:(SYS_IMPORT_TABLE_01) 2016-03-27 16:53:25.236

=====================
PARSING IN CURSOR #3084547884 len=321 dep=1 uid=32 oct=9 lid=32 tim=1459068805223411 hv=3884167576 ad='45178ebc' sqlid='c7p05ngms7ccs'
CREATE INDEX "AWEN"."IND_AIT_OBJID" ON "AWEN"."A_IMPDP_TEST" ("OBJECT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 4
END OF STMT
PARSE #3084547884:c=999,e=209,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=166871857,tim=1459068805223407
XCTEND rlbk=0, rd_only=1, tim=1459068805374016
EXEC #3084547884:c=26996,e=137209,p=563,cr=568,cu=0,mis=0,r=0,dep=1,og=3,plh=166871857,tim=1459068805374060

*** 2016-03-27 16:53:27.062
STAT #3084547884 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #3084547884 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)'
STAT #3084547884 id=3 cnt=0 pid=2 pos=1 obj=0 op='INDEX BUILD NON UNIQUE IND_AIT_OBJID (cr=0 pr=0 pw=0 time=0 us)'
STAT #3084547884 id=4 cnt=0 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=0 us)'
STAT #3084547884 id=5 cnt=0 pid=4 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0 pw=0 time=0 us cost=2 size=26000 card=2000)'
STAT #3084547884 id=6 cnt=0 pid=5 pos=1 obj=0 op='PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=2 size=26000 card=2000)'
STAT #3084547884 id=7 cnt=41432 pid=6 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=568 pr=563 pw=0 time=295170 us cost=2 size=26000 card=2000)'
STAT #3084547884 id=8 cnt=41432 pid=7 pos=1 obj=18947 op='TABLE ACCESS FULL A_IMPDP_TEST (cr=568 pr=563 pw=0 time=79812 us cost=2 size=26000 card=2000)'
CLOSE #3084547884:c=0,e=198,dep=1,type=0,tim=1459068807062579

从trace文件中可以看出在并行创建索引时，产生了2个query slave set，分别是Q10000和Q10001，这时并行度就会乘于2变为8，从执行计划id=8和id=4中分别可以看出，一组用来扫描表，一组用来创建索引。
这也就是为什么我们指定parallel为4，而实际并行度为8的原因。

最后要关掉系统级别的10046事件，不然会产生非常多的trace文件：

SQL> alter system set events '10046 trace name context off';

System altered.

测试导入时指定parallel等于1

[oracle@localhost log]$ impdp awen/oracle dumpfile=ait_%U.dmp logfile=2.log parallel=1 tables=A_IMPDP_TEST

Import: Release 11.2.0.4.0 - Production on Sun Mar 27 17:11:15 2016

可以看出数据库未生成并行的trace文件，也就是未并行创建索引：

-rw-r----- 1 oracle dba 516 Mar 27 17:10 orasql_mmon_2856.trm
-rw-r----- 1 oracle dba 102588 Mar 27 17:10 orasql_mmon_2856.trc
-rw-r----- 1 oracle dba 711 Mar 27 17:11 orasql_ora_3320.trm
-rw-r----- 1 oracle dba 2311 Mar 27 17:11 orasql_ora_3320.trc
-rw-r----- 1 oracle dba 441 Mar 27 17:11 orasql_m000_3322.trm
-rw-r----- 1 oracle dba 6170 Mar 27 17:11 orasql_m000_3322.trc
-rw-r----- 1 oracle dba 1550410 Mar 27 17:11 alert_orasql.log
-rw-r----- 1 oracle dba 1080 Mar 27 17:11 orasql_dm00_3326.trm
-rw-r----- 1 oracle dba 3885 Mar 27 17:11 orasql_dm00_3326.trc
-rw-r----- 1 oracle dba 496 Mar 27 17:11 orasql_lgwr_2848.trm
-rw-r----- 1 oracle dba 4078 Mar 27 17:11 orasql_lgwr_2848.trc
-rw-r----- 1 oracle dba 6499 Mar 27 17:11 orasql_dw00_3328.trm
-rw-r----- 1 oracle dba 22614 Mar 27 17:11 orasql_dw00_3328.trc

测试在导出时不使用并行，然后导入时指定parallel参数等于4：

[oracle@localhost log]$ expdp awen/oracle dumpfile=ait_%U.dmp logfile=1.log parallel=1 tables=A_IMPDP_TEST

Export: Release 11.2.0.4.0 - Production on Sun Mar 27 17:43:52 2016

[oracle@localhost log]$ impdp awen/oracle dumpfile=ait_%U.dmp logfile=2.log parallel=4 tables=A_IMPDP_TEST

Import: Release 11.2.0.4.0 - Production on Sun Mar 27 17:46:15 2016

从trace文件可以看出，索引创建时使用了并行：

-rw-r----- 1 oracle dba 83 Mar 27 17:46 orasql_p007_9699.trm
-rw-r----- 1 oracle dba 2627 Mar 27 17:46 orasql_p007_9699.trc
-rw-r----- 1 oracle dba 83 Mar 27 17:46 orasql_p006_9697.trm
-rw-r----- 1 oracle dba 2632 Mar 27 17:46 orasql_p006_9697.trc
-rw-r----- 1 oracle dba 83 Mar 27 17:46 orasql_p005_9695.trm
-rw-r----- 1 oracle dba 2628 Mar 27 17:46 orasql_p005_9695.trc
-rw-r----- 1 oracle dba 92 Mar 27 17:46 orasql_p004_9693.trm
-rw-r----- 1 oracle dba 2630 Mar 27 17:46 orasql_p004_9693.trc
-rw-r----- 1 oracle dba 3944 Mar 27 17:46 orasql_p003_9691.trm
-rw-r----- 1 oracle dba 28548 Mar 27 17:46 orasql_p003_9691.trc
-rw-r----- 1 oracle dba 1971 Mar 27 17:46 orasql_p002_9689.trm
-rw-r----- 1 oracle dba 18380 Mar 27 17:46 orasql_p002_9689.trc
-rw-r----- 1 oracle dba 3672 Mar 27 17:46 orasql_p001_9687.trm
-rw-r----- 1 oracle dba 27584 Mar 27 17:46 orasql_p001_9687.trc
-rw-r----- 1 oracle dba 4809 Mar 27 17:46 orasql_p000_9685.trm
-rw-r----- 1 oracle dba 31436 Mar 27 17:46 orasql_p000_9685.trc

EXEC #3084613420:c=87986,e=1584439,p=0,cr=45,cu=752,mis=0,r=0,dep=1,og=3,plh=166871857,tim=1459071987554611
STAT #3084613420 id=1 cnt=0 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 time=0 us)'
STAT #3084613420 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (ORDER) :TQ10001 (cr=0 pr=0 pw=0 time=0 us)'
STAT #3084613420 id=3 cnt=1 pid=2 pos=1 obj=0 op='INDEX BUILD NON UNIQUE IND_AIT_OBJID (cr=45 pr=0 pw=142 time=1549013 us)'
STAT #3084613420 id=4 cnt=67620 pid=3 pos=1 obj=0 op='SORT CREATE INDEX (cr=0 pr=0 pw=0 time=672821 us)'
STAT #3084613420 id=5 cnt=67620 pid=4 pos=1 obj=0 op='PX RECEIVE (cr=0 pr=0 pw=0 time=283350 us cost=2 size=26000 card=2000)'
STAT #3084613420 id=6 cnt=0 pid=5 pos=1 obj=0 op='PX SEND RANGE :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=2 size=26000 card=2000)'
STAT #3084613420 id=7 cnt=0 pid=6 pos=1 obj=0 op='PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2 size=26000 card=2000)'
STAT #3084613420 id=8 cnt=0 pid=7 pos=1 obj=19067 op='TABLE ACCESS FULL A_IMPDP_TEST (cr=0 pr=0 pw=0 time=0 us cost=2 size=26000 card=2000)'
CLOSE #3084613420:c=0,e=168,dep=1,type=0,tim=1459071987588896

结论：

1、数据泵在导入时一般情况下只需指定parallel>1即可在创建索引时候使用并行，导入时可以从trace文件中看到数据泵采用并行创建索引；
2、创建索引时并行度可能会等于parallel*2，这是因为oracle产生了两组query slave set，一组用来扫描表，一组用来创建索引；
3、如果数据泵带有sqlfile参数，得到的sql文本中并行度会显示为1，但是在实际导入时是可以并行创建的；
4、导出时未并行导出，导入时只要指定parallel参数同样可以并行创建索引；
5、如果不想并行创建索引，去掉parallel参数，或者指定parallel=1即可。

参考文档：

Impdp Parallel Index Creation always creates indexes with degree 1. (Doc ID 1289032.1)
Bug 8604502 : INDEXES ARE ALWAYS CREATED WITH PARALLEL DEGREE 1 DURING IMPORT