Expdp for metadata is slow in 11.2.0.3 with partitioned objects [ID 1466040.1]

Applies:

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
11.2.0.1 17:11:04 17:42:57

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

Processing object type database_export/Schema/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
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
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:

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.76 changes

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 cocould 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