資料泵不同工作方式效能比較（二）
根據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部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-626290/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵不同工作方式效能比較（六）
- 資料泵不同工作方式效能比較（五）
- 資料泵不同工作方式效能比較（四）
- 資料泵不同工作方式效能比較（三）
- 資料泵不同工作方式效能比較（一）
- 10g資料泵和匯入匯出效能對比（二）
- insert的不同場景效能比較
- 【原創】比較資料泵和exp/imp對相同資料匯出/匯入的效能差異
- Dapper, Ef core, Freesql 插入大量資料效能比較（二）APPSQL
- XML資料讀取方式效能比較XML
- 百萬行資料查詢效能比較
- Java不同壓縮演算法的效能比較Java演算法
- MySQL大量資料入庫的效能比較MySql
- Java不同資料結構記憶體消耗比較Java資料結構記憶體
- Java中不同的併發實現的效能比較Java
- 如何比較兩個資料庫表結構的不同資料庫
- 不同資料型別與零值比較的if從句資料型別
- 不同DBMS的SQL比較SQL
- 10g資料泵和匯入匯出效能對比（三）
- 10g資料泵和匯入匯出效能對比（一）
- 10g資料泵和匯入匯出效能對比（四）
- 10g資料泵和匯入匯出效能對比（六）
- 10g資料泵和匯入匯出效能對比（五）
- MySQL大量資料入庫的效能比較（分割槽）MySql
- Julia不同資料型別函式的內部程式碼比較資料型別函式
- Linux 比較不同命令Linux
- 不同解決方案的比較
- MySQL大量資料插入各種方法效能分析與比較MySql
- 用PHP連mysql和oracle資料庫效能比較（轉）PHPMySqlOracle資料庫
- JAVA IO效能比較Java
- 批量更新效能比較
- 圖資料庫比較資料庫
- 資料結構比較資料結構
- 主流資料庫比較資料庫
- Java基礎（二）－ 普通for迴圈、foreach效能比較Java
- 不同Java垃圾回收器的比較Java
- 不同備份方法的特性比較
- HHDESK資料夾比較功能