Expdp For Metadata Is Slow In 11.2.0.3 With Partitioned Objects [ID 1466040.1]

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

- 11.2 Database with partitioned objects:

select partitioned,count(*) from dba_tables group by partitioned;

PAR COUNT(*)
--- ----------
NO 1332
YES 498

- In 11.2.0.3 the Data Pump Export for tables,indexes and alter_procedure is lasting for hours whereas the same export in 11.2.0.1 is much faster, as can be seen below:

db_name version expdp start time expdp end time elapsed 
------------------------------------------------------------------
11.2.0.3 17:12:21 08:56:16 15:43:56 
11.2.0.1 17:11:04 17:42:57 00:31:53

- In 11.2.0.1 with Patch 10416375 installed, the expdp behavior is:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
      Completed 747 TABLE objects in 120 seconds
..........
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
      Completed 734 INDEX objects in 52 seconds
..........
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
      Completed 130 ALTER_PROCEDURE objects in 1 seconds 

- In 11.2.0.3 with Patch 13844935 and Patch 13898265 installed, the expdp behavior is:

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
      Completed 749 TABLE objects in 1413 seconds
........................
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
     Completed 130 ALTER_PROCEDURE objects in 11200 seconds
.............
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
      Completed 837 INDEX objects in 1106 seconds

- TKPROF output generated for the DW trace file shows lots of time is being spent in SYS.KU$_PHTABLE_VIEW and SYS.KU$_INDEX_VIEW views:

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$.PART_OBJ.PARTOBJ.DEFTS_NAME ,KU$.TRIGFLAG 
FROM
 SYS.KU$_PHTABLE_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(200001))) AND  (BITAND(KU$.FLAGS,536870912)=0)

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 (KU$.TYPE_NUM=1 AND KU$.INTCOLS=1 AND KU$.OID_OR_SETID!=0) AND  
  NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,
  4)=4 AND   KU$.SCHEMA_OBJ.OWNER_NAME IN (SELECT UNIQUE object_schema FROM 
  "SYS"."SYS_EXPORT_TABLE_01" WHERE process_order = -55 AND duplicate BETWEEN 
  1 AND 1) AND   KU$.TYPE_NUM NOT IN (2,4,8,9) AND   KU$.BASE_OBJ_NUM IN 
  (SELECT * FROM TABLE(DBMS_METADATA.FETCH_OBJNUMS(200001))) AND  
  BITAND(KU$.PROPERTY,16)!=16 ORDER BY KU$.SCHEMA_OBJ.OWNER_NUM, KU$.SCHEMA_OBJ.DATAOBJ_NUM

- From AWR report we see high logical reads and Full Table Scan for:

Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads Tot
==============================================================================
SYS SYSTEM TABSUBPART$   TABLE 49,245,408 46.94 
SYS SYSTEM TABCOMPART$   TABLE 43,975,200 41.92 
SYS SYSTEM I_INDCOMPART_BOPART$   INDEX 2,899,584 2.76Changes

Upgrade to 11.2.0.3

Cause

 The root cause is that metadata for partitioned objects is permanently  stored  in a variety of dictionary tables. The dictionary tables store partition numbers. However, the various
pieces of metadata 
are correctly related only using 'relative fragment numbers'. The metadata API prior to 11.2.0.3 operated on (stored) partition numbers rather than (computed) relative partition numbers.
As a result, extracted metadata could be incorrect. In 11.2.0.3, views used by the metadata API were changed to use views which provide relative fragment numbers, rather than directly accessing 
dictionary tables and using partition numbers.
  The Development Team determined the root cause of this issue in Bug 14006804: EXPDP SLOW FOR TABLES, INDEXES, CONSTRAINTS AND ALTER_PROCEDURE and the real fix was provided
in 
Bug 14192178 - BUG 14006804 FIX DOES NOT RESOLVE THE PERFORMANCE ISSUE.

Solution

- The Patch 14006804 is not fixing the problem and the fix has been withdrawn. The correct fix comes with Patch
14192178.

- The following options are available to have the issue from Bug 14006804 fixed:

- Apply 11.2.0.4 when available

OR

- Install generic Patch 14192178 on top of 11.2.0.3 and run post install scripts:

cd rdbms/admin
run sqlplus as sysdba

SQL> @catnomtt.sql
SQL> @catnomta.sql
SQL> @dbmsmeta.sql
SQL> @dbmsmeti.sql
SQL> @dbmsmetu.sql
SQL> @dbmsmetb.sql
SQL> @dbmsmetd.sql
SQL> @dbmsmet2.sql
SQL> @catmeta.sql
SQL> @prvtmeta.plb
SQL> @prvtmeti.plb
SQL> @prvtmetu.plb
SQL> @prvtmetb.plb
SQL> @prvtmetd.plb
SQL> @prvtmet2.plb
SQL> @catmet2.sql
-- Recompile all invalid objects.
SQL> @utlrp

References

BUG:14006804 - EXPDP
SLOW FOR TABLES, INDEXES, CONSTRAINTS AND ALTER_PROCEDURE
BUG:14192178 - BUG 14006804
FIX DOES NOT RESOLVE THE PERFORMANCE ISSUE
BUG:13960827 - IMPORT
THROUGH NETWORK_LINK SLOW AFTER UPGRADE TO 11.2.0.3