My Oracle Support Banner

Expdp Performance Issue - Datapump expdp is slow and hanging on queries against datapump dictionary views ( SYS.KU$ views) (Doc ID 2704705.1)

Last updated on AUGUST 27, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Sometimes the expdp hangs and takes lot of time during export.
It may hang while exporting indexes,tables,comments or procobj objects or some other object types.

The datapump metadata api generates a select stmt for each object type. Some of the examples of these sqls are below:
The DW sql trace also shows the below sqls to be taking maximum time.
The queries are mainly against datapump dictionary views like SYS.KU$_PROCOBJ_VIEW,SYS.KU$_FHTABLE_VIEW,SYS.KU$_SYNONYM_VIEW,SYS.KU$_CONSTRAINT_VIEW,SYS.KU$_INDEX_VIEW.

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('PROCOBJ_T', '7')), KU$.OBJ_NUM , KU$.SCHEMA_OBJ.NAME , KU$.SCHEMA_OBJ.NAME , 'PROCOBJ' ,
KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_PROCOBJ_VIEW KU$ WHERE KU$.PLSQL IS NOT NULL AND KU$.OBJ_NUM IN
(SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001))) AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE '%' AND
NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='PROCOBJ' AND A.NAME=KU$.SCHEMA_OBJ.NAME AND A.SCHEMA=KU$.SCHEMA_OBJ.OWNER_NAME) AND
NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.SCHEMA_OBJ.OWNER_NAME) AND KU$.CLASS=2 AND KU$.PREPOST=1
ORDER BY KU$.LEVEL_NUM, KU$.TYPE_NUM

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T','7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME ,
KU$.ANC_OBJ.TYPE_NAME ,KU$.BASE_OBJ.NAME ,KU$.BASE_OBJ.OWNER_NAME , KU$.BASE_OBJ.TYPE_NAME ,KU$.SPARE1 ,KU$.TSTZ_COLS ,KU$.XMLSCHEMACOLS ,
KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'TABLE' ,KU$.PARENT_OBJ.NAME , KU$.PARENT_OBJ.OWNER_NAME ,KU$.PROPERTY ,KU$.REFPAR_LEVEL ,
KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,KU$.TRIGFLAG
FROM SYS.KU$_FHTABLE_VIEW KU$ WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.OBJ_NUM IN (SELECT * FROM
TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001))) AND (BITAND(KU$.FLAGS, 536870912)=0)

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('SYNONYM_T', '7')), KU$.OBJ_NUM ,KU$.SYN_LONG_NAME ,KU$.SCHEMA_OBJ.NAME ,'SYNONYM' ,
KU$.SCHEMA_OBJ.OWNER_NAME FROM SYS.KU$_SYNONYM_VIEW KU$ WHERE NOT (BITAND (KU$.SCHEMA_OBJ.FLAGS,16)=16) AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA1 AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE
'%' AND NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE= 'SYNONYM' AND A.SCHEMA=KU$.OWNER_NAME AND KU$.SCHEMA_OBJ.OWNER_NUM =1 ) AND
NOT EXISTS (SELECT 1 FROM SYS.KU$NOEXP_TAB A WHERE A.OBJ_TYPE='SCHEMA' AND A.NAME=KU$.SCHEMA_OBJ.OWNER_NAME)

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('CONSTRAINT_T', '7')), 0 ,KU$.BASE_OBJ.NAME , KU$.BASE_OBJ.OWNER_NAME ,KU$.BASE_OBJ.TYPE_NAME ,KU$.NAME ,KU$.NAME ,
'CONSTRAINT' ,KU$.OWNER_NAME FROM SYS.KU$_CONSTRAINT_VIEW KU$ WHERE NOT (KU$.CON1.CONTYPE=3 AND KU$.CON1.INTCOLS=1 AND KU$.CON1.OID_OR_SETID!=0) AND NOT (KU$.CON1.CONTYPE=
2 AND BITAND(KU$.CON1.PROPERTY,64+4096)!=0) AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001))) AND KU$.BASE_OBJ.OWNER_NAME
LIKE '%' AND KU$.OWNER_NAME LIKE '%' AND (KU$.CON1.CONTYPE NOT IN (5) OR KU$.CON1.IND.TYPE_NUM NOT IN (4,8,9))

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('INDEX_T','7')), KU$.OBJ_NUM ,KU$.ANC_OBJ.NAME ,KU$.ANC_OBJ.OWNER_NAME , KU$.ANC_OBJ.TYPE_NAME ,KU$.SCHEMA_OBJ.NAME ,KU$.SCHEMA_OBJ.NAME ,'INDEX' ,
KU$.PROPERTY ,KU$.SCHEMA_OBJ.OWNER_NAME ,KU$.TS_NAME ,to_char(KU$.TYPE_NUM) ,decode(cardinality(KU$.COL_LIST),0,'1','0')
FROM SYS.KU$_INDEX_VIEW KU$ WHERE NOT KU$.FOR_PKOID=1 AND NOT KU$.FOR_REFPAR=1 AND NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,4)=4 AND KU$.BASE_OBJ_NUM IN (SELECT * FROM
TABLE(DBMS_METADATA.FETCH_OBJNUMS(100001))) AND KU$.BASE_OBJ.OWNER_NAME LIKE '%' AND KU$.SCHEMA_OBJ.OWNER_NAME LIKE '%' AND KU$.TYPE_NUM =2

SELECT /*+ ordered */ O.NAME RSNAME, RU.NAME RSCHEMA, RO.NAME RNAME, RM.PROPERTY, DECODE(RL.ECTX#, 0, DECODE(RM.ECTX#, 0, ' ', '"' || BU.NAME|| '"."' || BO.NAME || '"'), ' ') BONAME, RM.RM_COMMENT
FROM SYS.OBJ$ O, SYS.RULE_MAP$ RM, SYS.OBJ$ RO, SYS.RULE$ RL, SYS.USER$ RU, SYS.OBJ$ BO, SYS.USER$ BU WHERE O.OBJ# = :B1 AND O.OBJ# = RM.RS_OBJ# AND
RM.R_OBJ# = RL.OBJ# AND RL.OBJ# = RO.OBJ# AND RO.OWNER# = RU.USER# AND RM.ECTX# = BO.OBJ#(+) AND BO.OWNER# = BU.USER#(+) AND ((RU.NAME, RO.NAME, 59) NOT IN (SELECT NE.OWNER, NE.NAME, NE.OBJ_TYPE FROM SYS.NOEXP$ NE))

 



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.