|
针对数据泵导出 (expdp) 和导入 (impdp)工具性能降低问题的检查表 (文档 ID 1549185.1) 文档内容 适用于:Oracle Database – Enterprise Edition – 版本 10.1.0.2 到 12.1.0.2 [发行版 10.1 到 12.1] 用途本文档提供了有关使用数据泵导入导出工具传输数据时所遇到的性能相关问题的可能原因。 适用范围本文的目标受众是 Oracle10g 和 Oracle11g 数据库的用户，并且使用 Export Data pump 工具从 Oracle 源数据库中导出数据，并使用 Import Data pump 工具将这些数据导入到 Oracle 目标数据库中。本文档仅适用于新的 Export Data Pump (expdp) 和 Import Data Pump (impdp) 客户端，不适用于原始的导出 (exp) 和导入 (imp) 客户端。对于 Oracle10g 及更高版本，我们建议使用数据泵在 Oracle 数据库之间传输数据。 详细信息简介从版本 10g (10.1.0) 开始，Oracle 引入了新的 Oracle 数据泵技术，通过该项技术，用户能够以极快的速度将数据和元数据从一个数据库移动到另一个数据库。此项技术是 Oracle 新的数据移动工具（“Export Data pump”和“Import Data pump”）的基础。 在某些情况下，使用数据泵客户端卸载或加载数据时，可能会遇到性能问题。本文档将提供有关安装和配置设置的详细信息，这些设置可能会对数据泵客户端的性能产生影响；还将提供有关如何检查数据泵在某一特定时刻正在进行哪些操作的详细信息；此外，还将讨论一些会对性能产生影响的已知缺陷。 参数在此部分列出了可能会对数据泵导出或导入作业的性能产生影响的数据泵参数。此外，还列出了一些通用数据库参数 (init.ora/spfile)，我们已知这些参数可能会对数据泵作业产生影响。
检查数据泵的活动已知缺陷概述下面概述了各个 Oracle10g 和 Orace11g 版本中已知的性能相关缺陷。请参阅概述之后的内容部分，以了解有关这些缺陷和可能的变通方案的详细信息。 注意 1：除了数据泵特定的缺陷，其它组件例如与优化器相关的缺陷也会在数据泵作业期间对性能产生影响。下面仅列出了一些影响最大的缺陷。 注意 2：使用指定的 NETWORK_LINK 参数执行导入时，影响 Export Data Pump 的缺陷也会对 Import Data Pump 产生影响。这些缺陷只在 Export Data Pump 部分列出一次。 Export DataPump (expdp):
10.1.0.1.0 至 10.1.0.3.0 – Bug 3447032 – Import Data Pump is slow when importing statistics – Bug:4513695 – Poor performance for SELECT with ROWNUM=1 with literal replacement – Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s – Bug:5464834 – Export Data Pump runs out of memory (ORA-4030) when many tables are involved – Bug:5590185 – Consistent Export Data Pump is slow when exporting row data – Bug:5928639 – Export Data Pump of table can be very slow if CURSOR_SHARING is not EXACT – Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables10.1.0.4.0 至10.1.0.5.0 以及 10.2.0.1.0 至 10.2.0.3.0 – Bug:4513695 – Poor performance for SELECT with ROWNUM=1 with literal replacement – Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s – Bug:5464834 – Export Data Pump runs out of memory (ORA-4030) when many tables are involved – Bug:5590185 – Consistent Export Data Pump is slow when exporting row data – Bug:5928639 – Export Data Pump of table can be very slow if CURSOR_SHARING is not EXACT – Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables – Bug 5573425 – Slow Datapump with wrong results due to subquery unnesting and complex view10.2.0.4.0 – Bug 7413726 – Poor EXPDP performance when db COMPATIBLE=10.2.0.3 or 10.2.0.4 (duplicate of Bug 7710931) – Bug 7710931 – DataPump export is extremely slow when extracting schema – Bug 6460304 – (affects earlier versions as well) Expdp domain index dump using RULE Optimizer and slow – Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp 11.1.0.6.0 11.1.0.7.0 11.2.0.1 11.2.0.3 Note:: 2) 3) Note: 12.1.0.1 12.1.0.2 Note: Import DataPump (impdp): 10.1.0.1.0 至 10.1.0.3.0 10.1.0.4.0 10.1.0.5.0 10.2.0.1.0 至 10.2.0.3.0 10.2.0.4.0 11.1.0.6.0 11.1.0.7.0 11.2.0.2 11.2.0.3 11.2.0.4 12.1.0.1 缺陷详细信息
对于11.2.0.3, patch 16038089 中包含了以下修复: 参考NOTE:1290574.1 – Datapump Performance Issue With Content=Metadata_only BUG:5292551 – IMPDP VERY SLOW WHEN IMPORTING A TABLE WITH INITIALIZED COLUMN OF TYPE VARRAY NOTE:331221.1 – 10g Export/Import Process for Oracle Applications Release 11i BUG:7722575 – DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP BUG:8363441 – VERY EXPENSIVE SQL STATEMENT DURING DATAPUMP IMPORT WITH MANY SUBPARTITIONS NOTE:223730.1 – Automatic PGA Memory Management BUG:7710931 – DATAPUMP EXPORT IS EXTREMELY SLOW WHEN EXTRACTING SCHEMA |
Checklist For Slow Performance Of DataPump Export (expdp) And Import (impdp) (文档 ID 453895.1)
APPLIES TO:
Oracle Database – Enterprise Edition – Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
PURPOSE
This document provides information about possible causes of performance related problems when using export DataPump and Import DataPump to transfer data from an Oracle database.
SCOPE
The article is intended for users of the Oracle10g and Oracle11g database who use the Export Data Pump utility to export data from an Oracle source database and the Import Data Pump utility to import into an Oracle target database. This document is only applicable to the new clients Export Data Pump (expdp) and Import Data Pump (impdp) and does not apply to the original export (exp) and import (imp) clients. For Oracle10g and higher, we recommend the usage the Data Pump to transfer data between Oracle databases.
DETAILS
INTRODUCTION
Starting with release 10g (10.1.0), Oracle introduced the new Oracle Data Pump technology, which enables very high-speed movement of data and metadata from one database to another. This technology is the basis for Oracle’s new data movement utilities, Data Pump Export and Data Pump Import.
Under certain circumstances, a performance problem may be seen when unloading or loading data with the Data Pump clients. This document will provide details about setup and configuration settings that may have an impact on the performance of the Data Pump clients; will provide details how to check what Data Pump is doing at a specific moment; and will discuss some known defects that have an impact on the performance.
PARAMETERS
In this section, the Data Pump parameters are listed that may have an impact on the performance of an Export DataPump or import DataPump job. There are also some generic database parameters (init.ora / spfile) listed that are known to have a possible impact of the Data Pump jobs.
If you experience and need to resolve a Data Pump performance issue, and one or more of following parameters are used for the job, then first check the remarks below and see whether this performance problem reproduces if the parameter is not used, or used differently.
- Data Pump parameter: PARALLEL
…
For details, see also:
Note:365459.1 “Parallel Capabilities of Oracle Data Pump”
. - Data Pump parameter: DUMPFILE
…
. - Export Data Pump parameter: ESTIMATE
…
For details about the Export Data Pump parameter ESTIMATE, see also:
Note.786165.1 “Understanding the ESTIMATE and ESTIMATE_ONLY parameter in Export DataPump”
. - Export Data Pump parameters: FLASHBACK_SCN and FLASHBACK_TIME
…
. - Import Data Pump parameter: TABLE_EXISTS_ACTION
…
. - Import Data Pump parameters: REMAP_SCHEMA or REMAP_TABLESPACE
…
For details related to this issue, see also the section “Defects Details” below, and:
Note:429846.1 “Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters”
. - Database parameter: CURSOR_SHARING
…
For details related to this issue, see also the section “Defects Details” below, and:
Note:94036.1 “Init.ora Parameter “CURSOR_SHARING” Reference Note”
Note:421441.1 “Datapump Import With dblink Going Slow With cursor_sharing Set to ‘force'”
. - Export/Import Data Pump parameter: STATUSMonitoring an in progress Data Pump job. This status information is written only to your standard output device, not to the log file (if one is in effect).
CHECK ACTIVITY OF DATA PUMP
KNOWN DEFECTS OVERVIEW
Below an overview of known performance related defects in the various Oracle10g and Orace11g releases. See the next section after the overview for details about these defects and possible workarounds.
Note 1: besides a Data Pump specific defect, there may also be a defect in a different area such as an optimizer related defect, which also has an impact on the performance during a Data Pump job. Only defects with highest impact have been listed below.
Note 2: Defects that have an impact on the performance of Export Data Pump, will also have an impact on import Data Pump when import is done with the NETWORK_LINK parameter specified. Those defects are listed only once at the Export Data Pump section.
10.1.0.1.0 to 10.1.0.3.0
– Bug 3447032 – Import Data Pump is slow when importing statistics
– Bug:4513695 – Poor performance for SELECT with ROWNUM=1 with literal replacement
– Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s
– Bug:5464834 – Export Data Pump runs out of memory (ORA-4030) when many tables are involved
– Bug:5590185 – Consistent Export Data Pump is slow when exporting row data
– Bug:5928639 – Export Data Pump of table can be very slow if CURSOR_SHARING is not EXACT
– Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables10.1.0.4.0 to 10.1.0.5.0 and 10.2.0.1.0 to 10.2.0.3.0
– Bug:4513695 – Poor performance for SELECT with ROWNUM=1 with literal replacement
– Bug 5095025 – Export Data Pump runs out of memory (ORA-4030) when exporting many schema’s
– Bug:5464834 – Export Data Pump runs out of memory (ORA-4030) when many tables are involved
– Bug:5590185 – Consistent Export Data Pump is slow when exporting row data
– Bug:5928639 – Export Data Pump of table can be very slow if CURSOR_SHARING is not EXACT
– Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables
– Bug 5573425 – Slow Datapump with wrong results due to subquery unnesting and complex view
10.2.0.4.0
– Bug 7413726 – Poor EXPDP performance when db COMPATIBLE=10.2.0.3 or 10.2.0.4 (duplicate of Bug 7710931)
– Bug 7710931 – DataPump export is extremely slow when extracting schema
– Bug 6460304 – (affects earlier versions as well) Expdp domain index dump using RULE Optimizer and slow
– Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
11.1.0.6.0
– Bug 7585314 – OCSSD.BIN consumes much too much CPU while running Datapump
– Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
11.1.0.7.0
– Bug 8363441 – Very Expensive Sql Statement During Datapump Import With Many Subpartitions
– Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
– Bug 8904037 – LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS
11.2.0.1
– Bug 10178675 – expdp slow with processing functional_and_bitmap/index
– Bug 10194031 – EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
– Bug 8904037 – LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS
11.2.0.3
– Unpublished Bug 12780993 DATA PUMP PERFORMANCE FOR ESTIMATE=STATISTICS IN EXPORT IS BAD
– Bug 13573203 – SLOW INDEX EXPORT DUE TO PERFORMANCE ISSUE WITH METADATA KU$_INDEX_COL_VIEW
– Bug 13914808 – QUERY AGAINST KU$_INDEX_VIEW KU$ SLOW EVEN AFTER USING METADATA FROM 13844935
– Bug 14192178 – EXPDP of partitioned table can be slow
– Bug 14794472 – EXPDP TOO SLOW HAVING TOO MANY TABLESPACES
– Bug 16138607 – SLOW EXPDP AFTER 11.2.0.3 UPGRADE
– Bug 16298117 – TTS EXPDP TAKING 26 HOURS TO COMPLETE, MOST OF TIME PROCESSING INDEX INFO
– Bug 16856028 – EXPORT DATAPUMP SLOW ON DATAGUARD STANDBY INSTANCE
– Bug 18793246 – EXPDP slow showing base object lookup during datapump export causes full table scan per object
– Bug 20446613 – EXPORTING NON-STREAMS TABLE FROM STRADMIN SCHEMA OVER NETWORK LINK IS SLOW
– Bug 20236523 – DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY
Note:
1)
For 11.2.0.3, also MLR Patch 16038089 is available which includes next fixes:
– Bug 12325243 – SLOW PERFORMANCE ON EXPDP FUNCTIONAL AND BITMAP INDEXES
– Unpublished Bug 12780993 – DATA PUMP PERFORMANCE FOR ESTIMATE=STATISTICS IN EXPORT IS BAD
– Bug 13573203 – SLOW INDEX EXPORT DUE TO PERFORMANCE ISSUE WITH METADATA KU$_INDEX_COL_VIEW
– Bug 13844935 – QUERY AGAINST KU$_INDEX_VIEW SLOW IN 11.2.0.3
– Bug 14192178 – BUG 14006804 FIX DOES NOT RESOLVE THE PERFORMANCE ISSUE
2)
There is now a better fix available. MLR Patch 15893700 is available for 11.2.0.3 and MLR Patch 14742362 is available for versions 11.2.0.3.3 or higher. These are better options than Patch 16038089 because they contain the same fixes as 16038089 and some additional ones and they address the performance issues which are present with Patch 16038089.
3)
All 8 bugs which are fixed with Patch 14742362 are also fixed in patch set 11.2.0.4.
Please refer to
Note 1562142.1 – 11.2.0.4 Patch Set – List of Bug Fixes by Problem Type
11.2.0.4
– Bug 14794472 – EXPDP TOO SLOW HAVING TOO MANY TABLESPACES
– Bug 16856028 – EXPORT DATAPUMP SLOW ON DATAGUARD STANDBY INSTANCE
– Bug 18469379 – Data pump export estimate phase takes a long time to determine if table is empty
– Bug 18793246 – EXPDP slow showing base object lookup during datapump export causes full table scan per object
– Bug 19674521 – EXPDP takes a long time when exporting a small table
– Bug 20111004 – “COMMENT ON COLUMN” statement waits 1 second on “Wait for Table Lock”
– Bug 20236523 – DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY
– Bug 20548904 – EXPDP HANG IN METADA_ONLY ON A PARTITION TABLE WITH AROUND 40000 SUBPARTITIONS
– Bug 20446613 – EXPORTING NON-STREAMS TABLE FROM STRADMIN SCHEMA OVER NETWORK LINK IS SLOW
– Bug 24560906 – HIGH CPU USAGE FOR PROCESS ORA_Q001_DBT11 AND ORA_Q007_DBT11
Note:
MLR Patch 20883577 released on top of 11.2.0.4 contains the fixes for the bugs: 18469379, 18793246, 19674521, 20236523 and 20548904
or next merge patch including the above:
MLR Patch 21443197 released on top of 11.2.0.4 contains the fixes for the bugs: 18082965 18469379 18793246 20236523 19674521 20532904 20548904
12.1.0.1
– Bug 18469379 – Data pump export estimate phase takes a long time to determine if table is empty
– Bug 18793246 – EXPDP slow showing base object lookup during datapump export causes full table scan per object
– Unpublished Bug 18720801 – DATAPUMP EXPORT IS SLOW DUE TO EXPORT OF SYNOPSES
– Bug 20111004 – “COMMENT ON COLUMN” statement waits 1 second on “Wait for Table Lock”
12.1.0.2
– Bug 18793246 – EXPDP slow showing base object lookup during datapump export causes full table scan per object
– Bug 20236523 – DATAPUMP EXPORT SLOW USING CONTENT=METADATA_ONLY
– Bug 20548904 – EXPDP HANG IN METADA_ONLY ON A PARTITION TABLE WITH AROUND 40000 SUBPARTITIONS
– Bug 21128593 – UPDATING THE MASTER TABLE AT THE END OF DP JOB IS SLOW STARTING WITH 12.1.0.2
– Bug 24560906 – HIGH CPU USAGE FOR PROCESS ORA_Q001_DBT11 AND ORA_Q007_DBT11
– Bug 20636003 – Slow Parsing caused by Dynamic Sampling (DS_SVC) queries (side effects possible ORA-12751/ ORA-29771)
Note:
MLR Patch 20687195 released on top of 12.1.0.2 contains the fixes for the bugs: 18793246, 20236523 and 20548904
MLR Patch 21554480 released on top of 12.1.0.2 contains the fixes for the bugs: 18793246, 20236523, 20548904 and 21128593.
Import DataPump (impdp):
10.1.0.1.0 to 10.1.0.3.0
– Bug 3447032 – Import Data Pump is slow when importing statistics
– Bug:5292551 – Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
– Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode
10.1.0.4.0
– Bug:5292551 – Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
– Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode
10.1.0.5.0
– Bug 3508675 – Import Data Pump is slow when importing TABLE_DATA
– Bug:5292551 – Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
– Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode
10.2.0.1.0 to 10.2.0.3.0
– Bug:5071931 – Import Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE is slow
– Bug:5292551 – Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
– Bug 6989875 -Transportable Tablespace Import Spins Using CPU
– Bug 5555463 – Import Data Pump can be slow when importing small LOBs in External Table mode
10.2.0.4.0
– Bug 7439689 – (affects earlier versions as well) Impdp workeer process spinning on MERGE statement
11.1.0.6.0
– Bug 7585314 – OCSSD.BIN consumes much too much CPU while running Datapump
11.1.0.7.0
– Bug 8363441 – Very Expensive Sql Statement During Datapump Import With Many Subpartitions
11.2.0.2
– Bug 13609098 – IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW
– Bug 16396856 – TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU
11.2.0.3
– Bug 13609098 – IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW
– Bug 14834638 – Import slow on create partitioned index
– Bug 16396856 – TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU
– Bug 19520061 – IMPDP: EXTREMELY SLOW IMPORT FOR A PARTITIONED TABLE
– Bug 20532904 DATAPUMP SLOW FOR PARTITIONED TABLE
– Bug 14192178 – EXPDP of partitioned table can be slow
Note: The fix for expdp Bug 14192178 helps for some IMPDP / import operations and some DBMS_METADATA queries.
11.2.0.4
– Bug 13609098 – IMPORTING SMALL SECUREFILE LOBS USING DATA PUMP IS SLOW
– Bug 19520061 – IMPDP: EXTREMELY SLOW IMPORT FOR A PARTITIONED TABLE
12.1.0.1
– Bug 16396856 – TTS IMPDP SEEMS TO HANG AND CONSUME 100% CPU
12.1.0.2
– Bug 24423416 – IMPDP FOR SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY TAKES HOURS
NOTE:
=====
When running the post install step of Generic DataPump patch in 12c Multitenant environment, you may be affected by Bug 23321125 – “DPLOAD DOESN’T CREATE THE SHARED OBJECTS ACROSS ALL PDBS”.
For details and solution, please review:
Note 2175021.1 – Alert – Multitenant Customers: The objects created by the post-install steps of 12.1.0.2 Generic DataPump Patches Are not Shared Across All PDBS.
- 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.
. - 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.
. - 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”.
. - 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”.
. - 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:5928639and Bug 5929373 (not a public bug).
. - 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].
. - 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).
. - 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.
. - 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.
. - 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).
. - 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. - 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 …… - 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 - 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 - 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.
REFERENCES
BUG:7585314 – OCSSD.BIN CONSUMING 6 TIMES MORE CPU IF EXCESSIVE DATAPUMP IS RUNNING ON NODE
NOTE:1673445.1 – EXPDP Estimate Phase Takes a Long Time With 12.1.0.1
NOTE:1290574.1 – Datapump Performance Issue With Content=Metadata_only
BUG:7710931 – DATAPUMP EXPORT IS EXTREMELY SLOW WHEN EXTRACTING SCHEMA
BUG:5928639 – DATAPUMP EXPORT SLOW WHEN CURSOR_SHARING != EXACT
BUG:4513695 – SELECT WITH ROWNUM=1 PERFORMANCE IS TOO LATE USING CURSOR_SHARING=SIMILAR
NOTE:885388.1 – DataPump Export Is Slow After Upgrade To 11g When Workspace Manager Is Installed
NOTE:223730.1 – Automatic PGA Memory Management
BUG:7439689 – IMPDP HANGS ON IDLE EVENT ‘WAIT FOR UNREAD MESSAGE ON BROADCAST CHANNEL’
NOTE:277905.1 – Export/Import DataPump Parameter TABLES – How to Export and Import Tables Residing in Different Schemas
NOTE:429846.1 – Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters
BUG:7413726 – POOR EXPDP PERFORMANCE WHEN DB COMPATIBLE=10.2.0.3 OR 10.2.0.4
BUG:5996665 – EXPDP HANGING MORE THAN 5 HOURS
BUG:5071931 – DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW
BUG:6460304 – EXPDP TAKES MORE TIME
BUG:20446613 – EXPORTING NON-STREAMS TABLE FROM STRADMIN SCHEMA OVER NETWORK LINK IS SLOW
NOTE:286496.1 – Export/Import DataPump Parameter TRACE – How to Diagnose Oracle Data Pump
NOTE:362205.1 – 10g Release 2 Export/Import Process for Oracle Applications Release 11i
NOTE:365459.1 – Parallel Capabilities of Oracle Data Pump
BUG:5590185 – CONSISTENT EXPORT DATA PUMP JOB HAS SLOWER PERFORMANCE
BUG:10178675 – EXPDP SLOW WITH PROCESSING FUNCTIONAL_AND_BITMAP/INDEX
NOTE:155477.1 – Parameter DIRECT: Conventional Path Export Versus Direct Path Export
NOTE:2175021.1 – Alert – Multitenant Customers: The objects created by the post-install steps of 12.1.0.2 Generic DataPump Patches Are not Shared Across All PDBS
BUG:8363441 – VERY EXPENSIVE SQL STATEMENT DURING DATAPUMP IMPORT WITH MANY SUBPARTITIONS
BUG:5573425 – NON-CORRELATED SUBQUERY RETURNS WRONG RESULTS, LIKE A CARTESIAN JOIN
NOTE:14834638.8 – Bug 14834638 – IMPDP import slow on create partitioned index
BUG:5464834 – ORA-4030 USING EXPDP
BUG:5481520 – WRONG RESULTS WITH ROWNUM AND BIND PEEKING
NOTE:94036.1 – Init.ora Parameter “CURSOR_SHARING” Reference Note
BUG:6807289 – IMPDP WITH REMAP_SCHEMA AND REMAP_TABLESPACE HANGS AT TABLE STATISTICS
BUG:6989875 – TRANSPORTABLE TABLESPACE IMPORT SPINS USING CPU
BUG:7722575 – DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP
BUG:8225599 – ER: CTAS WITH LOB ACCESS ACROSS DATABASE LINK IS SLOW
NOTE:421441.1 – DataPump Import Via NETWORK_LINK Is Slow With CURSOR_SHARING=FORCE
NOTE:762160.1 – DataPump Import (IMPDP) Hangs When Using Parameters TRANSPORT_DATAFILES and REMAP_DATAFILE
BUG:5292551 – IMPDP VERY SLOW WHEN IMPORTING A TABLE WITH INITIALIZED COLUMN OF TYPE VARRAY
BUG:10194031 – EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
BUG:10416375 – DATA PUMP EXPDP JUST HANG ON KU$_TEMP_SUBPARTDATA_VIEW
NOTE:331221.1 – 10g Export/Import Process for Oracle Applications Release 11i
NOTE:786165.1 – Understanding the ESTIMATE and ESTIMATE_ONLY Parameters in Export DataPump
BUG:4438573 – DATAPUMP RUNS VERY SLOW OVER NETWORK FOR TABLES WITH CLOBS
BUG:24423416 – IMPDP FOR SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY TAKES HOURS