Re: connection pooling from an application server to oracle

Duly noted. I guess that Saddam Hussein related excuses will no longer work, either. I'll have to thoroughly comb my book of excuses.

On 12/15/2003 01:54:35 PM, Paul Drake wrote:
> Mladen,
>
> Tread lightly on the "solar flares" my friend.
> Last time we had some severe flares, 2 of my client sites lost their air conditioning units, which resulted in media failures. Hadn't had any air conditioning outages in at least 3 years at sites. Just a strange (circumstantial) coincidence, I thought not.
>
> Pd
>
> Mladen Gogala <mladen_at_wangtrading.com> wrote:
> Such app server side connection pooling is a nightmare for a DBA.
> Http pages ([aj]sp junk,php,mod_plsql,mod_perl using Apache::DBI) will
> each open one connection per page, as well as most CGI scripts (Adam von Nieda's
> exquisite oracle tool on http://www.oracletool.com is what comes to mind).
> If there is a problem with SQL, DBA has a way of finding out who is
> doing what because each session is connected to a single server process,
> which can then be located. With app server pooling, you have a single user
> logged in multiple times and each dedicated server can be used to execute any
> single SQL statement. With iPlanet, it looks like this:
> Application invokes bean, pumpkin or servlet which has something to do
> with oracle. The app server pooling mechanism will allocate one of the
> already established dedicated server connections, which will perform the
> work. When the same user executes another vegetable (EJB or servlet), he
> or she may do so within a different dedicated server connection. We have a
> situation in which a single user utilizes several dedicated server connections
> within a single logical screen. Whenever faced with a choice, I try preventing
> that from happening and make users connect to dedicated servers with different
> usernames and sessions which can be tied to the user. If I can't prevent it,
> I always use cache hit ratio (which tells me nothing) for blame shifting game.
> Buffer cache hit ratio can always be interpreted in such a way that you can
> blame someone else. It's a DBA version of sun flares excuse found on Simon
> Trevaglia's wonderful BOFH web site. I am a DBA and it is my job to interpret
> database statistics. The database statistics tells me that application server
> pooling is naughty and sinful and should not be utilized. Q.E.D. It's not me,
> it's BCHR. If I am persistent enough, I can get it off the system. Who was
> saying that BCHR isn't useful?
>
> On 12/15/2003 09:49:26 AM, Justin Cave wrote:
> > At 05:59 AM 12/15/2003, ryan_oracle_at_cox.net wrote:
> > >The software engineers here are using an application server with
> > >connection pooling to connect to our oracle instances.
> > >They are doing it with a dedicated connection to Oracle. No MTS.
> > >
> > >This concerns me. how do you handle transaction control in this type of
> > >environment? in this type of environment do you have to commit after every
> > >DML statement? since multiple users will access the database with the same
> > >conneciton?
> >
> > You may have only, say, 10 connections that are servicing 100 users, but
> > each user will appear to have his or her own, dedicated connection. This
> > works because users are going to tend to "open" a connection, do some work,
> > and "close" a connection in short order.
> >
> > The classic example of the utility of a connection pool is a web
> > site. Since HTTP is a stateless protocol, most JSP pages will open and
> > close a connection each time a user requests a page. Within the scope of
> > generating the page, the JSP engine will issue a few SELECT statements and
> > may do a little DML. Since it takes a fraction of a second (we hope) to
> > generate a single web page, opening & closing a physical connection would
> > obviously take an order of magnitude more time than generating the contents
> > of the page, the application server can be set to maintain a set of
> > permanent database connections which it will parcel out to the individual
> > JSP engines. As an added benefit, you now only need a number of
> > connections in the connection pool equal to the number of truly concurrent
> > sessions you have. Since users spend far more time digesting page contents
> > than the servlet engine spends building them, this typically means that you
> > can service hundreds of users with tens of connections. From a transaction
> > control standpoint, however, the important thing to remember is that each
> > connection will be assigned to a particular user for the length of that
> > user's transaction.
> > Justin Cave
> > Distributed Database Consulting
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Justin Cave
> > INET: jcave_at_cableone.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).
> >
>
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mladen_at_wangtrading.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).
>
> ---------------------------------
> Do you Yahoo!?
> New Yahoo! Photos - easier uploading and sharing

Mladen Gogala
Oracle DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).