Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (Doc ID 453895.1)

 DEFECT DETAILS

1. Bug 3447032 - Import Data Pump is slow when importing statistics
   - Defect: Bug 3447032 "DBMS_STATS.SET_COLUMN_STATS can be slow (affects IMPORT)" (not a public bug) 
   - Symptoms: an Import (original client) or Import Data Pump job may show long wait times when importing INDEX_STATISTICS or TABLE_STATISTICS 
   - Releases: 10.1.0.3.0 and lower
   - Fixed in: 10.1.0.4.0 and higher; for some platforms a fix on top of 10.1.0.3.0 is available with Patch:3447032 
   - Patched files: exuazo.o  kustat.xsl
   - Workaround: exclude import of statistics (EXCLUDE=statistics) and manually create the statistics after the import completes
   - Cause: issue how column statistics are set on tables with (many) sub-partitions 
   - Trace: SQL trace shows references to DBMS_STATS package 
   - Remarks: the fix for this bug has to be applied at both sites (source and target database) and any Export or Export Data Pump dumpfile has to be regenerated to get improved performance upon import. 
   .
2. Bug 3508675 - Import Data Pump is slow when importing TABLE_DATA
   - Defect:  Bug 3508675 "APPSST10G: BAD PLAN WHEN QUERYING ALL_POLICIES WHEN IMPORTING TABLE_DATA" (not a public bug)
   - Symptoms:  an impdp job may show high CPU usage and a slow down during the import phase of: TABLE_DATA
   - Releases:  10.1.0.5.0
   - Fixed in:  10.2.0.1.0 and higher; generic fix available for 10.1.0.5.0 with Patch:3508675
   - Patched files:  prvtbpdi.plb
   - Workaround:  none
   - Cause:  introduced with fix for Bug 3369744 ALL_SYNONYMS view does not show synonym for a synonym (not a public bug)
   - Trace:  SQL trace and AWR trace show high CPU usage and execution time for query:
   SELECT count(*) FROM ALL_POLICIES WHERE enable = :y and ins = :y2 and object_name = :tname and object_owner = :sname
   - Remarks:  may show up during impdp job of Oracle Applications database (apps) or any other target database where many tables are imported.
   .
3. Bug 4513695 - Export Data Pump of large table can be very slow when CURSOR_SHARING=SIMILAR 
   - Defect:  Bug:4513695 "Poor performance for SELECT with ROWNUM=1 with literal replacement" 
   - Symptoms:  an export Data Pump job of a large table (100+ Gb) can be much slower (e.g. 24+ hours) than an export with the original exp client
   - Releases:  10.1.0.x and 10.2.0.3.0 and lower 
   - Fixed in:  10.2.0.4.0 and higher; for some platforms a fix on top of 10.2.0.3.0 is available with Patch:5481520  
   - Patched files:  apa.o kko.o kkofkr.o qerco.o 
   - Workaround:  if possible, set CURSOR_SHARING=EXACT before starting the export Data Pump job 
   - Cause:  query optimization issue in Cost Base Optimizer (CBO) when cursor_sharing is set to similar
   - Trace:  Data Pump Worker trace shows very high elapsed fetch time for: "SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ :"SYS_B_0" FROM ... WHERE ROWNUM = :"SYS_B_1"), :"SYS_B_2") FROM DUAL" 
   - Remarks:  a fix for this defect can only be provided as a fix for Bug:5481520 "Wrong results with ROWNUM and bind peeking". 
   .
4. Bug 5071931 - Import Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE is slow
   - Defect:  Bug:5071931 "DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW"
   - Symptoms:  an impdp job with REMAP_SCHEMA and REMAP_TABLESPACE slows down during the import phase of DDL such as: TABLE, INDEX, OBJECT_GRANT
   - Releases:  10.2.0.1.0 to 10.2.0.3.0
   - Fixed in:  10.2.0.4.0 and higher; a generic fix available for 10.2.0.3.0 with Patch:5071931 and for some platforms a fix on top of lower releases is also available with the same number
   - Patched files:  prvtmeti.plb
   - Workaround:  if not required, do not use the REMAP_% parameters
   - Cause:  problem when multiple transforms are chained together
   - Trace:  Data Pump Worker trace shows high elapsed times between "DBMS_METADATA.CONVERT called" and "DBMS_METADATA.CONVERT returned"
   - Remarks:  this defect does not reproduce in Oracle10g Release 1; for details, see also:
   Note:429846.1 "Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters".
   .
5. Bug 5095025 - Export Data Pump runs out of memory (ORA-4030) when exporting many schema's
   - Defect:  Bug 5095025 "ORA-4030 (KXS-HEAP-C,TEMPORARY MEMORY) USING EXPDP" (not a public bug)
   - Symptoms: a schema level expdp job of many schema's (like 50+) and where procedural objects are involved (like schema jobs), may fail due to running out of PGA (leaking memory) when exporting procedural objects
   - Releases:  10.1.0.x and 10.2.0.3.0 and lower
   - Fixed in:  10.2.0.4.0 and higher
   - Patched files:  (in patchset)
   - Workaround:  if possible, run multiple export Data Pump jobs so every job has fewer schema's to export 
   - Cause:  query optimization (Rule Based Optimizer (RBO) instead of Cost Base Optimizer (CBO))
   - Trace:  ORA-4030 and Data Pump Worker trace may show reference to: "SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PROCDEPOBJ_T', ..."
   - Remarks:  also related to this defect are: Bug:5464834 and Bug:5928639 and Bug 5929373 (not a public bug). 
   .
