資料泵不同工作方式效能比較(五)

yangtingkun發表於2010-01-31

根據Oracle的文件的描述,資料泵採用不同的方式匯出匯入,效能也會有明顯的差別,這次正好有機會測試一下,遷移表空間、直接路徑、外部表方式,以及資料庫鏈方式匯出、匯入的效能差異。

資料泵不同工作方式效能比較(一):http://yangtingkun.itpub.net/post/468/496368

資料泵不同工作方式效能比較(二):http://yangtingkun.itpub.net/post/468/496396

資料泵不同工作方式效能比較(三):http://yangtingkun.itpub.net/post/468/496397

資料泵不同工作方式效能比較(四):http://yangtingkun.itpub.net/post/468/496398

這篇測試NETWORK_LINK匯入方式。

 

 

首先清除上一篇文章中匯入的使用者和表空間,並重新建立測試使用者和表空間。

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

這個步驟總用時230秒。

由於Oracle資料泵的預設匯出、匯入都是直接路徑方式,因此當前的測試不需要進行額外的設定。

使用NETWORK_LINK方式執行匯入是否的簡單,不需要匯出和傳送的過程,只需要一個impdp命令就可以完成所有的操作:

[oracle@tj dmp]$ impdp system network_link=NEWDEMO logfile=d_dmp:procedure.log schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper

Import: Release 10.2.0.3.0 - 64bit Production on 星期四, 18 6, 2009 15:11:06

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
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10.13 GB
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
. . imported "TJSQ_NDMAIN"."CAT_REGION_MERCHANDISE"     3095660 rows
. . imported "TJSQ_TRADE"."ORD_HIT_COMM"                1508527 rows
. . imported "TJSQ_TRADE"."CON_ITEM_SEND_IND"           6212238 rows
.
.
.
. . imported "TJSQ_TRADE_OPER"."WYP_DISABLE_DUP"              0 rows
. . imported "TJSQ_TRADE_OPER"."WYP_DISABLE_HEPING14"         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_SCHEMA_01" successfully completed at 15:45:42

整個的匯入過程用了3436秒。

同樣把資料加入到記錄表中:

SQL> INSERT INTO T_EXPDP_IMPDP_RECORD VALUES
  2  ('NETWORK_LINK', 0, 0, 34*60+36, 150, 34*60+36+150);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM T_EXPDP_IMPDP_RECORD
  2  WHERE TYPE = 'NETWORK_LINK';

TYPE                 EXPDP_TIME TRANS_TIME IMPDP_TIME OTHER_TIME TOTAL_TIME
-------------------- ---------- ---------- ---------- ---------- ----------
NETWORK_LINK                  0          0       2076        150       2226

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 = 'NETWORK_LINK';

EXPDP_RATE TRANS_RATE IMPDP_RATE OTHER_RATE
---------- ---------- ---------- ----------
         0          0      93.26       6.74

整個NETWORK_LINK匯入方式用了376秒。

雖然沒有必要,但是為了更好的說明問題,下面執行一個NETWORK_LINK方式的匯出:

[oracle@tj dmp]$ expdp system network_link=NEWDEMO directory=d_dmp dumpfile=tjsq_network.dp logfile=network_exp.log schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper

Export: Release 10.2.0.3.0 - 64bit Production on 星期四, 18 6, 2009 16:18:50

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/******** network_link=NEWDEMO directory=d_dmp dumpfile=tjsq_network.dp logfile=network_exp.log schemas=tjsq_trade,tjsq_ndmain,tjsq_gov,tjsq_ndmain_oper,tjsq_trade_oper
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10.13 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_NDMAIN"."CAT_REGION_MERCHANDISE"      1.662 GB 3095660 rows
. . 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_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_network.dp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:30:46

可以看到NETWORK_LINK方式的匯出耗時1156

根據文件NETWORK_LINK方式的速度是最慢的,如果只是對比直接路徑匯出、外部表匯入以及NETWORK_LINK匯出方式,顯然這種匯出方式是最慢的。

同樣對比直接路徑匯入、外部表匯入以及NETWORK_LINK匯入方式,顯然這種匯入方式也是最慢的。

但是,NETWORK_LINK的匯入可以將匯出、傳送資料檔案和匯入整合在一個操作中完成,因此如果單獨對比匯出或匯入時間是不公平的。

對比第三篇文章中的資料就可以發現,NETWORK_LINK匯出居然比直接路徑匯出加上檔案傳遞的速度還快了3分鐘左右。這是因為網路傳送其實是操作的瓶頸,資料泵生成匯出資料的速度要遠遠快於等待傳遞的時間,因此匯出和IO時間在整個操作中並不明顯。

可以看到NETWORK_LINK方式的總體速度僅比直接路徑方式慢了5分鐘左右。而從操作上將這種方式是最簡單的,只需要一個impdp命令,不再需要任何其他的操作了。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/4227/viewspace-626501/,如需轉載,請註明出處,否則將追究法律責任。

相關文章