数据泵不同工作方式性能比较（二）
根据Oracle的文档的描述，数据泵采用不同的方式导出导入，性能也会有明显的差别，这次正好有机会测试一下，迁移表空间、直接路径、外部表方式，以及数据库链方式导出、导入的性能差异。
数据泵不同工作方式性能比较（一）：http://yangtingkun.itpub.net/post/468/496368
这篇测试迁移表空间方式。
首先检查源数据库的表空间是否满足自包含条件：
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TJSQ,TJSQ_TMP')
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
设置源数据库迁移表空间为只读状态：
SQL> ALTER TABLESPACE TJSQ READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE TJSQ_TMP READ ONLY;
Tablespace altered.
下面利用数据泵执行表空间迁移的导出操作：
SQL> HOST
[oracle@yans2 ~]$ expdp system directory=d_dmpdp dumpfile=tjsq_090617_trans_tablespace.dp transport_tablespaces=tjsq, tjsq_tmp
Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 17 June, 2009 18:07:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=d_dmpdp dumpfile=tjsq_090617_trans_tablespace.dp transport_tablespaces=tjsq, tjsq_tmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_TABLE_ACTION
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW_LOG
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/data/dmp/tjsq_090617_trans_tablespace.dp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 18:08:30
可以看到由于不需要导出表中的数据，因此导出操作十分迅速就完成了，只用了1分1秒。
下面利用DBMS_FILE_TRANSFER包进行传送：
SQL> SET TIMING ON
SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DATAFILE', 'tjsq01.dbf', 'NEWDEMO', 'D_DATAFILE', 'tjsq01.dbf')
PL/SQL procedure successfully completed.
Elapsed: 00:30:57.84
SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DATAFILE', 'tjsq02.dbf', 'NEWDEMO', 'D_DATAFILE', 'tjsq02.dbf')
PL/SQL procedure successfully completed.
Elapsed: 00:06:29.41
SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DATAFILE', 'tjsq_tmp.dbf', 'NEWDEMO', 'D_DATAFILE', 'tjsq_tmp.dbf')
PL/SQL procedure successfully completed.
Elapsed: 00:06:28.63
SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DMPDP', 'tjsq_090617_trans_tablespace.dp', 'NEWDEMO', 'D_DMP', 'tjsq_trans_tablespace.dp')
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.22
DBMS_FILE_TRANSFER包一共用了43分59秒。
现在可以将源数据库表空间至于可写状态。
目标数据库建立用户，并授权：
SQL> CREATE USER TJSQ_NDMAIN IDENTIFIED BY TJSQ_NDMAIN;
User created.
SQL> CREATE USER TJSQ_TRADE IDENTIFIED BY TJSQ_TRADE;
User created.
SQL> CREATE USER TJSQ_GOV IDENTIFIED BY TJSQ_GOV;
User created.
SQL> CREATE USER TJSQ_NDMAIN_OPER IDENTIFIED BY TJSQ_NDMAIN_OPER;
User created.
SQL> CREATE USER TJSQ_TRADE_OPER IDENTIFIED BY TJSQ_TRADE_OPER;
User created.
SQL> GRANT CONNECT TO TJSQ_GOV;
Grant succeeded.
SQL> GRANT RESOURCE TO TJSQ_GOV;
Grant succeeded.
SQL> GRANT CONNECT TO TJSQ_NDMAIN;
Grant succeeded.
SQL> GRANT RESOURCE TO TJSQ_NDMAIN;
Grant succeeded.
SQL> GRANT CONNECT TO TJSQ_NDMAIN_OPER;
Grant succeeded.
SQL> GRANT RESOURCE TO TJSQ_NDMAIN_OPER;
Grant succeeded.
SQL> GRANT CONNECT TO TJSQ_TRADE;
Grant succeeded.
SQL> GRANT RESOURCE TO TJSQ_TRADE;
Grant succeeded.
SQL> GRANT CONNECT TO TJSQ_TRADE_OPER;
Grant succeeded.
SQL> GRANT RESOURCE TO TJSQ_TRADE_OPER;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO TJSQ_GOV;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO TJSQ_NDMAIN;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO TJSQ_NDMAIN_OPER;
Grant succeeded.
SQL> GRANT CREATE DATABASE LINK TO TJSQ_TRADE;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO TJSQ_TRADE;
Grant succeeded.
SQL> GRANT CREATE SYNONYM TO TJSQ_TRADE_OPER;
Grant succeeded.
最后利用数据库执行导入操作：
[oracle@tj ~]$ impdp system dumpfile=tjsq_trans_tablespace.dp directory=d_dmp logfile=tjsq_trans.log transport_datafiles=/data/oracle/oradata/tjsq/tjsq01.dbf, /data/oracle/oradata/tjsq/tjsq02.dbf, /data/oracle/oradata/tjsq/tjsq_tmp.dbf
Import: Release 10.2.0.3.0 - 64bit Production on 星期三, 17 6月, 2009 19:59:58
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=tjsq_trans_tablespace.dp directory=d_dmp logfile=tjsq_trans.log transport_datafiles=/data/oracle/oradata/tjsq/tjsq01.dbf, /data/oracle/oradata/tjsq/tjsq02.dbf, /data/oracle/oradata/tjsq/tjsq_tmp.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_TABLE_ACTION
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW_LOG
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 20:04:20
导入总共用了4分22秒。
由于传输表空间只会导入表空间包含的表、索引等数据对象，而过程、视图、同义词、序列等对象无法通过传输表空间完成：
SQL> SELECT OBJECT_TYPE, COUNT(*)
2 FROM DBA_OBJECTS
3 WHERE OWNER IN ('TJSQ_TRADE', 'TJSQ_NDMAIN', 'TJSQ_GOV', 'TJSQ_NDMAIN_OPER', 'TJSQ_TRADE_OPER')
4 GROUP BY OBJECT_TYPE
5 ORDER BY 1;
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 938
INDEX PARTITION 16
LOB 7
TABLE 1237
TABLE PARTITION 113
可以看到目标数据库通过传输表空间只导入了索引、索引分区、大对象、表和表分区5种类型的对象。
而在源数据库检查对象包括：
SQL> SELECT OBJECT_TYPE
2 FROM DBA_OBJECTS
3 WHERE OWNER IN ('TJSQ_TRADE', 'TJSQ_NDMAIN', 'TJSQ_GOV', 'TJSQ_NDMAIN_OPER', 'TJSQ_TRADE_OPER')
4 GROUP BY OBJECT_TYPE
5 ORDER BY 1;
OBJECT_TYPE
-------------------
FUNCTION
INDEX
INDEX PARTITION
LOB
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
11 rows selected.
下面利用数据泵的NETWORK_LINK方式导入其他对象：
[oracle@tj ~]$ impdp system network_link=NEWDEMO logfile=d_dmp:procedure.log schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper include=procedure include=package include=function include=synonym include=sequence
Import: Release 10.2.0.3.0 - 64bit Production on 星期四, 18 6月, 2009 10:27:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** network_link=NEWDEMO logfile=d_dmp:procedure.log schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper include=procedure include=package include=function include=synonym include=sequence
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 10:28:00
导入这些对象用时58秒。
建立记录表，记录用时：
SQL> CREATE TABLE T_EXPDP_IMPDP_RECORD
2 (TYPE VARCHAR2(20),
3 EXPDP_TIME NUMBER,
4 TRANS_TIME NUMBER,
5 IMPDP_TIME NUMBER,
6 OTHER_TIME NUMBER,
7 TOTAL_TIME NUMBER);
Table created.
SQL> INSERT INTO T_EXPDP_IMPDP_RECORD VALUES
2 ('TRANSPORT_TABLESPACE', 61, 43*60+59, 4*60+22, 58, 61+43*60+59+4*60+22+58);
1 row created.
SQL> SELECT * FROM T_EXPDP_IMPDP_RECORD;
TYPE EXPDP_TIME TRANS_TIME IMPDP_TIME OTHER_TIME TOTAL_TIME
-------------------- ---------- ---------- ---------- ---------- ----------
TRANSPORT_TABLESPACE 61 2639 262 58 3020
数据泵进行表空间迁移28G的数据文件，总共用时50分钟20秒，其中导出、网络传输时间、导入时间和其他时间所占总体时间百分比如下：
SQL> SELECT ROUND(EXPDP_TIME/TOTAL_TIME*100, 2) EXPDP_RATE,
2 ROUND(TRANS_TIME/TOTAL_TIME*100, 2) TRANS_RATE,
3 ROUND(IMPDP_TIME/TOTAL_TIME*100, 2) IMPDP_RATE,
4 ROUND(OTHER_TIME/TOTAL_TIME*100, 2) OTHER_RATE
5 FROM T_EXPDP_IMPDP_RECORD
6 WHERE TYPE = 'TRANSPORT_TABLESPACE';
EXPDP_RATE TRANS_RATE IMPDP_RATE OTHER_RATE
---------- ---------- ---------- ----------
2.02 87.38 8.68 1.92
超过87%的时间用在网络传送文件上，可以说对于迁移表空间而言，最大的瓶颈在于网络传送。如果采用千兆网络，那么可能传送的时间将缩短为现在时间的1/10，这种情况下，迁移表空间的效率无疑是相当高的，但是对于当前测试的情况，这种方式的导入效率并不算太高。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-626290/ ，如需转载，请注明出处，否则将追究法律责任。
