Oracle 10g Data pump and import export performance comparison (i)

Some time ago in a migration of data pump and exp, found that the difference in efficiency is quite large. Here is an example of a simple comparison.

First, compare the difference between exp general mode, exp direct path mode, and EXPDP three ways of exporting.

This example exports a scenario on a 10g database that is greater than 40G:

$ sqlplus "/as SYSDBA"

Sql*plus:release 10.2.0.3.0-production on Friday June 1 02:11:29 2007

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connect to:

Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options

sql> select sum (bytes)/1024/1024/1024 from dba_segments where owner = ' Zhejiang ';

SUM (BYTES)/1024/1024/1024

-------------------------

41.5518188

The first is the general path export of Exp:

$ exp Zhejiang/zhejiang file=/data1/zj_regular.dmp buffer=20480000

Export:release 10.2.0.3.0-production on Friday June 1 17:45:06 2007

Copyright (c) 1982, +, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options exported ZHS16GBK character set and Al16utf16 NCHAR character set

. Exporting Pre-schema process objects and actions

. Exporting external function library names for user Zhejiang

. Export public type synonyms

. Exporting private type synonyms

. Exporting object type definition for user Zhejiang the object that is about to export Zhejiang ...

. Exporting database Links

. Exporting ordinal

. Exporting cluster definitions

. The table to be exported Zhejiang through the general path ...

. . Exporting table A exports 1216 rows

.

.

.

. . Exporting table Z_invite_comm_pub 147 rows Exported

. Exporting synonyms

. Exporting views

. Exporting stored Procedures

. Exporting operators

. Exporting referential integrity constraints

. Exporting triggers

. Exporting index types

. Exporting bitmaps, functional indexes, and extensible indexes

. Exporting late table activity

. Exporting materialized views

. Exporting snapshot logs

. Exporting job queues

. Exporting refresh groups and subgroups

. Exporting dimension

. Exporting Post-Schema process objects and actions

. Exporting statistics successfully terminated the export without warning.

$ ls-l/data1/zj_regular.dmp

-rw-r--r--1 Oracle Oinstall 18540327936 June 1 19:09/data1/zj_regular.dmp

Depending on the start time and end time, you can see that the regular exp export is performed for 1 hours and 24 minutes.

Let's look at the direct path export method:

$ exp Zhejiang/zhejiang file=/data1/zj_direct.dmp buffer=20480000 recordlength=65535 direct=y

Export:release 10.2.0.3.0-production on Friday June 1 21:06:35 2007

Copyright (c) 1982, +, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45549.htm

With the partitioning, real application clusters, OLAP and Data Mining options exported ZHS16GBK character set and Al16utf16 NCHAR character set

. Exporting Pre-schema process objects and actions

. Exporting external function library names for user Zhejiang

. Export public type synonyms

. Exporting private type synonyms

. Exporting object type definition for user Zhejiang the object that is about to export Zhejiang ...

. Exporting database Links

. Exporting ordinal

. Exporting cluster definitions

. The table that is about to export Zhejiang through the direct path ...

. . Exporting table A exports 1216 rows

.

.

.

. . Exporting table Z_invite_comm_pub 147 rows Exported

. Exporting synonyms

. Exporting views

. Exporting stored Procedures

. Exporting operators

. Exporting referential integrity constraints

. Exporting triggers

. Exporting index types

. Exporting bitmaps, functional indexes, and extensible indexes

. Exporting late table activity

. Exporting materialized views

. Exporting snapshot logs

. Exporting job queues

. Exporting refresh groups and subgroups

. Exporting dimension

. Exporting Post-Schema process objects and actions

. Exporting statistics successfully terminated the export without warning.

$ ls-l/data1/zj_direct.dmp

-rw-r--r--1 Oracle Oinstall 18460095405 June 1 21:24/data1/zj_direct.dmp

Direct path import takes 18 minutes, with a significant improvement over the normal path export speed. Finally, let's look at the export speed of the data pump.

Data pump must use directory, set up a directory below, and then perform the export operation:

$ EXPDP Zhejiang/zhejiang DUMPFILE=ZJ_DATAPUMP.DP directory=d_test Schemas=zhejiang

Export:release 10.2.0.3.0-64bit Production on Friday, 01 June, 2007 21:34:08

Copyright (c) 2003, +, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition release 10.2.0.3.0-64bit Production

With the partitioning, real application clusters, OLAP and Data Mining options Start "Zhejiang". Sys_export_schema_01 ": zhejiang/******** dumpfile=zj_datapump.dp directory=d_test Schemas=zhejiang is using the BLOCKS method to estimate ... Handling object Types Schema_export/table/table_data Total estimates using the BLOCKS method: 22.25 GB Processing Object Types Schema_export/pre_schema/procact_schema processing object types Schema_export/synonym/synonym processing Object Types Schema_export/type/type_spec processing object types Schema_export/db_link processing object Types SCHEMA_EXPORT/ Sequence/sequence processing Object Types schema_export/table/table processing object types Schema_export/table/index/index processing object Types schema_export/table /constraint/constraint handles object types schema_export/table/index/statistics/index_statistics processing object Types SCHEMA_EXPORT/TABLE/ Comment Processing object types Schema_export/package/package_spec processing object types schema_export/function/function processing object Types schema_export/ Procedure/procedure handles object types Schema_export/package/compile_package/package_spec/alter_package_spec processing object Types SCHEMA_ Export/function/alter_function handles object types schema_export/procedure/alter_procedure processing object Types Schema_export/package/package _body Treatment ofLike type Schema_export/type/type_body processing object types Schema_export/table/constraint/ref_constraint processing object Types schema_export/table/ Index/functional_and_bitmap/index Processing Object Types Schema_export/table/index/statistics/functional_and_bitmap/index_ STATISTICS handles object types schema_export/table/statistics/table_statistics processing object Types Schema_export/job

. . The "Zhejiang" was exported. Ord_hit_comm "3.255 GB 4176146 Lines

.

.

.

. . The "Zhejiang" was exported. Usr_user_plat_role "0 KB 0 rows have successfully loaded/uninstalled the primary table" Zhejiang "." Sys_export_schema_01 "

******************************************************************************

Zhejiang. The set of dump files for sys_export_schema_01 is:

/DATA1/ZJ_DATAPUMP.DP job "Zhejiang". Sys_export_schema_01 "completed successfully at 21:48:25

From the export information, the data pump export time only 14 minutes, faster than the direct way to import more than 20%. and observe the size of three exported files can be found, the faster the export of the corresponding file is smaller, which data pump export way to get the file than the exp way youngster nearly 1.5G.

$ ls-l/data1/zj_*.d*p

-RW-R-----1 Oracle Oinstall 17066897408 June 1 21:48/DATA1/ZJ_DATAPUMP.DP

-rw-r--r--1 Oracle Oinstall 18460095405 June 1 21:24/data1/zj_direct.dmp

-rw-r--r--1 Oracle Oinstall 18540327936 June 1 19:09/data1/zj_regular.dmp