資料泵不同工作方式效能比較（二）

yangtingkun發表於2010-01-28

根據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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章