oracle 数据泵导出时间,Exp 导出与 Expdp 的速度与时间对比 曾经终败给现在 2023-01-18 06:59 1086阅读 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 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 | 8 Comments "但是在使用expdp时会在数据库中生成表，而且还有Bug，应当清晰的了解这些" 怎么解释？每种倒入方式 都生成表啊;都有哪些bug啊？ "SMS"."SYS\_EXPORT\_TABLE\_01" Master Table，我指这个。 15分钟并不是dp的作用，而是cpu的作用 PARALLEL=2 34-02也不等于15啊 nohup 是同时提交3个exp? 34-02也不等于15啊 nohup 是同时提交3个exp? 汗，是我糊涂了。抱歉！ 我把expdp的时间当成rman类似的总时间消耗了。我错了！ 应该是用了32分钟！ [6ee5639a40442445944d63b514b2dd02.png]: /images/20221021/1788e654eebf4612a884f13041bb698e.png
还没有评论，来说两句吧...