Selects Against ALL_SYNONYMS that Filter Rows Based on Attributes Such as the Synonym or Owner Name Perform Slowly
Last updated on OCTOBER 12, 2017
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 to 188.8.131.52 [Release 11.2]
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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms