Oracle 10g Data pump and import Export performance comparison (iii) methods to improve IMPDP import efficiency

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 describes ways to improve the efficiency of IMPDP import.

The test in the previous article found that IMPDP's import speed is comparable to IMP's import speed. When Oracle introduced the data pump, it was mentioned that IMPDP's import speed was 10 times times higher than that of IMP. But fortunately IMPDP can still optimize the adjustment, that is by setting parallel to improve the IMPDP degree of parallelism.

Sql> Show parameter CPU

NAME TYPE VALUE

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

Cpu_count Integer 2

PARALLEL_THREADS_PER_CPU Integer 2

Because the database server has a CPU number of 2, the following attempts to set parallel for import:

$ IMPDP Zhejiang/zhejiang dumpfile=zj_datapump.dp directory=d_test full=y logfile=zj_datapump.log

Import:release 10.2.0.3.0-64bit Production on Wednesday, 06 June, 2007 18:06:19

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

ORA-39154: The object in the external scenario has been removed from the import and the primary table "Zhejiang" has been successfully loaded/unloaded. Sys_import_full_01 "Start" Zhejiang "." Sys_import_full_01 ": zhejiang/******** dumpfile=zj_datapump.dp directory=d_test full=y LOGFILE=zj_datapump.log Parallel=2 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 handles Object Types Schema_export/table/table_data

. . The "Zhejiang" was imported. Ord_order_item "1.890 GB 3226647 Lines

.

.

.

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

. . The "Zhejiang" was imported. Usr_user_plat_role "0 KB 0 Row Processing object type Schema_export/table/index/index processing object type schema_export/table/constraint/ Constraint processing object types schema_export/table/index/statistics/index_statistics processing object types schema_export/table/comment processing object types Schema_export/package/package_spec handles 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 Processing Object Types Schema_export/procedure/alter_procedure

ORA-39082: Object type alter_procedure: "Zhejiang". Pt_enter_factory "created with compile warning

.

.

.

ORA-39082: Object type package_body: "Zhejiang". USR_ROLE$RP "has been created, but with compile warnings to handle object types Schema_export/type/type_body processing object Types Schema_export/table/constraint/ref_ Constraint handles object types Schema_export/table/index/functional_and_bitmap/index processing object Types schema_export/table/index/ Statistics/functional_and_bitmap/index_statistics processing object Types Schema_export/table/statistics/table_statistics Jobs " Zhejiang "." Sys_import_full_01 "has been completed, but there are 34 errors (completed at 20:35:03)

Using the parallel degree of 2 of the import method, found that the speed is indeed improved a lot. The 1-Parallel import speed is 3 hours and 8 minutes, and now takes less than 2.5 hours.

Because the parallelism setting should not exceed twice times the CPU number, try the import of parallelism 3 and 4, which is very close to the import time and the degree of parallelism 2. It seems that there is no longer a way to improve performance by increasing the degree of parallelism.