Re: Suggestions Needed: Latch free - library cache

Murali Vallath Sat, 10 Jan 2004 14:14:24 -0800

Rachel,

I think we had a similar situation in my previous life, when we had to get the programmers to modify their code to trace specific areas of the application. The way we went about doing this (in this case they had a concept of using a catalog.xml file where all the SQL queries got stored and the Java called specific queries identified by TAGS) is define the 10046 event in the catalog and called the on off routines using specific TAG's to trace.

<sql><![CDATA[

alter session set events '10046 trace name context forever, level 12'

]]></sql>

</query>

<sql><![CDATA[

alter session set events '10046 trace name context off'

]]></sql>

</query>

Java code snippet to invoke the trace queries:

stmt.executeUpdate(sql);

where sql is the 'traceTurnOn' or 'traceTurnOff' queries retrieved from the catalog,xml file mentioned earlier.

Rachel Carmichael <[EMAIL PROTECTED]> wrote:

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 <[EMAIL PROTECTED]>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" <[EMAIL PROTECTED]>
> 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 <[EMAIL PROTECTED]>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: [EMAIL PROTECTED]
> > >
> > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line contai! ning: 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

Re: Suggestions Needed: Latch free - library cache

Reply via email to