eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 10g新特性之-跨平台传输表空间 | Blog首页 | Oracle诊断案例-Spfile案例一则 »

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
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
. . imported "EYGLE"."BIG_BIG_TABLE" 116.6 MB 1363292 rows
Job "EYGLE"."IMPTAB" successfully completed at 11:07

Tue Apr 27 11:07:06 CST 2004


-The End-


历史上的今天...
    >> 2013-06-26文章:
    >> 2011-06-26文章:
    >> 2009-06-26文章:
    >> 2008-06-26文章:
    >> 2007-06-26文章:
           摄影习作-小小的太阳
    >> 2006-06-26文章:
           Oracle9iR2 NF:压缩表技术
    >> 2005-06-26文章:

无觅

By eygle on 2004-06-26 20:34 | Comments (0) | Oracle12c/11g | 30 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com