Bug 14588746 ORA-600 [kjbmprlst:shadow] in LMS in RAC – crashes the instance

目录 ORACLE, 数据库

Bug 14588746 – ORA-600 [kjbmprlst:shadow] in LMS in RAC – crashes the instance (文档 ID 14588746.8)

Bug 14588746 ORA-600 [kjbmprlst:shadow] in LMS in RAC – crashes the instance

This note gives a brief overview of bug 14588746.
The content was last updated on: 11-SEP-2016
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected
11.2.0.3
11.2.0.2
11.2.0.1
Platforms affected Generic (all / most platforms affected)
Fixed:

The fix for 14588746 is first included in
12.1.0.1 (Base Release)
11.2.0.4 (Server Patch Set)
11.2.0.3.11 Database Patch Set Update
11.2.0.3 Bundle Patch 23 for Exadata Database
11.2.0.3 Patch 29 on Windows Platforms

Interim patches may be available for earlier versions – click here to check.
Symptoms:

Related To:

Instance May Crash
Internal Error May Occur (ORA-600)
ORA-600 [kjbmprlst:shadow]
RAC (Real Application Clusters) / OPS
_gc_policy_time
_gc_undo_affinity
Description

This bug is only relevant when using Real Application Clusters (RAC)
A RAC database instance may crash with the following error in an
LMS process:
ORA-600 [kjbmprlst:shadow]

and an alert message like:
LMS1 (ospid: 21692502): terminating the instance due to error 484
..
Instance terminated by LMS1, pid = 21692502

The call stack is likely to look like this:
.. kjbmprlst

Workaround
Disable DRM by setting:
_gc_policy_time=0
_gc_undo_affinity=false

Note:
It was previously mentioned that setting _gcs_disable_skip_close_remastering=true
provided a workaround for this issue but that was incorrect. The bug can occur
regardless of the value of that parameter.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References

Bug:14588746 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

针对数据泵导出 (expdp) 和导入 (impdp)工具性能降低问题的检查表 (文档 ID 1549185.1)

目录 ORACLE, 数据库

 

针对数据泵导出 (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),我们已知这些参数可能会对数据泵作业产生影响。
如果您遇到了数据泵性能问题并需要解决它,且作业中使用了以下一个或多个参数,请先检查以下备注,并查看在不使用该参数或以不同方式使用该参数的情况下此性能问题是否重现。

  1. 数据泵参数:PARALLEL

    有关详细信息,另请参阅:
    Note:365459.1 “Parallel Capabilities of Oracle Data Pump”
    .
  2. 数据泵参数:DUMPFILE

    .
  3. Export Data Pump 参数:ESTIMATE

    有关Export Data Pump 参数 ESTIMATE 的详细信息,另请参阅:
    Note.786165.1 “Understanding the ESTIMATE and ESTIMATE_ONLY parameter in Export DataPump”
    .
  4. Export Data Pump 参数:FLASHBACK_SCN and FLASHBACK_TIME

    .
  5. Import Data Pump 参数:TABLE_EXISTS_ACTION

    .
  6. Import Data Pump 参数:REMAP_SCHEMA 或 REMAP_TABLESPACE

    与此问题相关的详细信息,另请参阅下面的“缺陷详细信息”部分,以及:
    Note:429846.1 “Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters”
    .
  7. 数据库参数: CURSOR_SHARING

    与此问题相关的详细信息,另请参阅下面的“缺陷详细信息”部分,以及:
    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'”
    .
  8. 导出/Import Data Pump 参数:STATUS监视正在进行的数据泵作业。此状态信息仅写入到您的标准输出设备中,而不写入到日志文件中(如果存在一个有效的日志文件)。

检查数据泵的活动

已知缺陷概述

下面概述了各个 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
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

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)
对于11.2.0.3, patch 16038089 中包含了以下修复:
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)
相对于Patch 16038089,下边两个patch是更好的选择:
11.2.0.3 – Patch 15893700
11.2.0.3.3或更高 – MLR Patch 14742362
这是因为这两个patch包含了Patch 16038089中所有的修复,同时还修复了其它一些之前patch没有修复的性能问题。

3)
所有8个 bug 都在Patch 14742362中修复并已包含11.2.0.4补丁集中,详见:
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 21286665 – EXPORT IS SLOW WAITING FOR “STREAMS AQ: ENQUEUE BLOCKED ON LOW MEMORY”

Note:
在11.2.0.4上发布的merge patch 20883577包含了以下bug的fix: 18469379, 18793246, 19674521, 20236523 and 20548904
在11.2.0.4上发布的merge patch 21443197包含了以下bug的fix: 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
– Unpublished Bug 17662403 – DATA PUMP EXPORT: SLOW I/O PERFORMANCE WRITING TO NFS DISKS
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

Note:
在12.1.0.2上发布的merge patch 20687195包含了以下bug的fix: 18793246, 20236523 and 20548904
在12.1.0.2上发布的merge patch 21554480包含了以下bug的fix: 18793246, 20236523, 20548904 and 21128593.

Import DataPump (impdp):

10.1.0.1.0    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    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
注意:expdp的bug 14192178的fix对一些impdp/import以及一些DBMS_METADATA的查询也有帮助

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

