[转载]10g新特性expdp与传统exp的速度比较
<P>信息来源:邪恶八进制信息安全团队 </P><P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412711 style="FONT-SIZE: 12px">测试环境:<BR><BR>System Configuration: Sun Microsystems sun4u Sun Enterprise 450 (4 X UltraSPARC-II 296MHz)<BR>System clock frequency: 99 MHz<BR>Memory size: 2048 Megabytes<BR><BR>数据量:<BR>单表测试,1363292行记录，116.6 MB左右数据</SPAN></FONT></P>
<P></P> <P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412713 style="FONT-SIZE: 12px">1.使用并行 expdp<BR><BR><BR>花费时间:<BR><FONT color=red>Tue Apr 27 10:21:54 CST 2004 - Tue Apr 27 10:21:10 CST 2004 = 43秒</FONT><BR><BR><BR>$ cat bak.sh<BR>date<BR>expdp eygle/eygle dumpfile=big_big_table.dmp directory=dpdata tables=big_big_table job_name=exptab parallel=4<BR>date<BR>$ ./bak.sh<BR>Tue Apr 27 10:21:10 CST 2004<BR><BR>Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 10:21<BR><BR>Copyright (c) 2003, Oracle. All rights reserved.<BR><BR>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Starting "EYGLE"."EXPTAB": eygle/******** dumpfile=big_big_table.dmp directory=dpdata tables=big_big_table job_name=exptab parallel=4 <BR>Estimate in progress using BLOCKS method...<BR>Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA<BR>Total estimation using BLOCKS method: 248 MB<BR>Processing object type TABLE_EXPORT/TABLE/TABLE<BR>. . exported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows<BR>Master table "EYGLE"."EXPTAB" successfully loaded/unloaded<BR>******************************************************************************<BR>Dump file set for EYGLE.EXPTAB is:<BR>/opt/oracle/dpdata/big_big_table.dmp<BR>Job "EYGLE"."EXPTAB" successfully completed at 10:21<BR><BR>Tue Apr 27 10:21:54 CST 2004</SPAN></FONT></P> <P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412716 style="FONT-SIZE: 12px">2.使用正常 expdp<BR><BR>花费时间:<BR><FONT color=red>Tue Apr 27 10:23:36 CST 2004 - Tue Apr 27 10:23:02 CST 2004 = 34 秒</FONT><BR><BR>看来并行的差异需要更大的数据量的测试<BR><BR>$ cat bak2.sh<BR>date<BR>expdp eygle/eygle dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab <BR>date<BR><BR>$ ./bak2.sh<BR>Tue Apr 27 10:23:02 CST 2004<BR><BR>Export: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 10:23<BR><BR>Copyright (c) 2003, Oracle. All rights reserved.<BR><BR>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Starting "EYGLE"."EXPTAB": eygle/******** dumpfile=big_big_table2.dmp directory=dpdata tables=big_big_table job_name=exptab <BR>Estimate in progress using BLOCKS method...<BR>Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA<BR>Total estimation using BLOCKS method: 248 MB<BR>Processing object type TABLE_EXPORT/TABLE/TABLE<BR>. . exported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows<BR>Master table "EYGLE"."EXPTAB" successfully loaded/unloaded<BR>******************************************************************************<BR>Dump file set for EYGLE.EXPTAB is:<BR>/opt/oracle/dpdata/big_big_table2.dmp<BR>Job "EYGLE"."EXPTAB" successfully completed at 10:23<BR><BR>Tue Apr 27 10:23:36 CST 2004</SPAN></FONT></P> <P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412720 style="FONT-SIZE: 12px">3.常规路径exp<BR><BR><FONT color=red>花费时间:Tue Apr 27 10:27:00 CST 2004 - Tue Apr 27 10:24:54 CST 2004 = 2:06</FONT><BR><BR>这是花费时间最长的.<BR><FONT color=red>是 126/34 = 370.58823529411764705882352941176%</FONT><BR><BR>expdp明显快于exp<BR><BR>$ cat bak3.sh<BR>date<BR>exp eygle/eygle file=big_big_table3.dmp tables=big_big_table<BR>date<BR><BR>$ ./bak3.sh<BR>Tue Apr 27 10:24:54 CST 2004<BR><BR>Export: Release 10.1.0.2.0 - Production on Tue Apr 27 10:24:54 2004<BR><BR>Copyright (c) 1982, 2004, Oracle. All rights reserved.<BR><BR><BR>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set<BR><BR>About to export specified tables via Conventional Path ...<BR>. . exporting table BIG_BIG_TABLE 1363292 rows exported<BR>Export terminated successfully without warnings.<BR>Tue Apr 27 10:27:00 CST 2004</SPAN></FONT></P> <P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412723 style="FONT-SIZE: 12px">4.直接路径exp<BR><BR><BR>花费时间:<BR><FONT color=red>Tue Apr 27 10:52:09 CST 2004 - Tue Apr 27 10:50:58 CST 2004 = 1.11</FONT><BR>是<FONT color=red> 71/34 = 208.82352941176470588235294117647%</FONT><BR><BR>直接路径导出快于常规路径导出，但是仍然不敌expdp<BR><BR>$ cat bak4.sh<BR>date<BR>exp eygle/eygle file=big_big_table3.dmp tables=big_big_table direct=y<BR>date<BR><BR>$ ./bak4.sh<BR>Tue Apr 27 10:50:58 CST 2004<BR><BR>Export: Release 10.1.0.2.0 - Production on Tue Apr 27 10:50:58 2004<BR><BR>Copyright (c) 1982, 2004, Oracle. All rights reserved.<BR><BR><BR>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set<BR><BR>About to export specified tables via Direct Path ...<BR>. . exporting table BIG_BIG_TABLE 1363292 rows exported<BR>Export terminated successfully without warnings.<BR>Tue Apr 27 10:52:09 CST 2004</SPAN></FONT></P> <P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412739 style="FONT-SIZE: 12px">5.文件大小<BR><BR>传统方式exp文件要大于expdp的文件大小<BR>大约大出10M左右.<BR><BR>$ ls -al<BR>total 741566<BR>drwxr-xr-x 2 oracle dba 512 Apr 27 10:50 .<BR>drwxr-xr-x 23 oracle dba 1024 Apr 26 22:53 ..<BR>-rwxr-xr-x 1 oracle dba 120 Apr 27 10:21 bak.sh<BR>-rwxr-xr-x 1 oracle dba 111 Apr 27 10:22 bak2.sh<BR>-rwxr-xr-x 1 oracle dba 71 Apr 27 10:24 bak3.sh<BR>-rwxr-xr-x 1 oracle dba 80 Apr 27 10:50 bak4.sh<BR>-rw-r----- 1 oracle dba 122413056 Apr 27 10:21 big_big_table.dmp<BR>-rw-r----- 1 oracle dba 122417152 Apr 27 10:23 big_big_table2.dmp<BR>-rw-r--r-- 1 oracle dba 134604800 Apr 27 10:52 big_big_table3.dmp<BR>-rw-r--r-- 1 oracle dba 965 Apr 27 10:23 export.log<BR><BR><BR>以上测试结果仅供参考.</SPAN></FONT></P> <P><FONT face="verdana, arial, helvetica" size=2><SPAN class=java script id=text1412850 style="FONT-SIZE: 12px">7. impdp<BR><BR><FONT color=red>花费时间: Tue Apr 27 11:07:06 CST 2004 - Tue Apr 27 11:06:40 CST 2004 = 26s</FONT><BR>与imp相比这个速度实在是惊人.<BR><FONT color=red>407/26 = 1565.3846153846153846153846153846%</FONT><BR><FONT color=blue>无怪乎Oracle说impdp才是data pump真正杰出的地方(really stands out)</FONT><BR><BR>$ ./rev.sh<BR>Tue Apr 27 11:06:40 CST 2004<BR><BR>Import: Release 10.1.0.2.0 - 64bit Production on Tuesday, 27 April, 2004 11:06<BR><BR>Copyright (c) 2003, Oracle. All rights reserved.<BR><BR>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production<BR>With the Partitioning, OLAP and Data Mining options<BR>Master table "EYGLE"."IMPTAB" successfully loaded/unloaded<BR>Starting "EYGLE"."IMPTAB": eygle/******** dumpfile=big_big_table.dmp tables=big_big_table directory=dpdata job_name=imptab <BR>Processing object type TABLE_EXPORT/TABLE/TABLE<BR>Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA<BR>. . imported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows<BR>Job "EYGLE"."IMPTAB" successfully completed at 11:07<BR><BR>Tue Apr 27 11:07:06 CST 2004</SPAN></FONT></P>
<P><FONT face="verdana, arial, helvetica" size=2><BR></FONT></P> dddddddddddddd
页:
[1]