Oracle 10g Data pump and import export performance comparison (v) The biggest factor affecting the import performance of data pump

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.

This article discusses the most important factors that affect the performance of data pump imports.

Several articles have been written to introduce the performance of Exp/imp and EXPDP/IMPDP, respectively, according to the description of the previous articles, if not using parallelism, it seems that IMPDP efficiency than IMP does not have an order of magnitude hint. This is true for the current environment. However, there is no previous description of an important factor, of course, because the database environment is configured with the standby database, not only in the archive mode, but also set the Force LOGGING:

Sql> SELECT NAME, Log_mode, force_logging from V$database;

NAME Log_mode for

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

Testrac Archivelog YES

And the biggest advantage of data pump is direct path import, the following closes force LOGGING, and the database as to non archive mode, check data pump import efficiency.

Export operations:

bash-2.03$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG.DP

Export:release10.2.0.3.0-64bit Production on Monday, 20 April, 2009 0:51:13

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

Connect to: Oracle database10genterprise Edition release10.2.0.3.0-64bit Production

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

Start "Zhejiang". Sys_export_schema_01 ": zhejiang/******** directory=d_test DUMPFILE=ZHEJIANG.DP

Using the blocks method to estimate ...

Working with Object Types Schema_export/table/table_data

Total estimates using the Blocks method: 22.64 GB

Working with Object Types Schema_export/pre_schema/procact_schema

Working with Object Types schema_export/synonym/synonym

Working with Object Types Schema_export/type/type_spec

Working with Object Types Schema_export/db_link

Working with Object Types Schema_export/sequence/sequence

Working with Object Types schema_export/table/table

Working with Object Types Schema_export/table/index/index

Working with Object Types Schema_export/table/constraint/constraint

Working with Object Types Schema_export/table/index/statistics/index_statistics

Working with Object Types Schema_export/table/comment

Working with Object Types Schema_export/package/package_spec

Working with Object Types Schema_export/function/function

Working with Object Types Schema_export/procedure/procedure

Working with Object Types Schema_export/package/compile_package/package_spec/alter_package_spec

Working with Object Types Schema_export/function/alter_function

Working with Object Types Schema_export/procedure/alter_procedure

Working with Object Types Schema_export/package/package_body

Working with Object Types Schema_export/type/type_body

Working with Object Types Schema_export/table/constraint/ref_constraint

Working with Object Types Schema_export/table/trigger

Working with Object Types Schema_export/table/index/functional_and_bitmap/index

Working with Object Types Schema_export/table/index/statistics/functional_and_bitmap/index_statistics

Working with Object Types Schema_export/table/statistics/table_statistics

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

. . "Zhejiang" was exported. Ord_order_item "1.890 GB 3226647 Lines

.

.

.

. . "Zhejiang" was exported. Unc_user_non "0 KB 0 Lines

. . "Zhejiang" was exported. Usr_user_plat_role "0 KB 0 Lines

The primary table "Zhejiang" was successfully loaded/unloaded. Sys_export_schema_01 "

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

Zhejiang. The set of dump files for sys_export_schema_01 is:

/data1/backup/zhejiang.dp

The job "Zhejiang". Sys_export_schema_01 "completed successfully at 01:04:48

The export spents 13 minutes and 35 seconds.

The following delete Zhejiang users, restart the database, and the database as a non-archive mode, while removing the database force logging settings:

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