Slow Datapump Export For Partitioned Tables

(Doc ID 1050907.1)

Last updated on JULY 26, 2016

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 18-Dec-2014***


Export DataPump execution of partitioned tables is slow.

Tkprof of the DataPump worker process trace files generated by the 10046 event confirms that the time is elapsed executing the following query:

UPDATE "schema"."TABLE" mtu SET mtu.base_process_order =
NVL((SELECT mts1.process_order FROM "schema"."TABLE" mts1
mts1.process_order > 0 AND mts1.duplicate = 0 AND mts1.object_schema =
mtu.base_object_schema AND mts1.object_name = mtu.base_object_name AND
mts1.object_type = mtu.base_object_type AND mts1.processing_state != :1 AND
((mts1.partition_name = mtu.partition_name AND mts1.subpartition_name =
mtu.subpartition_name) OR (mts1.partition_name = mtu.partition_name AND
mts1.subpartition_name IS NULL) OR (mts1.partition_name IS NULL AND
mts1.subpartition_name IS NULL))), (-1000 - (SELECT MIN(mts2.process_order)
FROM "schema"."TABLE" mts2 WHERE mts2.process_order > 0 AND
mts2.duplicate = 0 AND mts2.object_schema = mtu.base_object_schema AND
mts2.object_name = mtu.base_object_name AND mts2.object_type =
mtu.object_type))) WHERE mtu.process_order > 0 AND mtu.duplicate = 0 AND
mtu.object_path_seqno = :2 AND mtu.processing_status = :3;



Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms