【原創】比較資料泵和exp/imp對相同資料匯出/匯入的效能差異

EXP/IMP 是資料庫傳統的匯出匯入工具，它有使用方便，在服務端和客戶端都可用的優點，速度沒有expdp快，功能沒有expdp強大

EXP  工具幫助文件，我們可以參考幫助文件進行命令列配置

[oracle@leonarding1 ~]$ exp –help    或者    exp help=y

Export: Release 11.2.0.1.0 - Production onThu Jun 20 07:28:33 2013

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

You can let Export prompt you forparameters by entering the EXP

command followed by your username/password:

    Example: EXP SCOTT/TIGER

Or, you can control how Export runs byentering the EXP command followed

by various arguments. To specifyparameters, you use keywords:

    Format:  EXP KEYWORD=value rKEYWORD=(value1,value2,...,valueN)

    Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)

               or TABLES=(T1:P1,T1:P2), if T1is partitioned table

USERID must be the first parameter on thecommand line.

Keyword   Description (Default)     Keyword      Description (Default)

--------------------------------------------------------------------------

USERID    username/password         FULL         export entire file(N)

BUFFER    size of data buffer       OWNER        list of ownerusernames

FILE      output files (EXPDAT.DMP)  TABLES       list of table names

COMPRESS  import into one extent (Y) RECORDLENGTH length of IO record

GRANTS    export grants (Y)         INCTYPE      incremental exporttype

INDEXES   export indexes (Y)        RECORD       track incr. export(Y)

DIRECT    direct path (N)            TRIGGERS     export triggers (Y)

LOG       log file of screen output STATISTICS   analyze objects(ESTIMATE)

ROWS      export data rows (Y)      PARFILE      parameter filename

CONSISTENT cross-table consistency(N)CONSTRAINTS  export constraints (Y)

OBJECT_CONSISTENT    transaction set to read only during objectexport (N)

FEEDBACK             display progress every x rows (0)

FILESIZE             maximum size of each dump file

FLASHBACK_SCN        SCN used to set session snapshot backto

FLASHBACK_TIME       time used to get the SCN closest to thespecified time

QUERY                select clause used to export asubset of a table

RESUMABLE            suspend when a space related erroris encountered(N)

RESUMABLE_NAME       text string used to identify resumablestatement

RESUMABLE_TIMEOUT    wait time for RESUMABLE

TTS_FULL_CHECK       perform. full or partial dependency checkfor TTS

VOLSIZE              number of bytes to write to eachtape volume

TABLESPACES          list of tablespaces to export

TRANSPORT_TABLESPACE export transportabletablespace metadata (N)

TEMPLATE             template name which invokes iASmode export

Export terminated successfully withoutwarnings.

#########################################################################

建立測試表leo1

LEO1@LEO1>set linesize 400 pagesize 999                                      格式化

LEO1@LEO1>drop table leo1 purge;                                                 清空環境

Table dropped.

LEO1@LEO1>create table leo1 as select *from dba_objects;     建立測試表leo1

Table created.

LEO1@LEO1>insert into leo1 select * fromleo1;

72543 rows created.

LEO1@LEO1>insert into leo1 select * fromleo1;

145086 rows created.

LEO1@LEO1>insert into leo1 select * fromleo1;

290172 rows created.

LEO1@LEO1>insert into leo1 select * fromleo1;

580344 rows created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>insert into leo1 select * fromleo1;               

1160688 rows created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select count(*) from leo1;                      現在是232萬

COUNT(*)

----------

  2321376

[oracle@leonarding1 ~]$ mkdir exp_dump                    建立一個匯出檔案的資料夾

[oracle@leonarding1 ~]$ exp leo1/leo1file='/home/oracle/exp_dump/leo1.dmp' tables=leo1 rows=y

Export: Release11.2.0.1.0 - Production on Fri Jun 21 06:38:28 2013

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Export done in US7ASCII character set andAL16UTF16 NCHAR character set

server uses ZHS16GBK character set(possible charset conversion)

About to export specified tables viaConventional Path ...

. . exporting table                           LEO1    2321376 rows exported

Export terminated successfully withoutwarnings.  成功終止匯出，沒有出現告警

[oracle@leonarding1 ~]$ date

Fri Jun 21 06:39:40 CST 2013

匯出從06:38:28開始到06:39:40結束，一共用時72秒

LEO1@LEO1>truncate table leo1;                           清空表，我再匯入一下

