oracle 数据泵导出时间,Exp 导出与 Expdp 的速度与时间对比

曾经终败给现在 2023-01-18 06:59 1137阅读 0赞

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

![6ee5639a40442445944d63b514b2dd02.png][]

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

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

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

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 |