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:
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:
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:
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:
==============================================================================
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 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:
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_PROCEDUREBUG:14192178 - BUG 14006804 FIX DOES NOT RESOLVE THE PERFORMANCE ISSUE
BUG:13960827 - IMPORT THROUGH NETWORK_LINK SLOW AFTER UPGRADE TO 11.2.0.3