缺陷详细信息

  1. Bug 3447032 – Import Data Pump is slow when importing statistics
    – 缺陷:Bug 3447032“DBMS_STATS.SET_COLUMN_STATS can be slow (affects IMPORT)”(不是公开的 bug)
    – 症状:导入 INDEX_STATISTICS 或 TABLE_STATISTICS 时,Import(传统客户端)或 Import Data Pump 作业可能显示很长的等待时间
    – 版本:10.1.0.3.0 及更低版本
    – 已在以下版本中修正:10.1.0.4.0 及更高版本;对于某些平台, Patch:3447032 提供了针对 10.1.0.3.0 的修正
    – 打过补丁的文件:exuazo.o  kustat.xsl
    – 变通方案:排除统计信息导入 (EXCLUDE=statistics),并在导入完成后手动创建统计信息
    – 原因:如何在带有(许多)子分区的表中设置列统计信息的问题
    – 跟踪:SQL 跟踪显示对 DBMS_STATS 包的引用
    – 备注:必须在两个站点(源数据库和目标数据库)上都应用此 bug 的修正,且必须重新生成全部的 Export 或 Export Data Pump 转储文件,以便在导入时获取性能提升。
    .
  2. Bug 3508675 – Import Data Pump is slow when importing TABLE_DATA
    – 缺陷:Bug 3508675“APPSST10G: BAD PLAN WHEN QUERYING ALL_POLICIES WHEN IMPORTING TABLE_DATA”(不是公开的 bug)
    – 症状:在 TABLE_DATA 的导入阶段,impdp 作业可能会显示较高的 CPU 使用率和较慢的运行速度
    – 版本:10.1.0.5.0
    – 已在以下版本中修正:10.2.0.1.0 及更高版本; Patch:3508675 提供了可用于 10.1.0.5.0 的通用修正
    – 打过补丁的文件:prvtbpdi.plb
    – 变通方案:无
    – 原因:伴随 Bug 3369744 的修正而产生,ALL_SYNONYMS 视图不显示同义词的同义词(不是公开的 bug)
    – 跟踪:SQL 跟踪和 AWR 跟踪显示了查询的执行时间和较高 CPU 使用率:
    SELECT count(*) FROM ALL_POLICIES WHERE enable = :y and ins = :y2 and object_name = :tname and object_owner = :sname
    – 备注:该 bug 可能会出现在 Oracle Application 数据库(apps)或导入了许多个表的任何其他目标数据库的 impdp 作业期间。
    .
  3. Bug 4513695 – Export Data Pump of large table can be very slow when CURSOR_SHARING=SIMILAR
    – 缺陷:  Bug:4513695 “Poor performance for SELECT with ROWNUM=1 with literal replacement”
    – 症状:大型表 (100+ Gb) 的 Export Data Pump 作业速度可能要比原始 exp 客户端的导出慢很多(例如,前者的导出时间超过 24 小时)
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本;对于某些平台,Patch:5481520 提供了针对 10.2.0.3.0 的修正
    – 打过补丁的文件:apa.o kko.o kkofkr.o qerco.o
    – 变通方案:如果可能,在开始 Export Data Pump 作业之前先设置 CURSOR_SHARING=EXACT
    – 原因:将 cursor_sharing 设置为 similar 时,基于成本的优化器(Cost Base Optimizer,CBO)中出现查询优化问题
    – 跟踪:Data Pump Worker 跟踪显示“SELECT NVL((SELECT /*+ NESTED_TABLE_GET_REFS */ :”SYS_B_0″ FROM … WHERE ROWNUM = :”SYS_B_1″), :”SYS_B_2″) FROM DUAL”的 elapsed fetch 时间值非常高
    – 备注:针对此缺陷的修正只能作为 Bug:5481520 “Wrong results with ROWNUM and bind peeking”
    的修正予以提供。
    .
  4. Bug 5071931 – Import Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE is slow
    – 缺陷:Bug:5071931 “DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW”
    – 症状:在 DDL 的导入阶段,使用 REMAP_SCHEMA 和 REMAP_TABLESPACE 进行的 impdp 作业运行缓慢,例如:TABLE、INDEX、OBJECT_GRANT
    – 版本:10.2.0.1.0 至 10.2.0.3.0
    – 已在以下版本中修正:10.2.0.4.0 及更高版本;Patch:5071931 提供了适用于 10.2.0.3.0 的通用修正,且对于某些平台,该补丁还提供了针对较低版本的修正
    – 打过补丁的文件:prvtmeti.plb
    – 变通方案:如果不需要,则不使用 REMAP_% 参数
    – 原因:将多个转换链接在一起时出现了问题
    – 跟踪:Data Pump Worker 跟踪显示“DBMS_METADATA.CONVERT called”与“DBMS_METADATA.CONVERT returned”之间的 elapsed 时间值较高
    – 备注:此缺陷在 Oracle10g Release 1 中不会重现;有关详细信息,另请参阅
    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
    – 缺陷:Bug 5095025“ORA-4030 (KXS-HEAP-C,TEMPORARY MEMORY) USING EXPDP”(不是公开的 bug)
    – 症状:在导出过程式的对象(比如 schema jobs)时,许多 schema(例如 50+)的 schema 级别 expdp 作业可能会因 PGA 耗尽(内存泄露)而失败
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 和更高版本
    – 打过补丁的文件:( patchset 中)
    – 变通方案:如果可能,运行多个 Export Data Pump 作业,使每个作业都导出较少的 schema
    – 原因:查询优化问题(基于规则的优化器(Rule Based Optimizer,RBO),而不是基于成本的优化器 (CBO))
    – 跟踪:ORA-4030 和 Data Pump Worker 跟踪可能会显示对以下语句的引用:“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘PROCDEPOBJ_T’, …”
    – 备注:与此缺陷相关的内容:Bug:5464834Bug:5928639 和 Bug 5929373(不是公开的 bug)
    .
  6. Bug 5292551 – Import Data Pump runs out of memory (ORA-04030) and can be very slow on certain tables
    – 缺陷:Bug:5292551 “IMPDP VERY SLOW WHEN IMPORTING A TABLE WITH INITIALIZED COLUMN OF TYPE VARRAY”
    – 症状:导入表数据时,特定表(例如包含 Spatial 数据 MDSYS.SDO_GEOMETRY 的表)的 impdp 作业速度可能会非常慢,且在加载这些表时,Data Pump Worker 进程显示内存使用量在不断增加
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本;对于某些平台,Patch:5292551 提供了针对 10.2.0.3.0 的修正
    – 打过补丁的文件:kpudp.o
    – 变通方案:如果可能,排除这些表:EXCLUDE=TABLE:”in(‘TAB_NAME’, …),并在第二次的表级别 Import Data Pump 作业中单独导入这些表:TABLES=owner.tab_name
    – 原因:内存没有释放,这导致存在较大数量的已分配内存
    – 跟踪:Heapdump 显示多个 freeable chunk“reeable assoc with marc”或“klcalh:ld_hds”
    – 备注:在运行数天之后,impdp 作业可能会失败,并出现错误,例如 ORA-4030(out of process memory when trying to allocate xxx bytes(在尝试分配 xxx 字节时进程内存不足))或 ORA-31626(job does not exist(作业不存在))或内部错误 ORA-00600 [729]、[12432]、[space leak]。
    .
  7. Bug 5464834 – Export Data Pump runs out of memory (ORA-4030) when many tables are involved
    – 缺陷:Bug:5464834 “ORA-4030 (KXS-HEAP-C,TEMPORARY MEMORY) USING EXPDP”
    – 症状:导出表数据时,许多表(例如 250+)的表级别 expdp 作业可能会因 PGA 耗尽(内存泄露)而失败
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本;Patch:5464834 提供了适用于 10.1.0.4.0 和 10.2.0.3.0 的通用修正
    – 打过补丁的文件:catmeta.sql  prvtmeti.plb
    – 变通方案:如果可能,运行多个 Export Data Pump 作业,使每个作业都导出较少数量的表
    – 原因:查询优化问题(基于规则的优化器 (RBO),而不是基于成本的优化器 (CBO))
    – 跟踪:ORA-4030 和Data Pump Worker 跟踪可能显示对以下语句的引用:“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘TABLE_DATA_T’, …”
    – 备注:与此缺陷相关的内容:Bug 5095025(不是公开的 bug)、Bug:5928639 和 Bug 5929373(不是公开的 bug)。
  8. Bug 5555463 – Import Data Pump can be slow when importing small LOBs (under 256K)
    – 缺陷:Bug 5555463“PERFORMANCE ISSUES FOR DATAPUMP IMPORT/EXTERNAL_TABLE MODE OF TABLES WITH LOBS”(不是公开的 bug)
    – 症状:在导入包含小 LOB(小于 256 kb 的 LOB)的表时,发生性能下降、高 CPU 使用率以及 LOB redo 生成的情况
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本
    – 打过补丁的文件:(在 patchset 中)
    – 变通方案:无(如果可能,在“Direct Path”模式下运行加载:ACCESS_METHOD=DIRECT_PATH)
    – 原因:在“External Table”模式下加载数据时使用临时 LOB
    – 跟踪:(无详细信息)
    – 备注:在“Direct Path”模式下,相同表数据的 impdp 作业显示更快的性能
    .
  9. Bug 5590185 – Consistent Export Data Pump is slow when exporting row data
    – 缺陷:Bug:5590185 “CONSISTENT EXPORT DATA PUMP JOB (FLASHBACK_TIME) HAS SLOWER PERFORMANCE”
    – 症状:在使用 FLASHBACK_TIME 或 FLASHBACK_SCN 时或在使用 logical standby 或 Streams 时,涉及较大数量表的 expdp 作业运行缓慢
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本;对于某些平台,Patch:5590185 提供了针对 10.2.0.2.0 的修正
    – 打过补丁的文件:prvtbpm.plb
    – 变通方案:如果不需要,则不运行一致性 Export Data Pump 作业
    – 原因:针对数据泵主表的全表扫描
    – 跟踪:SQL 跟踪显示以下语句的执行时间:
    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)
    – 备注:如果正常的 expdp 作业需要 1 个小时,则现在相同的一致性作业可能需要 8 个小时以上的时间。
    .
  10. Bug 5928639 – Export Data Pump can be very slow if CURSOR_SHARING is not EXACT
    – 缺陷:Bug:5928639 “DATAPUMP EXPORT SLOW WHEN CURSOR_SHARING is not EXACT”
    – 症状:如果涉及到多个表且未将 init.ora 或 spfile 参数 CURSOR_SHARING 设置为 EXACT,则 Export Data Pump 作业的运行速度可能会比较慢
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本,已包含 Bug:5464834 的修正(见上文)
    – 打过补丁的文件:catmeta.sql prvtmeti.plb
    – 变通方案:设置 spfile 参数 CURSOR_SHARING=EXACT
    – 原因:查询优化问题(基于规则的优化器 (RBO),而不是基于成本的优化器 (CBO))
    – 跟踪:Data Pump Worker 跟踪文件显示调用 DBMS_METADATA.FETCH_XML_CLOB 的等待时间较长,SQL 跟踪文件显示对以下语句的引用:“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘TABLE_DATA_T’, …”
    – 备注:与此缺陷相关的内容:Bug 5095025(不是公开的 bug)、Bug:5464834 和 Bug 5929373(不是公开的 bug)。
    .
  11. Bug 5929373 – Export Data Pump of a table can be very slow if database has many user tables
    – 缺陷:Bug 5929373“APPS ST GSI – DATA PUMP TAKES LONG TIME TO EXPORT DATA”(不是公开的 bug)
    – 症状:如果数据库具有多个用户表,则小表的 Export Data Pump 作业的运行速度可能会比较慢
    – 版本:10.1.0.x 和 10.2.0.3.0 及更低版本
    – 已在以下版本中修正:10.2.0.4.0 及更高版本,已包含 Bug:5464834 的修正(见上文)
    – 打过补丁的文件:catmeta.sql prvtmeti.plb
    – 变通方案:无
    – 原因:查询优化问题(基于规则的优化器 (RBO),而不是基于成本的优化器 (CBO))
    – 跟踪:Data Pump Worker 跟踪文件显示调用 DBMS_METADATA.FETCH_XML_CLOB 的等待时间较长,SQL 跟踪文件显示对以下语句的引用:“SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(‘TABLE_DATA_T’, …”
    – 备注:数据泵可能需一个小时以上的时间来处理表,而原始的导出客户端则只需要两三分钟;与此缺陷相关的内容:Bug 5095025(不是公开的 bug)、Bug:5464834Bug:5928639
  12. Bug 7722575 -DATAPUMP VIEW KU$_NTABLE_DATA_VIEW causes poor plan / slow Expdp
    – 缺陷:Bug 7722575“DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP”
    – 症状:数据泵视图 KU$_NTABLE_DATA_VIEW 和
    KU$_NTABLE_BYTES_ALLOC_VIEW 的定义可能会导致执行计划不甚理想以及数据泵导出视图的查询性能不佳
    – 版本:10.2.0.x 和 11.1.0.X
    – 已在以下版本中修正:10.2.0.5.0 和 11.2
    – 打过补丁的文件:catmeta.sql
    – 变通方案:无
    – 原因:ku$_ntable_data_view 数据泵视图的定义不正确
    – 跟踪:SQL 跟踪文件显示以下语句的执行计划成本过高:
    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
    – 缺陷:Bug:10178675 “expdp slow with processing functional_and_bitmap/index”
    – 症状:EXPDP 显示以下步骤消耗时间过长:
    Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
    – 版本:10.2.0.4、11.1.0.7、11.2.0.1、11.2.0.2
    – 已在以下版本中修正:11.2.0.3、12.1
    – 打过补丁的文件:prvtmeta.plb、prvtmeti.plb
    – 变通方案:无
    – 原因:导出域索引时,其内部使用的是视图 ku$_2ndtab_info_view。使用 RBO时,此视图上的 select 会生成不良计划并耗费更多时间。
    – 跟踪:Expdp Worker (DW) 显示,执行以下形式的 SQL 花费了很长时间:
    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
    – 缺陷:Bug:10194031 – EXPDP OF OR XML LEAKS MEMORY / RUNS SLOW 11.2.0.1 WORKS 11.1.0.7
    – 症状:产生 ORA-4030 错误之前,包含 XMLTYPE 列的表的导出速度可能会非常慢。在尝试导出整个用户表或单独的表时,会发生此问题。
    – 版本:11.2.0.1、11.2.0.2
    – 已在以下版本中修正:11.2.0.3、12.1
    – 变通方案:无
    – 原因:对包含 xmltype 数据的表运行 expdp 时,发生内存泄露
  15. Bug 8904037 – LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS
    – 缺陷:Bug 8904037 – LT_CTX_PKG.SCHEMA_INFO_EXP IS TAKING MORE TIME WHILE EXPORTING PROCOBJ OBJECTS
    – 症状:导出操作在对象类型为 DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM 上花费时间过长。
    – 版本:11.1.0.7, 11.2.0.1
    – 已在以下版本中修正:11.2.0.2, 12.1
    – 变通方案:移除 Workspace Manager 选项
    – 原因:由于在11.1.0.7中引入的函数”setCallStackAsValid”

对于11.2.0.3, patch 16038089 中包含了以下修复:

参考

NOTE:1290574.1 – Datapump Performance Issue With Content=Metadata_only
BUG:5071931 – DATAPUMP IMPORT WITH REMAP TABLESPACE, AND SCHEMA IS VERY SLOW

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
NOTE:362205.1 – 10g Release 2 Export/Import Process for Oracle Applications Release 11i
NOTE:365459.1 – Parallel Capabilities of Oracle Data Pump
BUG:7439689 – IMPDP HANGS ON IDLE EVENT ‘WAIT FOR UNREAD MESSAGE ON BROADCAST CHANNEL’
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
NOTE:786165.1 – Understanding the ESTIMATE and ESTIMATE_ONLY Parameters in Export DataPump
BUG:6807289 – IMPDP WITH REMAP_SCHEMA AND REMAP_TABLESPACE HANGS AT TABLE STATISTICS
BUG:6989875 – TRANSPORTABLE TABLESPACE IMPORT SPINS USING CPU
BUG:5464834 – ORA-4030 USING EXPDP
BUG:5928639 – DATAPUMP EXPORT SLOW WHEN CURSOR_SHARING != EXACT

BUG:7722575 – DATAPUMP VIEW KU$_NTABLE_DATA_VIEW CAUSES POOR PLAN / SLOW EXPDP
BUG:5481520 – WRONG RESULTS WITH ROWNUM AND BIND PEEKING
NOTE:429846.1 – Slow Data Pump with REMAP_SCHEMA and REMAP_TABLESPACE parameters
NOTE:94036.1 – Init.ora Parameter “CURSOR_SHARING” Reference Note
NOTE:155477.1 – Parameter DIRECT: Conventional Path Export Versus Direct Path Export

BUG:8363441 – VERY EXPENSIVE SQL STATEMENT DURING DATAPUMP IMPORT WITH MANY SUBPARTITIONS
BUG:5996665 – EXPDP HANGING MORE THAN 5 HOURS
NOTE:277905.1 – Export/Import DataPump Parameter TABLES – How to Export and Import Tables Residing in Different Schemas
BUG:10178675 – EXPDP SLOW WITH PROCESSING FUNCTIONAL_AND_BITMAP/INDEX
BUG:7585314 – OCSSD.BIN CONSUMING 6 TIMES MORE CPU IF EXCESSIVE DATAPUMP IS RUNNING ON NODE
BUG:7710931 – DATAPUMP EXPORT IS EXTREMELY SLOW WHEN EXTRACTING SCHEMA
NOTE:14834638.8 – Bug 14834638 – IMPDP import slow on create partitioned index
NOTE:1673445.1 – EXPDP Estimate Phase Takes a Long Time With 12.1.0.1
NOTE:885388.1 – DataPump Export Is Slow After Upgrade To 11g When Workspace Manager Is Installed
BUG:7413726 – POOR EXPDP PERFORMANCE WHEN DB COMPATIBLE=10.2.0.3 OR 10.2.0.4

NOTE:223730.1 – Automatic PGA Memory Management
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

BUG:7710931 – DATAPUMP EXPORT IS EXTREMELY SLOW WHEN EXTRACTING SCHEMA
BUG:4438573 – DATAPUMP RUNS VERY SLOW OVER NETWORK FOR TABLES WITH CLOBS
BUG:4513695 – SELECT WITH ROWNUM=1 PERFORMANCE IS TOO LATE USING CURSOR_SHARING=SIMILAR
BUG:5573425 – NON-CORRELATED SUBQUERY RETURNS WRONG RESULTS, LIKE A CARTESIAN JOIN
BUG:5590185 – CONSISTENT EXPORT DATA PUMP JOB HAS SLOWER PERFORMANCE
BUG:8225599 – ER: CTAS WITH LOB ACCESS ACROSS DATABASE LINK IS SLOW
NOTE:286496.1 – Export/Import DataPump Parameter TRACE – How to Diagnose Oracle Data Pump
BUG:6460304 – EXPDP TAKES MORE TIME

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.

  1. Data Pump parameter: PARALLEL

    For details, see also:
    Note:365459.1 “Parallel Capabilities of Oracle Data Pump”
    .
  2. Data Pump parameter: DUMPFILE

    .
  3. 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”
    .
  4. Export Data Pump parameters: FLASHBACK_SCN and FLASHBACK_TIME

    .
  5. Import Data Pump parameter: TABLE_EXISTS_ACTION

    .
  6. 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”
    .
  7. 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'”
    .
  8. 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.

Export DataPump (expdp):

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.

 

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
    DefectBug: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
    DefectBug: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.

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

Install Oracle 12cR2 on RHEL6

目录 ORACLE, 数据库

[TOC]

Install Oracle 12cR2 on RHEL6

操作系统

Red Hat Enterprise Linux Server release 6.5

IP HOSTNAME
192.168.56.10 oel12c

/etc/hosts 文件

 vi /etc/hosts
192.168.56.10   oel12c

/etc/sysctl.conf

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
/sbin/sysctl -p

/etc/security/limits.conf

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728

依赖包

yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install make -y
yum install nfs-utils -y
yum install net-tools -y
yum install smartmontools -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y
yum install e2fsprogs -y
yum install e2fsprogs-libs -y
yum install libs -y
yum install libxcb.i686 -y
yum install libxcb -y

关闭防火墙

chkconfig iptables off
service iptables stop

禁用selinux

vi /etc/selinux/config
SELINUX=disabled
setenforce 0 

创建用户组及用户

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
useradd -u 54321 -g oinstall -G dba,oper oracle
passwd oracle

创建目录

mkdir -p /u01/app/oracle/product/12.2.0.1/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

oracle 用户环境配置

vi ~/.bash_profile

# Set Oracle Environment
ORACLE_BASE=/u01/app/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/db_1; export ORACLE_HOME
ORACLE_SID=enmo;export ORACLE_SID
ORACLE_OWNER=oracle;export ORACLE_OWNER
ORACLE_TERM=vt100;export ORACLE_TERM
LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
PS1=`hostname`'<$PWD>$';export PS1
EDITOR=/bin/vi; export EDITOR
JAVA_HOME=/usr/local/java;export JAVA_HOME
ORA_NLS33=/u01/product/oracle/ocommon/nls/admin/data;export ORA_NLS33
CLASSPATH=/u01/product/oracle/jdbc/lib/classesl11.zip:/usr/local/java;
PATH=$ORACLE_HOME/bin:$JAVA_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.;export PATH

软件包准备

Download Software

OTN: Oracle Database 12c Release 2 (12.2.0.1) Software (64-bit).

linuxx6412201database.zip

Unzip the file.

unzip linuxx64_12201_database.zip

图形界面安装oracle 12R2 数据库软件

./runInstaller 启动图形安装界面

配置安全更新

只安装数据库软件

安装单实例数据库软件

root 用户运行脚本

netca 创建监听

dbca 建库

dbca建库完成

 

 

oracle expdp/impdp的常用用法总结

目录 ORACLE, 数据库

oracle expdp/impdp的常用用法总结

1、导出某个用户下的所有东东

schemas=schemas 名称 liups

expdp system/liups.com schemas=liups DIRECTORY=DATA_PUMP_DIR dumpfile=liups.com.dmp logfile=liups.com.log

2、只导出表结构不导出数据、或者只导出数据不导出表结构

CONTENT
指定要卸载的数据。
有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY 。
DATA_ONLY :只导出数据
METADATA_ONLY : 只导出表结构(元数据)
只导出表结构不导出数据
expdp system/liups.com schemas=liups DIRECTORY=DATA_PUMP_DIR CONTENT=METADATA_ONLY  dumpfile=liups.com.dmp logfile=liups.com.log

只导出数据不导出表结构
expdp system/liups.com schemas=liups DIRECTORY=DATA_PUMP_DIR CONTENT=DATA_ONLY  dumpfile=liups.com.dmp logfile=liups.com.log
3、从一个用户导入到另一个用户
REMAP_SCHEMA=元用户名称:新导入的用户名称(可以不存在)

导出的时候以schemas的方式导出 导入的时候使用   REMAP_SCHEMA=元用户名称:新导入的用户名称(可以不存在)
导出: expdp system/liups.com DIRECTORY=DATA_PUMP_DIR  schemas=liups.com dumpfile=liups.com.dmp logfile=liups.com.log
导入: impdp system/liups.com DIRECTORY=DATA_PUMP_DIR dumpfile=liups.com.dmp REMAP_SCHEMA=liups.com:liups.org logfile=liups.com.log

1) REMAP_SCHEMA : 重定义对象所属Schema
该参数的作用类似IMP中的Fromuser+Touser,支持多个Schema的转换,语法如下:
REMAP_SCHEMA=Source_schema:Target_schema[,Source_schema:Target_schema]
如 把A的对象转换到C用户,将C转换到D用户。Remap_schema=a:b,c:d
Warning: 不能在同一个IMPDP命令中指定remap_schema=a:b,a:c.

