Query on ALL_TYPE_ATTRS is Very Slow (Doc ID 1489204.1)

Last updated on JUNE 05, 2023

Applies to:

Symptoms

Oracle JDBC driver  executes the below query when retrieving metadata information for complex data types.  The execution time of this query is very high and affects performance.

SELECT ATTR_NO, ATTR_NAME, ATTR_TYPE_NAME, ATTR_TYPE_OWNER
FROM ALL_TYPE_ATTRS
WHERE TYPE_NAME IN
(SELECT TABLE_NAME
FROM USER_SYNONYMS START WITH SYNONYM_NAME = :tname CONNECT BY NOCYCLE PRIOR TABLE_NAME = SYNONYM_NAME UNION
SELECT :tname
FROM DUAL) ORDER BY ATTR_NO

Changes

Cause

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