Thanks for the question, Hans .
Asked: November 16, 2015 - 7:39 am UTC
Last updated: November 18, 2015 - 6:35 am UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi Tom, Chris, Conner
I have taken over a large system consisting of forms and reports running on WebLogic Server 10.3.6.0 using Oracle 11.2.0.4.0 databases.
The databases are loaded with PUBLIC synonyms due to “lazy form-developers”.
I share your feelings about synonyms in general but I have to live with them for now.
For some reason there are also thousands of PRIVATE synonyms pointing to exactly the same objects as the PUBLIC synonym with the same name.
My questions are:
Can there be any reasons having PRIVATE synonyms pointing to the same object as the corresponding PUBLIC synonym (assuming they are always referenced without the schema name)?
Will I gain anything (in performance parsing SQL etc) by dropping the PRIVATE synonyms?
and Connor said...
You will probably actually *lose* a little if you drop the public synonyms.
This is quote from Steve Adams, from the ixora site. The quote is many years old, but I believe it still to be valid.
"...The parse phase for a cached shared SQL statement with fully qualified object references only involves establishing the authorization of the user to execute the statement, if that. However, for an equivalent reference via a private synonym, the synonym must be loaded into the library cache, and a dependency structure must be established. For an equivalent reference via a public synonym, the same is done except that it is a "negative dependency" (that is, the cursor is dependent on the non-existence of an object of that name in each user's schema). The statement also has a positive dependency on the public synonym. Each library cache lookup establishes a lock structure, for which memory must be allocated. Each library cache object access establishes a pin structure, which also needs memory. The same goes for loading the metadata for an object not yet present in the library cache, and this commonly involves dictionary cache access and memory management as well.
For a statement that refers to a single table only, the CPU usage during the parse phase is approximately 1:2:4 for a fully-qualified reference, a private synonym and a public synonym respectively. For complex SQL statements, the cost of synonym usage is even greater. The synonym based solutions involve potentially large library cache dependency tables, and are sensitive to the aging out of the library cache objects representing the table name in the users' own schemas. These library cache objects cannot be "kept". In an instance with high parse rates, the use of synonyms increases CPU usage dramatically, and often causes contention on the latches that control access to the library cache, dictionary cache and shared pool.
The suggestion that a large number of private synonyms can cause performance problems in the library cache because they all hash onto the same hash bucket is mistaken. There can only be one user of each name, and the user name is part of the object name on which the hash is calculated. Thus private synonyms do not increase the risk of library cache hash collisions.
People often claim that synonyms are needed to allow code to be tested against differently named schemas, particularly in development. However, as well as the ALTER SESSION SET CURRENT_SCHEMA command, there are numerous code management techniques to obtain the same flexibility without using synonyms. The ALTER SESSION SET CURRENT_SCHEMA command can be issued from an application startup procedure, without any security concerns. However, in versions prior to 8.1, it must be executed before any other SQL is parsed, or there may be risk of data corruption (which is why it was previously unsupported). "
Benchmarking would validate the statement and I encourage you to do that. But for the time being, I'd either keep the private ones or look at "set current_schema" is thats appropriate for your app(s).
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment