Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Suggestions Needed: Latch free - library cache

Re: Suggestions Needed: Latch free - library cache

From: Murali Vallath <murali_vallath_at_yahoo.com>
Date: Sat, 10 Jan 2004 12:59:25 -0800
Message-ID: <F001.005DC5E8.20040110125925@fatcity.com> 






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.


 





<query name="traceTurnOn">


<sql><![CDATA[ 


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


]]></sql>


</query>




<query name="traceTurnOff">


<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 <wisernet100@yahoo.com> 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 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" 
> 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 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@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@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@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@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@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@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@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@fatcity.com (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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Murali Vallath
  INET: murali_vallath_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:59:25 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US