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:

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.

Cause

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