« 大表海量数据的转移及索引创建的记录 | Blog首页 | AIX Oracle 安装纪要 - cpio 、X11 forwarding »

Exp 导出与 Expdp 的速度与时间对比

虽然expdp是Oracle 10g推荐的逻辑导出方式，但是仍然有很多人并不习惯这一方式，这与expdp的使用复杂度有关，我觉得expdp的directory方式应当简化，由参数行定义就好了，后台由数据库自动去维护。

最近在做导出时比较了一下几种方式对于速度的影响，记录一下数据供参考。

nohup time exp sms/sms file=smsmg_p2.dmp tables=SMSMG:M02 &
nohup time exp sms/sms file=smsmg_p3.dmp tables=SMSMG:M03 direct=y recordlength=65535 &
nohup time expdp sms/sms dumpfile=smsmg_p4.dmp directory=dataorg tables=SMSMG:M04 PARALLEL=2 &

首先，使用常规路径导出，1.3亿记录，大约耗时50分钟：

Export: Release 10.2.0.2.0 - Production on Thu Sep 17 23:22:57 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                    SMSMG
. . exporting partition                            M02  134528204 rows exported
Export terminated successfully with warnings.
1578.95user 260.72system 49:53.59elapsed 61%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (11major+3146minor)pagefaults 0swaps

使用直接路径模式导出，1.8亿记录，大约使用了22分钟时间：

Export: Release 10.2.0.2.0 - Production on Sat Sep 19 11:34:33 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Direct Path ...
. . exporting table                    SMSMG
. . exporting partition                            M03  188043489 rows exported
Export terminated successfully with warnings.
30.85user 508.78system 22:03.25elapsed 40%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (9major+3156minor)pagefaults 0swaps

使用expdp导出，1.6亿记录，大约用了32分钟：

Export: Release 10.2.0.2.0 - 64bit Production on Saturday, 19 September, 2009 14:02:07

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Starting "SMS"."SYS_EXPORT_TABLE_01":  sms/******** dumpfile=smsmg_p4.dmp directory=dataorg tables=SMSMG:M04 PARALLEL=2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 38 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SMS"."SMSMG":"M04"               34.87 GB 169622558 rows
Master table "SMS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SMS9885.SYS_EXPORT_TABLE_01 is:
  /racbackup/smsmg_p4.dmp
Job "SMS"."SYS_EXPORT_TABLE_01" successfully completed at 14:34:12

使用expdp的速度一般，起在评估空间使用上消耗了不少时间，更重要的是在将来的导入中，会有更好的性能。
但是在使用expdp时会在数据库中生成表，而且还有Bug，应当清晰的了解这些，以下是测试中告警日志中记录的信息：

Sat Sep 19 14:02:08 2009
The value (30) of MAXTRANS parameter ignored.
streams_pool_size defaulting to 80530636
Sat Sep 19 14:02:12 2009
ALTER SYSTEM SET service_names='ora','SYS$SYS.KUPC$C_1_20090919140209.ORADB' SCOPE=MEMORY SID='oradb1';
Sat Sep 19 14:02:13 2009
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20090919140209.ORADB','ora','SYS$SYS.KUPC$S_1_20090919140209.ORADB' SCOPE=MEMORY SID='oradb1';
kupprdp: master process DM00 started with pid=52, OS id=16672
         to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_TABLE_01', 'SMS9885', 'KUPC$C_1_20090919140209', 'KUPC$S_1_20090919140209', 0);
kupprdp: worker process DW01 started with worker id=1, pid=46, OS id=16859
         to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SMS9885');
Sat Sep 19 14:09:20 2009
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20090919140209.ORADB','ora','SYS$SYS.KUPC$S_1_20090919140209.ORADB','oradb' SCOPE=MEMORY SID='oradb1';
Sat Sep 19 14:10:36 2009
kupprdp: worker process DW02 started with worker id=2, pid=76, OS id=5288
         to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_TABLE_01', 'SMS9885');
Sat Sep 19 14:34:13 2009
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20090919140209.ORADB','ora','oradb' SCOPE=MEMORY SID='oradb1';
Sat Sep 19 14:34:14 2009
ALTER SYSTEM SET service_names='ora','oradb' SCOPE=MEMORY SID='oradb1';

该服务器的CPU为4颗4核的Intel(R) Xeon(R) CPU  E7330  @ 2.40GHz。

-The End-

By eygle on 2009-09-24 08:07 | Comments (8) | Backup&Recovery | Case | 2408 |

expdpExport