3、从一个表空间导入到另一个表空间

2) REMAP_TABLESPACE : 重定义对象所在的表空间。
该参数用来重映射导入对象存储的表空间,支持同时对多个表空间进行 转换,相互间用逗号分割。语法如下:
REMAP_TABLESPACE=Source_tablespace:Target_tablespace[,Source_tablespace:Target_tablespace]
Warning: 如果使用Remap_tablespace参数,则要保证导入的用户对目标表空间有读写权限。

4、过滤数据
过滤数据主要依赖于Query和Sample两个参数。其中Sample参数主要针对expdp导出功能。
1. Query
与exp命令中的Query功能类似,不过Expdp中,该参数功能得到了增强,控制的粒度更细。Expdp中的Query也是指定类 似where语句来限定记录。语法如下:
Query = [Schema.][Table_name:] Query_clause
默认 情况如果不指定Schema.table_name,则Query_clause针对所有导出的表有效,或者你可以为每一个表指定不同的 Query_clause,如:导出a表中所有id<5的记录,导出b表中所有name=’a’的记录,则Query的参数应该如下:
Query=A:”Where id<5″,B:”Where name=’a’”
如果Where条件前没有指定Schema名或者表名的话,默认就是针对当前所有要被 导出的表。如:Query=Where id <5
Warning: 建议把Query参数放入到参数文件中使用,以避免转义符带来的麻烦。
2. Sample
该参数用来指定导出数据的百分比,可指定的值的范围从0.000001到99.999999,语法如下:
Sample=[[Schema_name.]Table_name:]sample_percent
指 定该参数以后,EXPDP导出将自动控制导出的记录量,如导出A表中50%的记录,设置的Sample参数如下:
Sample=A:50
Warning: Sample_percent指定的值只是一个参考值,EXPDP会根据数据量算出一个近似值。
过滤对象
过滤对象主要依赖于Include和Exclude两个参数。这两个参数作用正好相反,在这两个参数中,可以指定你知道的任何对象 类型(如:Package、Procedure、Table等等)或者对象名称(支持通配符)
1. Exclude 反规则
指定不被包含的对象类型或者对象名称。指定了该参数以后,指定的对象类型对应的所有对象都不会被导入或导出。 如果被排除的对象有依赖的对象,那么其依赖的 对象也不会被导入或导出。如:通过Exclude参数指定不导出表对象的话,不仅指定的表不会被导出,连这些表关联的Index、Check等都不会被导出。Warning: 建议把Exclude参数放入到参数文件中使用,以避免转义符带来的麻烦。
2. Include 正规则
与Exclude正好相反。指定包含的对象类型或者对象名称。
Warning: 由于两个参数功能正好相反,因此在执行导入或导出命令时,两个参数不能同时使用,否则Oracle也不知道你想要干什么啦。
exclude/include参数用法:
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]  –>排出特定对象
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]  –>包含特定对象
object_type子句用于指定对象的类型,如table,sequence,view,procedure,package等等
name_clause子句可以为SQL表达式用于过滤特定的对象名字。它由SQL操作符以及对象名(可使用通配符)来过滤指定对象类型中的特定对象。
当未指定name_clause而仅仅指定object_type则所有该类型的对象都将被过滤或筛选。多个[object_type]:[name_clause]中间以逗号分割。
示例:
expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:”IN (‘EMP’,’DEPT’)”
impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:”=’EMP'”
常用的过滤SQL表达式
EXCLUDE=SEQUENCE,VIEW                          –过滤所有的SEQUENCE,VIEW
EXCLUDE=TABLE:”IN (‘EMP’,’DEPT’)”               –过滤表对象EMP,DEPT
EXCLUDE=SEQUENCE,VIEW,TABLE:”IN (‘EMP’,’DEPT’)” –过滤所有的SEQUENCE,VIEW以及表对象EMP,DEPT
EXCLUDE=INDEX:”= ‘INDX_NAME'”                   –过滤指定的索引对象INDX_NAME
INCLUDE=PROCEDURE:”LIKE ‘PROC_U%'”              –包含以PROC_U开头的所有存储过程(_ 符号代表任意单个字符)
INCLUDE=TABLE:”> ‘E’ ”                          –包含大于字符E的所有表对象
其它常用操作符 NOT IN, NOT LIKE, <, != 等等
直接将过滤操作符封装到参数文件中,如下面的例子
Parameter file:exp_scott.par
DIRECTORY = dump_scott
DUMPFILE = exp_scott_%U.dmp
LOGFILE = exp_scott.log
SCHEMAS = scott
PARALLEL= 2
EXCLUDE = TABLE:”IN (‘EMP’, ‘DEPT’)”
expdp system/manager parfile=exp.par
命令行下转义符的处理
Windows平台:
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
INCLUDE=TABLE:\”IN (‘EMP’, ‘DEPT’)\”
在Windows平台下,需要对象双引号进行转义,使用转义符\
Unix平台:
在未使用parfile文件的情形下,所有的符号都需要进行转义,包括括号,双引号,单引号等
% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
INCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”
exclude/include常见的错误
任意需要转义的字符如果未转义或转义错误,都会产生ORA错误。下面给出几种常见的ORA错误。
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: “DEPT”: invalid identifier
ORA-39001: invalid argument value
ORA-39041: Filter “INCLUDE” either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types
ORA-39001: invalid argument value
ORA-39038: Object path “USER” is not supported for TABLE jobs.
高级过滤
在导出/导入的时候,我们常常有这样的需求,只想导出/导入表结构,或者只想导出/导入数据。幸运的是数据泵也提供了该功能。使用 Content参数。该参数有三个属性
1) ALL : 导出/导入对象定义和数据,该参数的默认值就是ALL
2) DATA_ONLY : 只导出/导入数据。
3) METADATA_ONLY : 只导出/导入对象定义。
Warning: 有一点值得注意的时,在执行导出的时候,如果使用了高级过滤,如只导出了数据,那么导入时,需要确保数据定义已经存在。否则数据都变成没有主子了。如果数 据定义已经存在,导入时最好指定data_only,否则会触发ORA-39151错误,因为对象已经存在了。
过滤已经存在的数据
我们知道,导入的表对象在目标库中已经存在,并且目标端没有创建数据完整性约束条件(RI)来检验数据的话,就有可能造 成数据被重复导入。数据泵提供了一 个新的参数Table_exists_action,可以一定程度上降低重复数据的产生。该参数用来控制如果要导入的表对象存在,执行什么操作。有以下几 个参数值:
1) SKIP : 跳过该表,继续处理下一个对象。该参数默认就是SKIP。值得注意的是,如果你同时指定了CONTENT参数为Data_only的话,SKIP参数无 效,默认为APPEND。
2) APPEND : 向现有的表中添加数据。
3) TRUNCATE : TRUNCATE当前表,然后再添加记录。使用这个参数需要谨慎,除非确认当前表中的数据确实无用。否则可能造成数据丢失。
4) REPLACE : 删除并重建表对象,然后再向其中添加数据。值得注意的是,如果同时指定了CONTENT参数为Data_only的话,REPLACE参数无效。

