Ryan:
Same named user with large number of connections is not a problem.
Things will become bad only IFF the large number of different users
using the same set of public synonymns.
KG
- Ryan <ryan.gaffuri_at_cox.net> wrote:
> 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).
Have a nice day !!
Best Regards,
K Gopalakrishnan,
Bangalore, INDIA.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: K Gopalakrishnan
INET: kaygopal_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 - 10:14:26 CST
