当前位置导航:炫浪网>>网络学院>>编程开发>>Oracle教程

10g新特性expdp与传统exp的速度比较


  测试环境:
  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
相关内容
赞助商链接