如何在11g上收集 Optimizer 统计信息 (文档 ID 1523938.1)

目录 ORACLE, 数据库

文档内容

目标
解决方案
快速重建统计信息的推荐步骤
关于收集optimizer统计信息的重要文档
收集对象的统计信息
使用足够大的sample size
收集所有对象的统计信息
收集字段的统计信息/数据非均匀分布列的histogram
收集分区对象的Global Statistics(全局统计信息)
收集System Statistics(系统统计信息)
从早期版本升级到11g
缺省设置
统计信息收集命令的例子
在单一的表收集统计信息
收集某一schema下的所有对象的统计信息
收集database中所有对象的统计信息:
参考

适用于:

Oracle Database – Enterprise Edition – 版本 11.1.0.6 到 11.2.0.3 [发行版 11.1 到 11.2]
Oracle Database – Standard Edition – 版本 11.1.0.6 到 11.2.0.3 [发行版 11.1 到 11.2]
Oracle Database – Personal Edition – 版本 11.1.0.6 到 11.2.0.3 [发行版 11.1 到 11.2]
本文档所含信息适用于所有平台

目标

这篇文档概述了在Oracle 11g中收集CBO(Cost Based Optimizer)的统计信息的推荐方法。对于其它版本请参见:

Document 1226841.1 How To: Gather Statistics for the Cost Based Optimizer

 

