Steve,
By "distinct user" do you mean distinct username? Or sid/serial#
combination? In my case, we use connection pooling, while there may be
up to 300 sessions, they are all the same named user.
Rachel
- Steve Adams <steve.adams_at_ixora.com.au> wrote:
> 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).
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
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).
Received on Sat Jan 10 2004 - 05:39:24 CST
