| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Suggestions Needed: Latch free - library cache
Having worked in an environment where every user was a distinct named
user, and I therefore could (and at points did) have something 1700
distinct connections (yes, I said one thousand seven hundred).....
I like connection pooling. It limits the stress on the database, because the number of sessions is limited to the maximum of the connections. And I do work with them on that.
It has its drawbacks. Most notable, and one I did not get a good answer to at the Server Technology Forum at UKOUG, is that you cannot really do a 10046 trace on a session, as you can't distinguish sessions. Gaja says you can do that in 10g but telling me to upgrade to a not-yet-released piece of software doesn't help me now.
I'm working on a way around that, I *may* have one, but it's untested for the moment (I have to bully the programmers into changing code, which is as likely to happen as it is for me to win the lottery without buying a ticket).
In any case, connection pooling appears to be the way of the future for forward-facing web applications, so we have to deal with it
Rachel
--- 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).
-- 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 - 14:19:25 CST
 |
 |