注:下面这个文档中有个标题为”Best Practices for Managing Optimizer Statistics”的章节,该章节提供了关于收集统计信息的一些其它建议

Document:1380043.1 Selected Performance Related Seminars from Oracle Openworld

解决方案

快速重建统计信息的推荐步骤

为快速删除和重建某张表及它的索引的统计信息(包含非均匀分布列的统计信息),推荐如下:

exec dbms_stats.delete_table_stats(ownname=>’user_name’,-
tabname=>’table_name’,cascade_indexes=>true);exec dbms_stats.gather_table_stats(ownname=>’user_name’,-
tabname=>’table_name’,-
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,-
cascade=>true,-
method_opt=>’for all columns size AUTO’);


随后我们会解释这样推荐的原因。同样,后边会有更多收集统计信息的例子。
需要注意的是,从10gR2 开始,下边的方法可以用来恢复统计信息:

Document 452011.1 * Restoring table statistics in 10G onwards

关于收集optimizer统计信息的重要文档

  • 这些推荐适用于大多数的数据库。
  • 这些推荐的目的是为了尽可能生成准确的统计信息,为此我们推荐使用的sample size(采样率)为100%。这是因为sample size的减少可能会导致生成的统计信息不准确。一般来说使用100%的sample size会使收集信息的时间增长,所以需要考虑调整当前使用的maintenance window,使统计信息的收集能在一个maintenance window完成。
  • 如果可能,我们推荐使用预设好的偏好设置使收集不同对象的过程标准化。不同于对不同的对象使用不同的设置,在 Database/Schema/Table 一级设置默认偏好能使设置集中化,且不需要对不同对象单独设置。自动统计信息收集操作也会使用设置好的默认偏好。关于如何设置默认偏好请详见:
    Document 1493227.1 How to Change Default Parameters for Gathering Statistics in Oracle 11g

