Oracle 11g R1: Poor Data Pump Performance when Exporting a Partitioned Table

(Originally posted on blogs.sun.com at http://blogs.sun.com/mandalika/entry/oracle_11g_r1_poor_data)

Symptom(s)

Data Pump Export utility, expdp, performs well with non-partitioned tables, but exhibits extreme poor performance when exporting objects from a partitioned table of similar size. In some cases the degradation can be as high as 3X or worse.

SQL traces may show that much of the time is being spent in a SQL statement that is similar to:

UPDATE "schema"."TABLE" mtu 
SET mtu.base_process_order = NVL((SELECT mts1.process_order FROM "schema"."TABLE" mts1 
WHERE ..

Here is an example data export session:

Export: Release 11.1.0.7.0 - 64bit Production on Wednesday, 31 March, 2010 6:56:50

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCHMA"."SYS_EXPORT_TABLE_01":  SCHMA/******** DIRECTORY=exp_dir DUMPFILE=SOME_DUMMY_PART_FULL.DMP TABLES=SOME_DUMMY_PART
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.56 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P01"  1.143 GB 13788224 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P02"  1.143 GB 13788224 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P03"  1.143 GB 13788224 rows
...
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P32"  151.1 MB 1789216 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P33"  11.37 MB  136046 rows
. . exported "SCHMA"."SOME_DUMMY_PART":"DUMMY_PART_P00"      0 KB       0 rows
Master table "SCHMA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCHMA.SYS_EXPORT_TABLE_01 is:
  /DBDUMP/SOME_DUMMY_PART_FULL.DMP
Job "SCHMA"."SYS_EXPORT_TABLE_01" successfully completed at 11:22:36

Solution(s) / Workaround

This is a known issue (that is, a bug) and a solution is readily available. Try any of the following to resolve the issue:

• Apply the 11g database patch 8845859
  
• Upgrade to 11.2.0.2 patchset when it is available, or
  
• Specify "VERSION=10.2.0.3" expdp option as a workaround
  

I ran into this issue and I chose the workaround to make some quick progress. With the string "VERSION=10.2.0.3" appended, export time went down from 265 minutes to 60+ minutes.