Expdp For Metadata Is Slow In 11.2.0.3 With Partitioned Objects [ID 1466040.1]
来源:互联网 发布:淘宝前十女装店 编辑:程序博客网 时间:2024/06/09 18:38
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
- Expdp For Metadata Is Slow In 11.2.0.3 With Partitioned Objects [ID 1466040.1]
- Checklist for Slow Performance of (expdp) (impdp) [ID 453895.1]
- WARN Fetching topic metadata with correlation id 955 for topics [Set(test3)] from broker [BrokerEndP
- It is too slow for git sendmail.
- ORA-31655: no data or metadata objects selected for job
- There is no getter for property named 'id' in 'clas
- partitioned for hive
- Using setTimeout in objects with Mootools
- Functions for Working with Objects matlab
- Why Is the Network in Ubuntu That Slow!!
- Inside Buffer Optimizing for Slow Motion Video in iOS
- PropertyEnumerators For Entity Objects in LiteMDA
- Typical memory usage for objects in java
- How to filter objects by user id with tastypie?
- AX 中识别被破坏的表对象 Identifying damaged Table objects in the AOT MetaData
- IMPDP 报错 ORA-31655: no data or metadata objects selected for job问题
- 关于DOM element with id in Element cache is not the same as element in the Dom解决方法
- Mybatis-There is no getter for property named 'id' in 'class java.lang.String'
- HDU 1407 测试你是否和LTC水平一样高
- SSL-用Keytool和OpenSSL生成和签发数字证书
- SSL-用OpenSSL命令行生成证书文件
- 面试题7:用两个队列实现栈
- 这11道题据说要智商200的人才能全解出来
- Expdp For Metadata Is Slow In 11.2.0.3 With Partitioned Objects [ID 1466040.1]
- 在.NET使用JSON作为数据交换格式(转http://blog.csdn.net/zhoufoxcn/article/details/6254657)
- YUV数据占用内存大小
- 程序员的八个级别
- 面试题20:栈的压入、弹出序列
- 黑马程序员--Java基础--异常
- 编程调试和诊断的五大规则
- [转自博客园]Android网络连接判断与处理
- 自己动手设计代码编辑器——(六)自己写XML管理类