尽管默认值对大多数系统都适用,如果您发现某些特定设置比默认值更适合您的系统,那么请自行添加至默认设置中。

  • 收集新的optimizer统计信息一般会保持或改进已存在的执行计划,但是也可能使某些查询的性能下降。所以从10gR1开始系统默认会保留最近30天的统计信息,这期间如果遇到问题可以用如下方法恢复到之前的统计信息:
    Document 452011.1 * Restoring table statistics in 10G onwards
  • 收集新的optimizer的统计信息可能使shared pool中的cursor无效,所以在数据库负载低的时候执行统计信息收集将是明智的选择,比如maintenance window。
  • 除了object statistics(比如业务表和索引的统计信息),我们推荐对dictionary objects(数据字典对象)也收集统计信息:
    Document 457926.1 How to Gather Statistics on SYS Objects and ‘Fixed’ Objects?
  • 对于非常大的系统,统计信息的收集会是非常消耗时间和资源的工作。在这种情况下需要小心选择sample size的大小,确保在可接受的时间范围、资源限制和维护窗口内完成收集。请参见以下文档:
Document 44961.1 Statistics Gathering: Frequency and Strategy Guidelines


对于这种系统,我们也推荐使用基于改变的统计信息的收集,避免重复收集不必要的信息。和10g相比,11g的自动统计信息的过程已经发生了改变,详见以下文档:

