Selects Against ALL_SYNONYMS Perform Badly on 10g Release 10.2 (Doc ID 377037.1)

Last updated on JUNE 30, 2014

Applies to:

Oracle Server - Enterprise Edition - Version 10.2.0.1 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.

Symptoms

In some cases queries against the ALL_SYNONYMS view are slower after upgrading to 10.2.0.x

For example:

SELECT /*+ RULE */ COUNT(*)
FROM
ALL_SYNONYMS WHERE OWNER='PUBLIC' AND SYNONYM_NAME='&1'


Oracle 10g Release 10.2:

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       81      0.02       0.00          0          0          0           0
Execute     81      0.05       0.09          0          0          0           0
Fetch       81    355.93     371.05          0   45269282          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      243    356.00     371.15          0   45269282          0          81

Oracle 9i Release 9.2:
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       81      0.02       0.02          0          0          0           0
Execute     81      0.04       0.01          0          0          0           0
Fetch       81      0.03       0.01          0        810          0          81
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      243      0.09       0.05          0        810          0          81

Changes

Changed to a version higher than 9.2 from 9.2 or below.

Cause

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