RE: Suggestions Needed: Latch free - library cache

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
@ http://www.ixora.com.au/ - For DBAs
@ http://www.christianity.net.au/ - For all

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).