DataPump Export Runs Very Slow
(Doc ID 1515935.1)
Last updated on AUGUST 04, 2018
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]Information in this document applies to any platform.
***Checked for relevance on 07-Aug-2017***
Symptoms
DataPump export (EXPDP) runs very slow during the phase of exporting indexes. Conventional export (EXP) is faster..
The 10046 trace of worker process shows:
BEGIN
SYS.KUPW$WORKER.MAIN('webia1p_DP_export_12-11-2012-0', 'FDICDBA', 0);
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 22019.32 22112.84 2 1277253 5942320 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 22019.33 22112.85 2 1277253 5942320 1
SQL ID: 0rdu3j1uygp5n Plan Hash: 0
INSERT INTO "FDICDBA"."webia1p_DP_export_12-11-2012-0" (object_type_path,
object_path_seqno, dump_fileid, dump_position,dump_length, dump_orig_length,
dump_allocation, process_order, duplicate, object_row, object_type,
object_schema, original_object_schema, object_name, object_long_name,
original_object_name, partition_name, subpartition_name, object_tablespace,
grantor, flags, processing_state, processing_status, base_process_order,
base_object_type, base_object_schema, base_object_name,
domain_process_order, xml_clob, ancestor_process_order, property, trigflag,
size_estimate, creation_level, parent_process_order, value_n,
object_int_oid, metadata_io)
VALUES
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :13, :16, :17,
:18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32,
:33, :34, :35, :36, :37, :38)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 66129 5.25 5.17 0 0 0 0
Execute 66129 737.13 765.55 305 131891 1202529 114976
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132258 742.38 770.73 305 131891 1202529 114976
SQL ID: acrg0q0qtx3gr Plan Hash: 2515621924
SELECT DBMS_METADATA_UTIL.LONG2CLOB(C.DEFLENGTH, 'SYS.COL$', 'DEFAULT$',
C.ROWID), C.PROPERTY
FROM
SYS.COL$ C WHERE C.OBJ#=:B2 AND C.INTCOL#=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 2571604 351.16 372.86 0 0 0 0
Fetch 2571604 4737.11 5038.46 55 10286416 13095369 2571604
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5143215 5088.27 5411.33 55 10286416 13095369 2571604
SYS.KUPW$WORKER.MAIN('webia1p_DP_export_12-11-2012-0', 'FDICDBA', 0);
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 22019.32 22112.84 2 1277253 5942320 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 22019.33 22112.85 2 1277253 5942320 1
SQL ID: 0rdu3j1uygp5n Plan Hash: 0
INSERT INTO "FDICDBA"."webia1p_DP_export_12-11-2012-0" (object_type_path,
object_path_seqno, dump_fileid, dump_position,dump_length, dump_orig_length,
dump_allocation, process_order, duplicate, object_row, object_type,
object_schema, original_object_schema, object_name, object_long_name,
original_object_name, partition_name, subpartition_name, object_tablespace,
grantor, flags, processing_state, processing_status, base_process_order,
base_object_type, base_object_schema, base_object_name,
domain_process_order, xml_clob, ancestor_process_order, property, trigflag,
size_estimate, creation_level, parent_process_order, value_n,
object_int_oid, metadata_io)
VALUES
(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :13, :16, :17,
:18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32,
:33, :34, :35, :36, :37, :38)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 66129 5.25 5.17 0 0 0 0
Execute 66129 737.13 765.55 305 131891 1202529 114976
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 132258 742.38 770.73 305 131891 1202529 114976
SQL ID: acrg0q0qtx3gr Plan Hash: 2515621924
SELECT DBMS_METADATA_UTIL.LONG2CLOB(C.DEFLENGTH, 'SYS.COL$', 'DEFAULT$',
C.ROWID), C.PROPERTY
FROM
SYS.COL$ C WHERE C.OBJ#=:B2 AND C.INTCOL#=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.00 0.00 0 0 0 0
Execute 2571604 351.16 372.86 0 0 0 0
Fetch 2571604 4737.11 5038.46 55 10286416 13095369 2571604
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5143215 5088.27 5411.33 55 10286416 13095369 2571604
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |