oracle expdp导出元数据TABLE COMMENT过程hang住遭遇bug14192178

expdp导出元数据时遭遇bug,TABLE COMMENT过程hang住了

在做expdp过程中，hang在以下位置几十个小时，
..Processing object type SCHEMA_EXPORT/TABLE/COMMENT

通过expdp过程指定TRACE=480300 ：

Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump (文档 ID 286496.1)

输出hang的trace 日志信息如下：
KUPW:12:24:27.492: 1: TABLE
KUPW:12:24:27.492: 1: NEW_SR_INCOME
KUPW:12:24:27.493: 1: WC_NSUP2_R_ACCL_ANSW_BN_MA_F
KUPW:12:24:27.494: 1: Base Process info: 41996 and processing status: C and processing state: R
KUPW:12:24:27.494: 1: new state: R
KUPW:12:24:27.494: 1: new status: C
KUPW:12:24:27.494: 1: In procedure BUILD_SUBNAME_LIST with COMMENT:.
KUPW:12:24:27.494: 1: In function NEXT_PO_NUMBER
KUPW:12:24:27.494: 1: KUPF$FILE.WRITE_LOB called.
KUPW:12:24:27.498: 1: KUPF$FILE.WRITE_LOB returned.
KUPW:12:24:27.498: 1: MD FilePieces Count: 1
KUPW:12:24:27.498: 1: FORALL called.
KUPW:12:24:27.510: 1: FORALL returned.
KUPW:12:24:27.511: 1: DBMS_LOB.TRIM called. v_md_xml_clob
KUPW:12:24:27.511: 1: DBMS_LOB.TRIM returned.
KUPW:12:24:27.511: 1: DBMS_METADATA.FETCH_XML_CLOB called. Handle: 100001

同时再结合10046事件，分析出hang在以下系统表：
DW slaves showing time executing SQL likely to be of the form    "SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(...FROM SYS.KU$_ACPTABLE_VIEW..."

10046 trace expdp进程方法如下：

游客，如果您要查看本帖隐藏内容请回复

通过诊断后，确认bug：14192178 ，通过对数据库进行patch后解决。

Bug 14192178 - EXPDP of partitioned table can be slow (文档 ID 14192178.8)

Description

This problem is introduced in 11.2.0.3

Datapump export (EXPDP) of partitioned objects can be slow compared to earlier releases.
There have been several separate bug fixes which attempted to address this expdp performanceissue for various internal queries such as bug 13844935, bug 13914808 , bug 13898265 and bug 14006804.
This fix effectively supersedes all of those fixes and can also help for some IMPDP / import operations and some DBMS_METADATA queries.

Rediscovery Notes
Observe poor performance of datapump export of partitioned objects in 11.2.0.3 DW slaves showing time executing SQL likely to be of the form   
"SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2(...FROM SYS.KU$_ACPTABLE_VIEW...

" Workaround None Note:
For interim patches you should only need the fix for this bug.   
Fixes for earlier bugs 13844935, bug 13914808 , bug 13898265 and bug 14006804 should  not be required if you have this fix (14192178) Getting a Fix Use one of the "Fixed" versions listed above (for Patch Sets / bundles use the latest version available as  contents are cumulative - the "Fixed" version listed above is  the first version where the fix is included) or You can check for existing interim patches here: Patch:14192178

好贴，讲得清楚,详细。可操作性强

好好学习，了解一下下！！！！！！！！