Oracle 10g Data pump and import export performance comparison (IV.) effect of parallelism on data pump export

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 effect of parallelism on the export of data pumps.

Write a few articles before the data pump also know not much, recently read the document found that the parallel settings are also skilled, if set reasonable, you can significantly improve the performance of the export.

First, take a look at the number of CPUs:

Sql> Show parameter CPU

NAME TYPE VALUE

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

Cpu_count Integer 2

PARALLEL_THREADS_PER_CPU Integer 2

According to the description of the document, the degree of parallelism should not be set to exceed twice times the number of CPUs, so the maximum degree of parallelism is set to 4, in contrast to not setting parallel, setting parallelism, setting up only one export file, setting parallelism, and setting the corresponding multiple export files.

First look at the performance of the direct export:

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

Export:release 10.2.0.3.0-64bit Production on Wednesday, 16 January, 2008 22:51:43

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/******** directory=d_test DUMPFILE=ZHEJIANG.DP is using the BLOCKS method to estimate ... Handling object Types Schema_export/table/table_data Total estimates using the BLOCKS method: 22.64 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 Processing Object Types Schema_export/tyPe/type_body processing Object Types Schema_export/table/constraint/ref_constraint processing object types Schema_export/table/trigger processing 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

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

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

. . The "Zhejiang" was exported. Con_list_item "4.505 MB 7985 Lines

. . The "Zhejiang" was exported. Ord_purchase_item "1.281 GB 3238674 Lines

.

.

.

. . The "Zhejiang" was exported. UNC_PRODUCT_MAPTJ "0 KB 0 Lines

. . The "Zhejiang" was exported. Unc_sms_user_new "0 KB 0 Lines

. . The "Zhejiang" was exported. Unc_user_non "0 KB 0 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/ZHEJIANG.DP job "Zhejiang". Sys_export_schema_01 "completed successfully at 23:06:01

The entire export operation was approximately 14 and a half, and the following attempts to export using the degree of parallelism 2 will still set an exported data file:

$ EXPDP Zhejiang/zhejiang directory=d_test DUMPFILE=ZHEJIANG_P2_1FILE.DP parallel=2

Export:release 10.2.0.3.0-64bit Production on Wednesday, 16 January, 2008 23:13:10

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/******** directory=d_test DUMPFILE=ZHEJIANG_P2_1FILE.DP parallel=2 is using the BLOCKS method to estimate ... Handling object Types Schema_export/table/table_data Total estimates using the BLOCKS method: 22.64 GB Processing Object Types Schema_export/pre_schema/procact_schema

. . The "Zhejiang" was exported. Ord_hit_comm "3.255 GB 4176146 Row Processing object Type schema_export/synonym/synonym processing object type Schema_export/type/type_spec processing object type 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 handles object types 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 processing object Types schema_export/ Function/function handles object types schema_export/procedure/procedure processing object Types Schema_export/package/compile_package/package_ Spec/alter_package_spec handles object types schema_export/function/alter_function processing object Types Schema_export/procedure/alter_ Procedure processing object Types Schema_export/package/package_body processing object types schema_export/type/type_body processing object Types schema_export/table/ Constraint/ref_constraint handles object types Schema_export/table/trigger processing object Types Schema_export/table/index/functional_and_ Bitmap/index handles object types schema_export/table/index/statistics/functional_and_bitmap/index_statistics processing object Types SCHEma_export/table/statistics/table_statistics

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