From:Steve Adams
Date:05-Dec-2000 11:29
Subject:   Public synonym performance

Oracle resolves all names while parsing, and the query execution plan generated is the same whether or not those names were resolved via synonyms. So there can be no difference in query execution performance. There is a small difference in parsing, but it goes the other way. A reference via a public synonym requires additional library cache and dictionary cache lookups. However, the performance impact should not be noticeable.

What may be more significant is that public synonym usage clutters the library cache and dictionary cache with information needed to track the negative dependencies on non-existent objects. If JONES and KING both refer to SCOTT.EMP and SCOTT.DEPT via public synonyms, then cache entries are needed to represent the non-existent tables JONES.EMP, KING.EMP, JONES.DEPT and KING.DEPT, and all dependent SQL statements must have negative dependencies on all these objects. With say 1000 users, 200 tables, and 1000 SQL statements each accessing 2 tables on average, you would have 200,000 non-existent object records and 2,000,000 negative dependency records. This clutter can cause latch contention in the library cache, dictionary cache and shared pool.

Your results which appear to favour public synonyms may be due to caching effects and the order in which you tested the two alternatives.

I have a situation where my queries run faster when I use a public synonym. Why would this happen?