Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Suggestions Needed: Latch free - library cache

RE: Suggestions Needed: Latch free - library cache

From: Steve Adams <>
Date: Fri, 09 Jan 2004 19:49:26 -0800
Message-ID: <>

Hi All,

Someone has alerted me to this thread, and asked for a comment. On a quick scan, and it seems to me that you've mostly got it right.

The "problem" is that when an SQL statement that refers to its base objects via public synonyms is shared by multiple distinct Oracle users, then name resolution and permission checking need to repeated for each distinct user, and because the results of these actions are cached on the shared cursor, they increase the cost of subsequent such operations. That is, public synonyms cause extended latch retention as well as additional latching.

For example, if 500 distinct users share 200 SQL statements that refer 300 times to 100 base tables via public synonyms. Then there will also be 100 * 500 non-existent objects in both the dictionary cache and the library cache; 200 * 500 cursor authorization structures; and 300 * 500 negative dependency records in the library cache. These last two things are cached as segmented arrays that are scanned linearly - thus the increased latch retention.

If your application doesn't have hundreds of distinct Oracle users, or if you can afford the extra latch gets and longer latch retention, then you will probably not notice all of this unless you start doing library cache dumps.

That is, the use of public synonyms is a major scalability threat, but does not normally cause performance problems.

@ Regards,
@ Steve Adams
@ - For DBAs
@ - For all

Please see the official ORACLE-L FAQ:
Author: Steve Adams

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Fri Jan 09 2004 - 21:49:26 CST

Original text of this message