My Oracle Support Banner

DBMS_METADATA.GET_DDL: Query Against SYS.KU$_INDEX_VIEW Is Slow In 11.2.0.3 (Doc ID 1459841.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

The below query runs for hours in 11.2.0.3:

SELECT /*+all_rows*/ SYS_XMLGEN(VALUE(KU$),
XMLFORMAT.createFormat2('INDEX_T', '7')), KU$.OBJ_NUM ,KU$.SCHEMA_OBJ.NAME
,'INDEX' ,KU$.SCHEMA_OBJ.OWNER_NAME
FROM SYS.KU$_INDEX_VIEW KU$
WHERE NOT BITAND(KU$.BASE_OBJ.FLAGS,128)!=0 AND
KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA1


The (event 10046) trace file for the above SQL statement shows a high number of rows for WINDOW SORT and Full Table Scan on INDCOMPART$:

       608        608        608       VIEW  INDCOMPARTV$ (cr=350208 pr=0 pw=0 time=14010662 us cost=1 size=985088 card=30784)
  18917920   18917920   18917920        WINDOW SORT (cr=350208 pr=0 pw=0 time=13964408 us cost=160 size=497840 card=31115)
  18917920   18917920   18917920         TABLE ACCESS STORAGE FULL INDCOMPART$ (cr=350208 pr=0 pw=0 time=2508966 us cost=158 size=497840 card=31115)
       608        608        608       TABLE ACCESS BY INDEX ROWID IND$ (cr=617 pr=0 pw=0 time=4480 us cost=1 size=12 card=1)

Changes

Upgrade to 11.2.0.3

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.