| 本月文章推荐 |
10g新特性expdp与传统exp的速度比较发表日期：2008-2-9
|
|
测试环境: System Configuration: Sun Microsystems sun4u Sun Enterprise 450 (4 X UltrASPARC-II 296MHz) System clock frequency: 99 MHz Memory size: 2048 Megabytes 数据量: 单表测试,1363292行记录，116.6 MB左右数据 1.使用并行 eXPdp 花费时间: Tue Apr 27 10:21:54 CST 2004 - Tue Apr 27 10:21:10 CST 2004 = 43秒 $ cat bak.sh date expdp eygle/eygle dumpfile=big_big_table.dmp Directory=dpdata tables=big_big_table job_name=exptab parallel=4 date $ ./bak.sh Tue Apr 27 10:21:10 CST 2004 Export: Release 10.1.0.2.0 - 64bit ProdUCtion on Tuesday, 27 April, 2004 10:21 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "EYGLE"."EXPTAB": eygle/******** dumpfile=big_big_table.dmp directory=dpdata tables=big_big_table job_name=exptab parallel=4 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 248 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows Master table "EYGLE"."EXPTAB" successfully loaded/unloaded ****************************************************************************** Dump file set for EYGLE.EXPTAB is: /opt/oracle/dpdata/big_big_table.dmp Job "EYGLE"."EXPTAB" successfully completed at 10:21 Tue Apr 27 10:21:54 CST 2004 2.使用正常 expdp 花费时间: Tue Apr 27 10:23:36 CST 2004 - Tue Apr 27 10:23:02 CST 2004 = 34 秒 看来并行的差异需要更大的数据量的测试 $ cat bak2.sh date expdp eygle/eygle dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab date $ ./bak2.sh Tue Apr 27 10:23:02 CST 2004 Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 10:23 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "EYGLE"."EXPTAB": eygle/******** dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 248 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows Master table "EYGLE"."EXPTAB" successfully loaded/unloaded ****************************************************************************** Dump file set for EYGLE.EXPTAB is: /opt/oracle/dpdata/big_big_table2.dmp Job "EYGLE"."EXPTAB" successfully completed at 10:23 Tue Apr 27 10:23:36 CST 2004 3.常规路径exp 花费时间:Tue Apr 27 10:27:00 CST 2004 - Tue Apr 27 10:24:54 CST 2004 = 2:06 这是花费时间最长的. 是 126/34 = 370.58823529411764705882352941176% expdp明显快于exp $ cat bak3.sh date exp eygle/eygle file=big_big_table3.dmp tables=big_big_table date $ ./bak3.sh Tue Apr 27 10:24:54 CST 2004 Export: Release 10.1.0.2.0 - Production on Tue Apr 27 10:24:54 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, 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 BIG_BIG_TABLE 1363292 rows exported Export terminated successfully without warnings. Tue Apr 27 10:27:00 CST 2004 4.直接路径exp 花费时间: Tue Apr 27 10:52:09 CST 2004 - Tue Apr 27 10:50:58 CST 2004 = 1.11 是 71/34 = 208.82352941176470588235294117647% 直接路径导出快于常规路径导出，但是仍然不敌expdp $ cat bak4.sh date exp eygle/eygle file=big_big_table3.dmp tables=big_big_table direct=y date $ ./bak4.sh Tue Apr 27 10:50:58 CST 2004 Export: Release 10.1.0.2.0 - Production on Tue Apr 27 10:50:58 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, 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 BIG_BIG_TABLE 1363292 rows exported Export terminated successfully without warnings. Tue Apr 27 10:52:09 CST 2004 5.文件大小 传统方式exp文件要大于expdp的文件大小 大约大出10M左右. $ ls -al total 741566 drwxr-xr-x 2 oracle dba 512 Apr 27 10:50 . drwxr-xr-x 23 oracle dba 1024 Apr 26 22:53 .. -rwxr-xr-x 1 oracle dba 120 Apr 27 10:21 bak.sh -rwxr-xr-x 1 oracle dba 111 Apr 27 10:22 bak2.sh -rwxr-xr-x 1 oracle dba 71 Apr 27 10:24 bak3.sh -rwxr-xr-x 1 oracle dba 80 Apr 27 10:50 bak4.sh -rw-r----- 1 oracle dba 122413056 Apr 27 10:21 big_big_table.dmp -rw-r----- 1 oracle dba 122417152 Apr 27 10:23 big_big_table2.dmp -rw-r--r-- 1 oracle dba 134604800 Apr 27 10:52 big_big_table3.dmp -rw-r--r-- 1 oracle dba 965 Apr 27 10:23 export.log 以上测试结果仅供参考. 6. imp测试 花费时间: Tue Apr 27 11:15:11 CST 2004 - Tue Apr 27 11:08:24 CST 2004 = 6:47s $ cat rev2.sh date imp eygle/eygle file=big_big_table3.dmp tables=big_big_table date $ ./rev2.sh Tue Apr 27 11:08:24 CST 2004 Import: Release 10.1.0.2.0 - Production on Tue Apr 27 11:08:24 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Export file created by EXPORT:V10.01.00 via direct path import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing EYGLE's objects into EYGLE . . importing table "BIG_BIG_TABLE" 1363292 rows imported Import terminated successfully without warnings. Tue Apr 27 11:15:11 CST 2004 $ 7. impdp 花费时间: Tue Apr 27 11:07:06 CST 2004 - Tue Apr 27 11:06:40 CST 2004 = 26s 与imp相比这个速度实在是惊人. 407/26 = 1565.3846153846153846153846153846% 无怪乎Oracle说impdp才是data pump真正杰出的地方(really stands out) $ ./rev.sh Tue Apr 27 11:06:40 CST 2004 Import: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 11:06 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Master table "EYGLE"."IMPTAB" successfully loaded/unloaded Starting "EYGLE"."IMPTAB": eygle/******** dumpfile=big_big_table.dmp tables=big_big_table directory=dpdata job_name=imptab |
| 上一篇：如何解决无法以SYSDBA的身份远程登陆ORACLE数据库
人气:940 下一篇：Oracle10g新特性-SYSAUX表空间 人气:886 |
| 浏览全部Oracle教程的内容
Dreamweaver插件下载 网页广告代码 祝你圣诞节快乐 2009年新年快乐 |