Document 237901.1 Gathering Schema or Database Statistics Automatically – Examples
Document 756734.1  11g: Scheduler Maintenance Tasks or Autotasks (Doc ID 756734.1)
Document 743507.1 Why Has the GATHER_STATS_JOB been removed in 11g? (Doc ID 743507.1)

收集对象的统计信息

The Cost Based Optimizer (CBO) 是基于统计信息生成特定查询的执行计划的。当sample size减小的时候,基于抽样出来的样本可能会生成不同的统计信息,这是因为这些样本有可能正好是用某些特定的方法录入的。

在11g中,收集统计信息推荐使用定期统计信息收集脚本。在绝大多数情况下系统默认脚本提供的采样率都是合适的,并兼顾了以下推荐:


  • 使用足够大的sample size

    在11g中,对于ESTIMATE_PERCENT,我们建议使用缺省的 DBMS_STATS.AUTO_SAMPLE_SIZE。Oracle首先会判断如果对一个表使用100%的sample size是否能在一个maintenance window中收集完。如果能收集完那么就对这个表使用100%的sample size,尽管这意味着对这张表收集统计信息的频率会降低。如果100%的sample size并不可行,那么会至少使用30%作为sample size。11g使用了一种hash算法来计算统计信息,所以在大多数时候收集信息的性能都是可以接受的。
    通常,对大部分应用程序而言,相对于数据每天发生的一点点变化,统计信息整体上的准确度更重要一些。这个参数在早期版本的设置请参见之后的文档。


  • 收集所有对象的统计信息

    确保所有对象(表和索引)都收集了统计信息。这个通过使用CASCADE参数可以很容易的实现。


  • 收集字段的统计信息/数据非均匀分布列的histogram

    确保所有数据非均匀分布的列都收集了histogram,并且使用METHOD_OPT参数来确保histogram的精度。通常我们推荐使用缺省的设置 “AUTO”。这意味着如果histogram的存在会有助于生成更准确的执行计划,DBMS_STATS会自动在需要字段上生成它。相对于在所有的列上 收集histogram,只在确实需要的列上收集是更稳妥的做法。

    Document 390249.1  How To Quickly Add/Remove Column Statistics (Histograms) For A Column

    注意:假如某个列上的数据是非均匀分布的,并且sample size不是100%(例如使用AUTO),那么这个列上的某些数据可能会被统计信息给漏掉。假如发生了这种情况,并且查询恰好使用了那些没有 histogram bucket对应的值,那么这条查询就不可能使用真正准确的统计信息,进而可能产生不准确甚至是错误的执行计划。在这种情况下,使用100%的 sample size是唯一能保证统计信息准确性的办法。假如这是不可行的,移除列上的histogram(只使用列的最大最小值)将使生成的执行计划更加一致,因为 selectivity 是不变的。

    同样的道理,假如统计信息不是最新的,histogram的存在甚至会引入额外的问题,比如当查询引 用的值是在histogram的范围之外,或者是”frequence” histogram的中间值(比如,列上添加了一个之前不存在的值或者某个特定值的行数发生了很大的变化)。在这样的情况下,optimizer将不得不 做一些猜测(有可能是不正确的),从而产生不准确的执行计划。 所以,需要通过应用程序测试不同的值以确定哪种统计信息能帮助产生最好的执行计划。

    需要注意的是由于功能限制目前只有254个buckets可以用来存储列统计信息。如果您的distinct value的个数非常大而且上面数据的分布是不均匀的,列的统计信息仍然可能是不准确的。详见:

    Document 212809.1 Limitations of the Oracle Cost Based Optimizer

    还有就是只有当你的应用确实能用到histogram的时候收集它才是有意义的。特别是当你使用绑定变量但不会做窥测(bind peek)的时候(例如_OPTIM_PEEK_USER_BINDS = FALSE),optimizer不会有任何用来检索histogram的数据,从而也不会因为histogram的存在而更好地估算 cardinality。

    在早期的版本中,参数METHOD_OPT的缺省的设定是”FOR ALL COLUMNS SIZE 1″。这意味着我们只会收集列的最大最小值,而没有值的具体分布的信息。我们知道,有些时候histogram的存在反而不利于好的执行计划的产生,所以 当从一个版本升级到另外一个版本后,用户可能希望先将这个参数设置为之前版本的值,运行一段时间后再调整为当前版本的值,使升级后的初始阶段更加平稳,详 见以下文档:

    Document 465787.1 How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g

  • 收集分区对象的Global Statistics(全局统计信息)

    如果使用了partition(分区)技术,时间允许的话尽可能收集Global Statistics。Global Statistics是非常重要的,但因为涉及数据量及收集时间长度的影响,很多时候它都被略过了。假如不能采用100%的sample size,那么我们推荐至少使用1%来做收集。小的采样(例如0.001, 0.0001, 0.00001等)可以是非常有效的,但同时要知道大量能帮助optimizer生成准确执行计划的数据会被忽略掉的。参数 ESTIMATE_PERCENT有一个非常弹性的有效的范围 [0.000001 -> 100] ,对于
    非常巨大的表您可以使用非常小的值。不同的系统需要经过测试才能得出最适合这个系统的值。详见以下文档:

    Document 236935.1 Global statistics – An Explanation

    11g同时提供了以增量方式收集Global Statistics的方法.详见:

    Oracle Database Performance Tuning Guide
    11g Release 1 (11.1)
    Part Number B28274-02
    Chapter 13 Managing Optimizer Statistics
    Section 13.3.1.3 Statistics on Partitioned Objects

  • 收集System Statistics(系统统计信息)

    收集System Statistics来真实反映系统CPU负载情况。除了估算正常I/O部分的cost之外,这些信息会帮助CBO更好的估算CPU部分的cost。详见:

    Document 470316.1 Using Actual System Statistics (Collected CPU and IO information
    Document 149560.1 Collect and Display System Statistics (CPU and IO) for CBO usage
    Document 153761.1 Scaling the System to Improve CBO optimizer

  • 从早期版本升级到11g

    如果您是从Oracle的早期版本升级到11g,您可以参见以下关于统计信息管理的建议:

    Document 465787.1 How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g/div>

缺省设置

不同版本收集统计信息时使用的缺省设置可能是不一样的,例如:

  • ESTIMATE_PERCENT的缺省值:
    •  9i : 100%
    • 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (使用非常小的估算百分比)
    • 11g : DBMS_STATS.AUTO_SAMPLE_SIZE (使用更大的估算百分比 – 100%)
  • METHOD_OPT: defaults:
    • 9i : “FOR ALL COLUMNS SIZE 1” 等同于没有列值的具体分布信息.
    • 10g and 11g : “FOR ALL COLUMNS SIZE AUTO” – 这意味着如果histogram的存在会有助于生成更准确的执行计划,DBMS_STATS会自动在需要字段上生成它。

在11g中,AUTO_SAMPLE_SIZE的缺省值是100%,从而尽可能使统计信息是准确的。在之前的版本上,由于时间的制约,100%的 sample size有时候是不可能的,由于11g实施了一个新的hash算法避免了传统的排序(在9i和10g典型慢的部分是排序),这显著的减少了了收集的时间并 提升了资源使用效率。同样,是否收集列上的统计信息也是自动决定的,总之11g上还有很多其它类似的改进。

您可以使用以下文档中提到的方法来修改默认值:

Document 1493227.1 How to Change Default Parameters for Gathering Statistics in Oracle 11g

统计信息收集命令的例子

在单一的表收集统计信息

>exec dbms_stats.gather_table_stats(  –
ownname => ‘  Schema_name ‘, –
tabname => ‘  Table_name  ‘, –
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,  –
cascade => TRUE,  –
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

注:如果确定histogram不会对产生更好的执行计划有帮助,那么更好的做法是将下边这一行:

method_opt => ‘FOR ALL COLUMNS SIZE AUTO’

替换成

method_opt => ‘FOR ALL COLUMNS SIZE 1’

注:您需要将’ Schema_name ‘和’ Table_name ‘替换成真正需要收集的schema和table的名字。

收集某一schema下的所有对象的统计信息

exec dbms_stats.gather_schema_stats( –
ownname => ‘  Schema_name ‘, –
cascade => TRUE, –
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );
注:将' Schema_name '替换成需要收集的schema的名字。

收集database中所有对象的统计信息:

exec dbms_stats.gather_database_stats( –
cascade => TRUE, –
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’ );

参考

NOTE:457926.1 – How to Gather Statistics on SYS Objects and ‘Fixed’ Objects?
NOTE:465787.1 – How to: Manage CBO Statistics During an Upgrade from 10g or 9i into 11g
NOTE:236935.1 – Global statistics – An Explanation
NOTE:237901.1 – Gathering Schema or Database Statistics Automatically in 8i and 9i – Examples
NOTE:44961.1 – Statistics Gathering: Frequency and Strategy Guidelines
NOTE:470316.1 – Using Actual System Statistics (Collected CPU and IO information)
NOTE:743507.1 – How to Benefit from Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in 11g?
NOTE:756734.1 – 11g: Scheduler Maintenance Tasks or Autotasks
NOTE:1226841.1 – How To: Gather Statistics for the Cost Based Optimizer
NOTE:1380043.1 – Selected Performance Related Seminars from Oracle Openworld (OOW)
NOTE:149560.1 – How to Collect and Display System Statistics (CPU and IO) for CBO use
NOTE:452011.1 – Restoring Table Statistics
NOTE:390249.1 – How To Quickly Add/Remove Column Statistics (Histograms) For A Column

Oracle Enterprise Manager Cloud Control 13c发布了

目录 ORACLE, 数据库

今天查找Oracle Enterprise Manager Cloud Control  的信息发现13c已经发布了,Oracle Enterprise Manager Cloud Control  12c还没使用呢,13c竟然发布了。。

下载及介绍: http://www.oracle.com/technetwork/oem/enterprise-manager/downloads/index.html

 

Oracle Enterprise Manager Downloads

Oracle Enterprise Manager Cloud Control 13c Release 2 Plug-in Update 1 (13.2.0.0) New!

Enterprise Manager Base Platform
(Full Installers for OMS, Agent, Repository, Management Plug-ins)

Download for Linux x86-64 (64-bit)
Download for Windows x86-64 (64-bit)
Download for Solaris Operating System (SPARC)
Download for Solaris Operating System (x86-64)
Download for IBM AIX on POWER Systems (64-bit)
Download for HP-UX Itanium (64-bit)

Additional Required Software for Oracle Enterprise Manager Cloud Control 13c Release 2 Plug-in Update 1 (13.2.0.0)

Download Oracle Database

Oracle Business Intelligence Publisher (BIP) 12.1.3.0 binaries are installed along with Enterprise Manager Fresh 13.2.0.0 Install or Upgrade to 13.2.0.0, so it is no longer required to download and install BI Publisher separately.

If Enterprise Manager is to be configured for High Availability, refer to the instructions available in Advanced Install and Configuration Guide here. Refer to the whitepaper here for load balancer configuration.

Database Template (with EM 13.2.0.0 repository pre-configured) for Installing Oracle Enterprise Manager Cloud Control 13c Release 2 Plug-in Update 1 (13.2.0.0)

To know more about how to use these template for installing EM 13.2.0.0 refer Enterprise Manager Cloud Control Basic Installation Guide 13c Release 2 (13.2.0.0) available here.

Download Download DB Templates

Refer to Enterprise Manager Certification Matrix available on My Oracle Support

Oracle Enterprise Manager Cloud Control 13c Upgrade

In order to upgrade from EM versions 12.1.0.4, 12.1.0.5 and 13.1.0.0 to EM 13.2.0.0 plug-in update 1 there is no need to apply any Pre-upgrade console patch or download any 13.2.0.0 Agent Binaries from OTN.

If you have enabled BI Publisher, refer to the load balancer configuration changes here.

If you have deployed any of the plug-ins listed on this page, please download & stage the latest 13c compatible version prior to starting the upgrade process. Detailed instructions are available in the ‘Cloud Control Upgrade Guide’ here.
Oracle Enterprise Manager Cloud Control Mobile

flashback table to timestamp

目录 ORACLE, 数据库

oracle中经常碰到误删除数据的情况,之前都是通过as of timestamp 进行查询,然后进行update等反操作,今天发现可以直接flashbak table to 误操作之前的一个时间点,但是需要开始行迁移即:

alter table[schema.]table_name enable row movement;

语法:flashback table [schema.]table_name[,…n] to {[scn] | [timestamp] [[enable | disable] triggers]}

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm

当然这样的话就直接还原到误删除 的时间点了,有时候不确定是什么时候的误删除,可以通过as of timestamp 查询一下数据是否符合要求,如果符合要求就可以使用

SQL> FLASHBACK TABLE scott.dept TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ’11’ minute);

Flashback complete.

既可。