6. Bug 5292551 - Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
   - Defect:  Bug:5292551 "IMPDP VERY SLOW WHEN IMPORTING A TABLE WITH INITIALIZED COLUMN OF TYPE VARRAY" 
   - Symptoms:  an impdp job of specific tables (like tables with Spatial data MDSYS.SDO_GEOMETRY) can be very slow when importing table data and the Data Pump worker process shows a continuous increase of memory when loading those tables
   - Releases:  10.1.0.x and 10.2.0.3.0 and lower 
   - Fixed in:  10.2.0.4.0 and higher; for some platforms a fix on top of 10.2.0.3.0 is available with Patch:5292551 
   - Patched files:  kpudp.o 
   - Workaround:  if possible, exclude those tables: EXCLUDE=TABLE:"in('TAB_NAME', ...) and import those tables separately in a second table level import Data Pump job: TABLES=owner.tab_name
   - Cause:  memory was not released, resulting in high amount of allocated memory
   - Trace:  Heapdumps show many freeable chunks 'freeable assoc with marc' or 'klcalh:ld_hds'
   - Remarks:  the impdp job may fail after running for days with errors such as ORA-4030 (out of process memory when trying to allocate xxx bytes) or or ORA-31626 (job does not exist) or internal error ORA-00600 [729], [12432], [space leak].
   .
7. Bug 5464834 - Export Data Pump runs out of memory (ORA-4030) when many tables are involved
   - Defect:  Bug:5464834 "ORA-4030 (KXS-HEAP-C,TEMPORARY MEMORY) USING EXPDP"
   - Symptoms:  a table level expdp job of many tables (like 250+) may fail due to running out of PGA (leaking memory) when exporting table data
   - Releases:  10.1.0.x and 10.2.0.3.0 and lower 
   - Fixed in:  10.2.0.4.0 and higher; generic fix available for 10.1.0.4.0 and 10.2.0.3.0 with Patch:5464834
   - Patched files:  catmeta.sql  prvtmeti.plb
   - Workaround:  if possible, run multiple export Data Pump jobs so every job has fewer tables to export
   - Cause:  query optimization (Rule Based Optimizer (RBO) instead of Cost Base Optimizer (CBO))
   - Trace:  ORA-4030 and Data Pump Worker trace may show reference to: "SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_DATA_T', ..." 
   - Remarks:  also related to this defect are: Bug 5095025 (not a public bug) and Bug:5928639 and Bug 5929373 (not a public bug). 
   .
8. Bug 5555463 - Import Data Pump can be slow when importing small LOBs (under 256K) 
   - Defect:  Bug 5555463 "PERFORMANCE ISSUES FOR DATAPUMP IMPORT/EXTERNAL_TABLE MODE OF TABLES WITH LOBS" (not a public bug)
   - Symptoms:  slow performance, high CPU usage, and LOB redo generation when importing table with small LOBs (LOBs smaller than 256 kb)
   - Releases:  10.1.0.x and 10.2.0.3.0 and lower 
   - Fixed in:  10.2.0.4.0 and higher
   - Patched files:  (in patchset)
   - Workaround:  none (if possible, run load in Direct Path mode: ACCESS_METHOD=DIRECT_PATH)
   - Cause:  using temporary LOBs when loading data in External Table mode 
   - Trace:  (details not available) 
   - Remarks:  an impdp job of the same table data in Direct Path mode shows much faster performance. 
   .
9. Bug 5590185 - Consistent Export Data Pump is slow when exporting row data
   - Defect:  Bug:5590185 "CONSISTENT EXPORT DATA PUMP JOB (FLASHBACK_TIME) HAS SLOWER PERFORMANCE"
   - Symptoms:  an expdp job of large amount of tables is slow when using FLASHBACK_TIME or FLASHBACK_SCN or when logical standby or Streams are used
   - Releases:  10.1.0.x and 10.2.0.3.0 and lower
   - Fixed in:  10.2.0.4.0 and higher; for some platforms a fix on top of 10.2.0.2.0 is available with Patch:5590185
   - Patched files:  prvtbpm.plb
   - Workaround:  if not required, do not run a consistent Export Data Pump job
   - Cause:  full table scans on DataPump's Master table
   - Trace:  SQL trace shows execution time for statement:
   UPDATE "SYSTEM"."SYS_EXPORT_SCHEMA_01" SET scn = :1, flags = :2 WHERE (object_path_seqno = :3) AND (base_process_order = :4) AND (process_order > 0)
   - Remarks:  If a normal expdp job takes 1 hour, then the same job but now consistent, may take more than 8 hours. 
   .
