how do you feel about connection pooling? Our software engineers implemented
that here? Am I wrong to be concerned about large numbers of users using the
same named user?
- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Saturday, January 10, 2004 6:39 AM
> 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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ryan
INET: ryan.gaffuri_at_cox.net
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 - 06:44:25 CST
