Selects Against ALL_SYNONYMS that Filter Rows Based on Attributes Such as the Synonym or Owner Name Perform Slowly
(Doc ID 1242844.1)
Last updated on FEBRUARY 14, 2019
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 to 18.104.22.168 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
- Poor performance may be encountered with queries against ALL_SYNONYMS that contain predicates to filter rows based on attributes such as the synonym name or owner name. For example, poor performance may be encountered when querying ALL_SYNONYMS with queries such as :
WHERE owner = '<owner>'
AND table_name = '<table-name>';
orSELECT table_name, table_owner
WHERE owner = 'PUBLIC'
AND synonym_name = '<synonym-name>';
- Dictionary and Fixed table statistics have been gathered and are up to date. See:
<Document 457926.1> How to gather statistics on SYS objects and fixed_objects?
<Document 798257.1> GATHER_FIXED_OBJECTS_STATS Considerations
- The problem may be more noticeable if the database has been upgraded from and earlier release (e.g. between 10.2 and 11.2) but this is not a necessary symptom; the database does not have to have been upgraded.
Installation and usage of 11.2 onwards or Migration from 10.2. to 11.2.
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