10. Bug 5928639 - Export Data Pump can be very slow if CURSOR_SHARING is not EXACT
    - Defect:  Bug:5928639 "DATAPUMP EXPORT SLOW WHEN CURSOR_SHARING is not EXACT" 
    - Symptoms:  an export Data Pump job can be slow if many tables are involved and init.ora or spfile parameter CURSOR_SHARING is not set to EXACT
    - Releases:  10.1.0.x and 10.2.0.3.0 and lower 
    - Fixed in:  10.2.0.4.0 and higher with fix for Bug:5464834 (see above) 
    - Patched files:  catmeta.sql prvtmeti.plb 
    - Workaround:  set spfile parameter CURSOR_SHARING=EXACT
    - Cause:  query optimization (Rule Based Optimizer (RBO) instead of Cost Base Optimizer (CBO))
    - Trace:  Worker trace file shows high waits for DBMS_METADATA.FETCH_XML_CLOB called, and SQL trace file shows reference to: "SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_DATA_T', ..." 
    - Remarks:  also related to this defect are: Bug 5095025 (not a public bug) and Bug:5464834 and Bug 5929373 (not a public bug).
    .
11. Bug 5929373 - Export Data Pump of a table can be very slow if database has many user tables 
    - Defect:  Bug 5929373 "APPS ST GSI - DATA PUMP TAKES LONG TIME TO EXPORT DATA" (not a public bug)
    - Symptoms:  an export Data Pump job of a small table can be slow if database has many user tables
    - Releases:  10.1.0.x and 10.2.0.3.0 and lower 
    - Fixed in:  10.2.0.4.0 and higher with fix for Bug:5464834 (see above) 
    - Patched files:  catmeta.sql prvtmeti.plb 
    - Workaround:  none
    - Cause:  query optimization (Rule Based Optimizer (RBO) instead of Cost Base Optimizer (CBO))
    - Trace:  Worker trace file shows high waits for DBMS_METADATA.FETCH_XML_CLOB called, and SQL trace file shows reference to: "SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_DATA_T', ..." 
    - Remarks:  Data Pump may need more than an hour for the table, while the original export client finishes in a couple of minutes; also related to this defect are: Bug 5095025 (not a public bug) and Bug:5464834 and Bug:5928639.
12. Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
    - Defect: Bug 7722575 "DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP"
    - Symptoms: The definition of datapump views KU$_NTABLE_DATA_VIEW and
    KU$_NTABLE_BYTES_ALLOC_VIEW can lead to a suboptimal execution plans and poor performance of queries against the view from Datapump export
    - Releases: 10.2.0.x and 11.1.0.X
    - Fixed in: 10.2.0.5.0 and 11.2
    - Patched files: catmeta.sql 
    - Workaround: none
    - Cause: incorrect definition of  ku$_ntable_data_view Datapump view
    - Trace: The SQL trace file shows an expensive execution plan for :
    SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$),  XMLFORMAT.createFormat2('TABLE_DATA_T', '7')), 0 ,KU$.BASE_OBJ.NAME , ...
    FROM SYS.KU$_TABLE_DATA_VIEW KU$ WHERE ......
13. Bug 10178675 - expdp slow with processing functional_and_bitmap/index
    - Defect: Bug 10178675 "expdp slow with processing functional_and_bitmap/index"
    - Symptoms: EXPDP shows a long time on the message:
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
    - Releases: 10.2.0.4, 11.1.0.7, 11.2.0.1, 11.2.0.2
    - Fixed in: 11.2.0.3, 12.1
    - Patched files: prvtmeta.plb, prvtmeti.plb
    - Workaround: none
    - Cause: While exporting domain index, the view ku$_2ndtab_info_view is internally used. With RBO, select on this view is generating bad plan and consuming more time.
    - Trace: Expdp worker (DW) shows a lot of time spent executing a SQL of the form:
    SELECT INDEX_NAME, INDEX_SCHEMA, TYPE_NAME, TYPE_SCHEMA, FLAGS FROM SYS.KU$_2NDTAB_INFO_VIEW WHERE OBJ_NUM=:B1
14. Bug 10194031 - EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
    - Defect: Bug 10194031 - EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
    - Symptoms: Exporting tables that contain XMLTYPE columns runs very slow before raising ORA-4030 error. This happens when trying to export and entire user or an individual table. 
    - Releases: 11.2.0.1, 11.2.0.2
    - Fixed in: 11.2.0.3, 12.1
    - Workaround: none
    - Cause: Memory leak running expdp on tables containing xmltype data
15. Bug 8904037 - LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS 
    - Defect: Bug 8904037 - LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS 
    - Symptoms: The export may appear to take a long time while processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
    - Releases: 11.1.0.7, 11.2.0.1
    - Fixed in: 11.2.0.2, 12.1
    - Workaround: Remove the Workspace Manager option
    - Cause:new function "setCallStackAsValid" in 11.1.0.7
     

Additional Resources

Community: Database Utilities

Still have questions? Use the above community to search for similar discussions or start a new discussion on this subject.