I have a database with lots of partitioned tables and indexes and when I try to get the DDL of a partitioned table the query runs for hours and does not end.
I tried do gather data dictionary statistics but it had no impact on performance.
Can anybody help me find what's causing this performance problem?
select dbms_metadata.get_ddl('TABLE', 'TABLE1') from dual;
On the "Top Activity" of the Database Control console, this is the query that's running:
SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM
FROM SYS.KU$_PHTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 AND KU$.SCHEMA_OBJ.NAME=:NAME1 AND KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2;
This is a known problem.
The optimizer is not pushing the predicate (icp.obj# = o.obj_num) to get the minimum rows at INDCOMPART$ level is the cause of the slowness. Hence, this table is read in a full scan, and at customer end it has several millions of rows (lots of schemas each with lot of composite partitioned tables and indexes).
This is a bug 13844935. You should get a patch from oracle Metalink.