Re: Suggestions Needed: Latch free - library cache

If you are on Oracle 9i, try connection identifier using DBMS_SESSION.SET_IDENTIFIER for each of the client sessions. Even if teh USERNAME in V$SESSION shows your named user, the field CLIENT_IDENTIFIER will show the actual user (say, the application userid). The trace files will show that, even auditing will, making it as useful as the USERNAME value.

You may find an article of mine on OTN
http://otn.oracle.com/pub/articles/nanda_fga_pt2.html which deals with Fine Grtained Auditing, but talks about the issues you have mentioned.

HTH. Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, January 10, 2004 12:04 PM

> I'm concerned about other problems. We may have 30,000 concurrent users,
> sharing 5 or so named users. My big concern is maintenance and tracing.
>
> Has anyone worked with this type of environment? How do you build tracing
> into the front end so I can tell which sid, serial# is experience
problems?
> The middle tier is complete, so any changes I recommend need to be
moderate
> since we have strict deadlines.
>
> Anyone do anything like this? Front end is complete. However, we signed a
> new client who has radically more users than we have had in the past. This
> limits my ability to add features that help maintain the database.
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Saturday, January 10, 2004 11:14 AM
>
>
> > 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).
>
> --
> 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: Arup Nanda
  INET: orarup_at_hotmail.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).