Last updated on JULY 17, 2017

Applies to:

Symptoms

• 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 :
  SELECT *
  FROM all_synonyms
  WHERE owner = '<owner>'
  AND table_name = '<table-name>';

  or

  SELECT table_name, table_owner
  FROM all_synonyms
  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.

Changes

Installation and usage of 11.2 onwards or Migration from 10.2. to 11.2. or higher

Cause