資料泵不同工作方式效能比較（四）
根據Oracle的文件的描述，資料泵採用不同的方式匯出匯入，效能也會有明顯的差別，這次正好有機會測試一下，遷移表空間、直接路徑、外部表方式，以及資料庫鏈方式匯出、匯入的效能差異。
資料泵不同工作方式效能比較（一）：http://yangtingkun.itpub.net/post/468/496368
資料泵不同工作方式效能比較（二）：http://yangtingkun.itpub.net/post/468/496396
資料泵不同工作方式效能比較（三）：http://yangtingkun.itpub.net/post/468/496397
這篇測試外部表匯出、匯入方式。
首先清除上一篇文章中匯入的使用者和表空間，並重新建立測試使用者和表空間。
SQL> DROP USER TJSQ_NDMAIN CASCADE;
User dropped.
SQL> DROP USER TJSQ_TRADE CASCADE;
User dropped.
SQL> DROP USER TJSQ_GOV CASCADE;
User dropped.
SQL> DROP USER TJSQ_NDMAIN_OPER CASCADE;
User dropped.
SQL> DROP USER TJSQ_TRADE_OPER CASCADE;
User dropped.
SQL> DROP TABLESPACE TJSQ INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> DROP TABLESPACE TJSQ_TMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
下面重新建立使用者，並設定許可權：
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.
表空間的建立以及資料檔案初始化的時間需要單獨計時：
SQL> SET TIMING ON
SQL> CREATE TABLESPACE TJSQ DATAFILE '/data/oracle/oradata/tjsq/tjsq01.dbf' size 20g,
2 '/data/oracle/oradata/tjsq/tjsq02.dbf' size 4g;
Tablespace created.
Elapsed: 00:02:07.68
SQL> CREATE TABLESPACE TJSQ_TMP DATAFILE '/data/oracle/oradata/tjsq/tjsq_tmp.dbf' size 4g;
Tablespace created.
Elapsed: 00:00:22.19
這個步驟總用時2分30秒。
顯然執行外部表方式的匯出，由於Oracle預設採用直接路徑的方式，因此要改變這種預設的設定，就必須做一些改動。
對於匯出而言，如果需要外部表方式對所有的表都適用，最好的方法就是使用QUERY方式匯出，加上一個恆等的匯出條件，將使得匯出不在使用直接路徑方式：
[oracle@yans2 ~]$ expdp system directory=d_dmpdp dumpfile=tjsq_external.dp schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper query=\"where 1=1\"
Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 18 June, 2009 15:59:13
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_SCHEMA_01": system/******** directory=d_dmpdp dumpfile=tjsq_external.dp schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper query="where 1=1"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9.230 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
. . exported "TJSQ_TRADE"."ORD_HIT_COMM" 1.180 GB 1508527 rows
. . exported "TJSQ_TRADE"."CON_ITEM_SEND_IND" 829.2 MB 6212238 rows
. . exported "TJSQ_NDMAIN"."PLT_ORG_PLAT" 87.76 KB 1383 rows
.
.
.
. . exported "TJSQ_TRADE_OPER"."WYP_DISABLE_DUP" 0 KB 0 rows
. . exported "TJSQ_TRADE_OPER"."WYP_DISABLE_HEPING14" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/data/dmp/tjsq_external.dp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:06:38
利用外部表方式匯出用了7分25秒。
下面使用DBMS_FILE_TRANSFER包進行檔案傳送：
SQL> SET TIMING ON
SQL> EXEC DBMS_FILE_TRANSFER.GET_FILE('D_DMPDP', 'tjsq_external.dp', 'NEWDEMO', 'D_DMP', 'tjsq_external.dp')
PL/SQL procedure successfully completed.
Elapsed: 00:10:22.82
檔案傳送用了10分23秒。
利用資料泵執行匯入，同樣的原因，為了使得每張表都使用外部表方式匯入資料，這裡新增了QUERY語句，在QUERY語句中加上一個恆等的條件。
[oracle@tj ~]$ impdp system dumpfile=tjsq_external.dp directory=d_dmp logfile=tjsq_imp_external.log query=\"where 1=1\"
Import: Release 10.2.0.3.0 - 64bit Production on 星期五, 19 6月, 2009 9:44:04
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_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=tjsq_external.dp directory=d_dmp logfile=tjsq_imp_external.log query="where 1=1"
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TJSQ_TRADE_OPER" already exists
ORA-31684: Object type USER:"TJSQ_NDMAIN_OPER" already exists
ORA-31684: Object type USER:"TJSQ_GOV" already exists
ORA-31684: Object type USER:"TJSQ_NDMAIN" already exists
ORA-31684: Object type USER:"TJSQ_TRADE" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TJSQ_TRADE"."ORD_HIT_COMM" 1.180 GB 1508527 rows
. . imported "TJSQ_TRADE"."CON_ITEM_SEND_IND" 829.2 MB 6212238 rows
. . imported "TJSQ_NDMAIN"."PLT_ORG_PLAT" 87.76 KB 1383 rows
.
.
.
. . imported "TJSQ_TRADE_OPER"."WYP_DISABLE_DUP" 0 KB 0 rows
. . imported "TJSQ_TRADE_OPER"."WYP_DISABLE_HEPING14" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 09:59:56
匯入執行了15分52秒。
現在將執行結果插入到記錄表中：
SQL> INSERT INTO T_EXPDP_IMPDP_RECORD VALUES
2 ('EXTERNAL_TABLE', 7*60+25, 623, 15*60+52, 150, 7*60+25+623+15*60+52+150);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM T_EXPDP_IMPDP_RECORD
2 WHERE TYPE = 'EXTERNAL_TABLE';
TYPE EXPDP_TIME TRANS_TIME IMPDP_TIME OTHER_TIME TOTAL_TIME
-------------------- ---------- ---------- ---------- ---------- ----------
EXTERNAL_TABLE 445 623 952 150 2170
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 = 'EXTERNAL_TABLE';
EXPDP_RATE TRANS_RATE IMPDP_RATE OTHER_RATE
---------- ---------- ---------- ----------
20.51 28.71 43.87 6.91
可以看到，外部表方式的匯出和匯入速度都要比直接路徑慢，而且匯出操作尤為明顯，不過可以確認的是，效能下降和兩方面因素有關，一個是外部表方式確實效率低於直接路徑方式，另一個是為了資料泵採用外部表方式而引入的QUERY語句，也會帶來一定的效能代價。
和其他匯入匯出方式相比，外部表方式在匯出、傳送、匯入三方面耗時相對來說比較平均。匯出佔20.5%，傳送佔了28.7%，而匯入最長用了43.87。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-626460/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 效能比較
- 如何比較兩個資料庫表結構的不同資料庫
- 四個id 生成器效能比較記錄
- Linux 比較不同命令Linux
- Dapper, Ef core, Freesql 插入大量資料效能比較（二）APPSQL
- 不同解決方案的比較
- 主流資料庫比較資料庫
- 圖資料庫比較資料庫
- 大資料入門課程：Hadoop和spark的效能比較大資料HadoopSpark
- python 批量resize效能比較Python
- difflib： Python 比較資料集Python
- HHDESK資料夾比較功能
- AWS Graviton2上資料壓縮演算法效能比較演算法
- 通過append hint來插入資料，演示它和普通插入資料的效能比較。APP
- 請比較下for、forEach、for of的效能的效能
- javascript訪問不同物件的速度比較JavaScript物件
- 關於 PHP 不同資料型別在比較時該如何轉化問題PHP資料型別
- Java中List集合效能比較Java
- 排序演算法效能比較排序演算法
- 77種資料建模工具比較
- 殺停資料泵
- IocPerformance 常見IOC 功能、效能比較ORM
- 堆排序和快速排序效能比較排序
- Java Bean Copy元件的效能比較JavaBean元件
- Java JIT與AOT效能比較 - foojayJava
- Play! Framework 系列（四）：DI 模式比較Framework模式
- 淺談前端MOCK資料工具比較前端Mock
- NLP 中不同詞嵌入技術的比較 - KDnuggets
- 資料庫升級之-資料泵資料庫
- mysql資料庫中decimal資料型別比較大小MySql資料庫Decimal資料型別
- Redis 不同插入方法的效能對比Redis
- 雲主機的硬碟IO效能比較硬碟
- Java幾種常用JSON庫效能比較JavaJSON
- WCF與ASP.NET Core效能比較ASP.NET
- PostgreSQL、Redis與Memcached的效能比較 - CYBERTECSQLRedis
- 四種在Javascript比較物件的方法JavaScript物件
- influxdb與傳統資料庫的比較UX資料庫
- 比較 Apache Hadoop 資料儲存格式 - techwellApacheHadoop
- mongodb資料遷移2種方式比較MongoDB