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: K Gopalakrishnan <>
Date: Fri, 09 Jan 2004 11:09:27 -0800
Message-ID: <>


I have not followed the thread completely. So I may be missing something obvious ;)


Many applications (for example Oracle Applications) use public synonyms heavily and running with better (or acceptable) performance. We should not really worry about the milli second performance improvements comparing with the coding/application development flexibilities offered by public synonymns.

If you look at Steve's test carefully, the improvement or over head in the public synonym to private synonym is around 10% of the CPU time and latch gets. How much performance improvement you can expect in practical systems with the additiona 10% of latch gets/CPU times comparing with the overall system performance. I would expect less than (LESS THAN) 1-2% in total response time.


The actual over head is coming from the negative dependency tracking or high version count in the Library cache. But this will be an issue only  when too many users are connected and accssing the database with too many accounts (different parsing user_id). But this is rarely a situation in many of the packaged application as most of the application connects to the database using a single account (like 'APPS' user in Oracle eBusiness suite) and no negative dependency or multi version of SQLs are an issue here (at least in a single instance oracle). The over head can be little higher in RAC environment as the Library Cache and Row cache is globally co-ordinated.

However I have not really seen major problem (may be I have not observed them too keen) with the public synonyms as most of the packaged applications I have worked are using a single oracle account to connect to the database. It is just my personal observation, and may be Jonathan can have a different opinion.


PS : Aplogies if some one has already addressed this issue, I have been traveling and didn;t have enough free time to read all the posts. Seen lots of posts flooding on this topic and jumped in out of curiosity.

Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
Please see the official ORACLE-L FAQ:
Author: K Gopalakrishnan

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 - 13:09:27 CST

Original text of this message