Table truncated.

[oracle@leonarding1 ~]$ cd exp_dump/

[oracle@leonarding1 exp_dump]$ ll

total 261356

-rw-r--r-- 1 oracle oinstall 267362304 Jun21 06:39 leo1.dmp      這個就是我們匯出的檔案

[oracle@leonarding1 exp_dump]$ impleo1/leo1 file='/home/oracle/exp_dump/leo1.dmp' full=y ignore=y

Import: Release11.2.0.1.0 - Production on Fri Jun 21 06:45:04 2013

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Export file created by EXPORT:V11.02.00 viaconventional path

import done in US7ASCII character set andAL16UTF16 NCHAR character set

import server uses ZHS16GBK character set(possible charset conversion)

. importing LEO1's objects into LEO1

. importing LEO1's objects into LEO1

. . importing table                         "LEO1"    2321376 rows imported

Import terminated successfully withoutwarnings.  成功終止匯入，沒有出現告警

[oracle@leonarding1 exp_dump]$ date

Fri Jun 21 06:48:26 CST 2013

匯入從06:45:04開始到06:48:26結束，一共用時202秒

EXPDP/IMPDP 是Oracle推崇的資料泵匯入匯出工具，用於代替傳統的EXP/IMP，只能在服務端使用，效率比EXP/IMP快幾十倍，有續傳功能和並行功能。這個工具始於Oracle10g，從Oracle11g開始不再提供老的EXP/IMP的諮詢但工具還可以使用。

EXPDP工具的效率差不多比EXP快幾倍

IMPDP工具的效率差不多比IMP快幾十倍

所以說這兩個工具適用於大資料匯入匯出的場景

匯出檔案的格式更接近於資料庫本身的檔案格式，避免了資料寫入檔案時的轉換

直接路徑載入，跳過SGA記憶體區，直接載入到高水位線之後

後設資料metadata和資料data在匯出的過程中可以重疊進行，提高匯出的效率。

我們再來看看EXPDP/IMPDP的匯出匯入效果

在使用EXPDP/IMPDP之前我們需要定義一個目錄物件，告知EXPDP/IMPDP工具匯出的檔案和匯入的檔案的存放目錄

LEO1@LEO1>create directory exp_dump as'/home/oracle/exp_dump';

Directory created.

[oracle@leonarding1 exp_dump]$ expdpleo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp tables=leo1;

Export: Release11.2.0.1.0 - Production on Fri Jun 21 07:07:09 2013

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Starting"LEO1"."SYS_EXPORT_TABLE_01":  leo1/******** directory=exp_dumpdumpfile=expdp_leo1.dmp tables=leo1

Estimate in progress using BLOCKS method...

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 264MB

Processing object typeTABLE_EXPORT/TABLE/TABLE

. . exported"LEO1"."LEO1"                               223.9 MB 2321376 rows

Master table"LEO1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for LEO1.SYS_EXPORT_TABLE_01is:

/home/oracle/exp_dump/expdp_leo1.dmp

Job"LEO1"."SYS_EXPORT_TABLE_01" successfully completed at07:08:22

匯出從07:07:09開始到07:08:22結束，一共用時73秒，匯出檔案大小223.9MB 行數2321376 rows，與exp時間相差1秒，expdp在資料量比較大的時候會體現出高效率。

現在我們impdp匯入

LEO1@LEO1>drop table leo1;   先把表刪除，因為如果表結構存在的話，impdp會認為後設資料已存在報錯，不可匯入

Table dropped.

[oracle@leonarding1 exp_dump]$ impdpleo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp

Import: Release11.2.0.1.0 - Production on Fri Jun 21 07:24:07 2013

Copyright (c) 1982, 2009, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Master table"LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting"LEO1"."SYS_IMPORT_FULL_01":  leo1/******** directory=exp_dumpdumpfile=expdp_leo1.dmp

Processing object typeTABLE_EXPORT/TABLE/TABLE

Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA

. . imported"LEO1"."LEO1"                               223.9 MB 2321376rows

Job"LEO1"."SYS_IMPORT_FULL_01" successfully completed at07:27:27

匯入從07:24:07開始到07:27:27結束，一共用時200秒，匯入檔案大小223.9MB 行數2321376 rows，比imp時間節約了2秒，impdp也是在資料量比較大的時候會體現出高效率。

2013.6.22
北京&summer
分享~成就夢想
Blog：