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

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

這篇介紹一下測試環境。

 

 

源資料庫和目標資料庫的版本都是10.2.0.3，不存在版本差異，字符集都是ZHS16GBK，國家字符集都是AL16UTF16字符集，源資料庫和目標資料庫都是16k的BLOCK_SIZE，因此採用遷移表空間的方式不存在任何的問題：

SQL> SELECT GLOBAL_NAME FROM GLOBAL_NAME;

GLOBAL_NAME
------------------------------------------------------------------------
NEWDEMO

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE '%CHARACTERSET';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> SHOW PARAMETER DB_BLOCK_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     16384

上面是源資料庫的查詢結果，目標資料庫上面的查詢結果完全一致，除了GLOBAL_NAME之外：

SQL> SELECT GLOBAL_NAME FROM GLOBAL_NAME;

GLOBAL_NAME
----------------------------------------------------------------------------
TJSQ

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> COL PROPERTY_VALUE FORMAT A50
SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
  2  FROM DATABASE_PROPERTIES
  3  WHERE PROPERTY_NAME LIKE '%CHARACTERSET';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------------------------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> SHOW PARAMETER DB_BLOCK_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     16384

要遷移的資料分佈在兩個表空間中：

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024/1024
  2  FROM DBA_DATA_FILES
  3  WHERE TABLESPACE_NAME LIKE 'TJ%'
  4  ORDER BY 1, 2;

TABLESPACE_NAME FILE_NAME                                          BYTES/1024/1024/1024
--------------- -------------------------------------------------- --------------------
TJSQ            /data/oracle/oradata/newdemo/tjsq01.dbf                              20
TJSQ            /data/oracle/oradata/newdemo/tjsq02.dbf                               4
TJSQ_TMP        /data/oracle/oradata/newdemo/tjsq_tmp.dbf                             4

資料分佈在5個SCHEMA中：

SQL> SELECT OWNER, SUM(BYTES)/1024/1024/1024
  2  FROM DBA_SEGMENTS
  3  WHERE TABLESPACE_NAME LIKE 'TJ%'
  4  GROUP BY OWNER
  5  ORDER BY 2 DESC;

OWNER                          SUM(BYTES)/1024/1024/1024
------------------------------ -------------------------
TJSQ_TRADE                                    14.0916138
TJSQ_NDMAIN                                   8.00140381
TJSQ_NDMAIN_OPER                              1.71221924
TJSQ_TRADE_OPER                               .194641113
TJSQ_GOV                                      .080993652

可以看到，使用者物件佔用了大於24G的空間，僅比資料檔案小4G，這種情況還是比較適合遷移表空間方式的，否則如果資料檔案中包含了大量的空閒空間，使用遷移表空間方式效率很可能比直接資料泵匯入匯出效率還低。

雖然測試的資料量並不大，但是28G也可以說明一定問題了。

目標資料庫使用的本地磁碟，6塊300G做的RAID 1＋0，IO效能肯定不會太好。區域網是百兆網路，同樣也可能成為效能的瓶頸。有時候用一個普通的環境測試更能說明問題，因為如果IO不是瓶頸、網路也不是瓶頸，那麼很可能幾種方式的效率相差不了太多。

為了能體現資料泵的優勢，顯示外部表方式和直接路徑效率的區別，目標資料庫為非歸檔模式。

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

SQL> SHOW SGA

Total System Global Area 3707764736 bytes
Fixed Size                  2077104 bytes
Variable Size             721423952 bytes
Database Buffers         2969567232 bytes
Redo Buffers               14696448 bytes

由於測試的方法包括資料庫鏈直接匯入的方式，而這種方式將匯出、資料傳遞以及匯入合成到一個操作中進行，因此這個測試的最終時間由匯出時間、資料傳遞時間，以及匯入時間3個部分之和構成。

最後準備目標資料庫重建使用者的角色和許可權：

SQL> SELECT 'GRANT ' || GRANTED_ROLE || ' TO ' || GRANTEE || ';'
  2  FROM DBA_ROLE_PRIVS
  3  WHERE GRANTEE LIKE 'TJ%'
  4  ORDER BY GRANTEE, GRANTED_ROLE;

'GRANT'||GRANTED_ROLE||'TO'||GRANTEE||';'
-----------------------------------------------------------------------
GRANT CONNECT TO TJSQ_GOV;
GRANT RESOURCE TO TJSQ_GOV;
GRANT CONNECT TO TJSQ_NDMAIN;
GRANT RESOURCE TO TJSQ_NDMAIN;
GRANT CONNECT TO TJSQ_NDMAIN_OPER;
GRANT RESOURCE TO TJSQ_NDMAIN_OPER;
GRANT CONNECT TO TJSQ_TRADE;
GRANT RESOURCE TO TJSQ_TRADE;
GRANT CONNECT TO TJSQ_TRADE_OPER;
GRANT RESOURCE TO TJSQ_TRADE_OPER;

10 rows selected.

SQL> SELECT 'GRANT ' || PRIVILEGE || ' TO ' || GRANTEE || ';'
  2  FROM DBA_SYS_PRIVS
  3  WHERE GRANTEE LIKE 'TJ%'
  4  AND PRIVILEGE NOT IN
  5  (SELECT PRIVILEGE FROM DBA_SYS_PRIVS
  6  WHERE GRANTEE IN ('CONNECT', 'RESOURCE'))
  7  ORDER BY GRANTEE;

'GRANT'||PRIVILEGE||'TO'||GRANTEE||';'
---------------------------------------------------------------------------------
GRANT CREATE SYNONYM TO TJSQ_GOV;
GRANT CREATE SYNONYM TO TJSQ_NDMAIN;
GRANT CREATE SYNONYM TO TJSQ_NDMAIN_OPER;
GRANT CREATE DATABASE LINK TO TJSQ_TRADE;
GRANT CREATE SYNONYM TO TJSQ_TRADE;
GRANT CREATE SYNONYM TO TJSQ_TRADE_OPER;

6 rows selected.

在源資料庫和目標資料庫上建立對應DIRECTORY：

SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER           DIRECTORY_NAME  DIRECTORY_PATH
--------------- --------------- ----------------------------------------
SYS             TRAN            /data/dmp
SYS             D_DMPDP         /data/dmp
SYS             DATA_PUMP_DIR   /data/oracle/product/10.2/rdbms/log/

SQL> CREATE DIRECTORY D_DATAFILE AS '/data/oracle/oradata/newdemo';

Directory created.

目標資料庫建立DIRECTORIES：

SQL> CREATE DIRECTORY D_DMP AS '/data/dmp';

Directory created.

SQL> CREATE DIRECTORY D_DATAFILE AS '/data/oracle/oradata/tjsq';

Directory created.

建立到源資料庫的資料庫鏈，資料庫鏈方式的匯出，以及利用DBMS_FILE_TRANSFER包都會用到這個資料庫鏈：

SQL> CREATE DATABASE LINK NEWDEMO
  2  CONNECT TO SYSTEM
  3  IDENTIFIED BY PASSWORD
  4  USING 'NEWDEMO';

Database link created.

SQL> SELECT GLOBAL_NAME FROM GLOBAL_NAME@NEWDEMO;

GLOBAL_NAME
------------------------------------------------------------------------------------
NEWDEMO

下面就可以準備測試了。