RE: RE: connection pooling from an application server to oracle

Ryan,

My understanding is that the transactions are packaged differently. For example, a trans that would come thru the app server will be packaged - like begin trans, exec trans and end trans - all as one call to the database. Almost like a two-phased commit (indeed, the errors that get reported to me are all distributed transaction errors). Because everything is packaged together, the app server waits for an open slot in the connection, sends the total transaction, and gets out. Almost like scheduling disk writes.

And with DCOM or COM+, MTS is required (at least on my project). How this all compares to Oracle Connection pooling, I don't know. Didn't get the chance to propose a test. Don't know about you, but these decisions were made without the DBA's being involved, so I just went with them. And from my point of view, what does it matter? As long as it works, I don't want to start a fight I can't win. Got to pick your battles.

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, December 15, 2003 9:24 AM
To: Multiple recipients of list ORACLE-L

so if user A has 10 DML statements to do in one transaction. The application server will be smart enough to to only allow user 'A' to use that connection until a 'commit' is issued?

how does application level connection pooling compare to MTS?
>
> From: Rachel Carmichael <wisernet100_at_yahoo.com>
> Date: 2003/12/15 Mon AM 09:04:26 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: Re: RE: connection pooling from an application server to oracle
>
> nope..... the application server watches the connections and
> transactions
>
> the main problem is it's very hard to do a 10046 trace on a session
> with connection pooling going on, as a user session may actually be
> several distinct database sessions.
>
>
>
> --- ryan_oracle_at_cox.net wrote:
> > doesnt this force you to commit after every single DML statement?
> > >
> > > From: "Mercadante, Thomas F" <thomas.mercadante_at_labor.state.ny.us>
> > > Date: 2003/12/15 Mon AM 08:36:09 EST
> > > To: "'ORACLE-L_at_fatcity.com'" <ORACLE-L_at_fatcity.com>
> > > CC: "'ryan_oracle_at_cox.net'" <ryan_oracle_at_cox.net>
> > > Subject: RE: connection pooling from an application server to
> > oracle
> > >
> > > Ryan,
> > >
> > > This is becoming for normal. There are a lot of software pieces
> > that do
> > > connection pooling - basically, everybody is plaing in everbody
> > else's
> > > space.
> > >
> > > I have a couple of projects where the app-server does the
> > connection
> > > pooling. One using Dcom and the other IBM WebSphere.
> > >
> > > From your point of view, it's just one less thing to worry about.
> > The
> > > number of db connections will be relatively small. The app server
> > keeps
> > > track of transactions. As long as they say it works, it's not your
> > problem.
> > >
> > > Tom Mercadante
> > > Oracle Certified Professional
> > >
> > >
> > > -----Original Message-----
> > > From: ryan_oracle_at_cox.net [mailto:ryan_oracle_at_cox.net]
> > > Sent: Monday, December 15, 2003 7:59 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: connection pooling from an application server to oracle
> > >
> > >
> > > 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.
> > >
> > > they compartmentalize stuff here, so Im having trouble figuring out
> > exactly
> > > how this affects the database and how to monitor performance. All I
> > know is
> > > that I see a handful of constantly open dedicated connections. I
> > have been
> > > told that this is actually alot of users connecting to the
> > database.
> > >
> > > 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?
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > --
> > > Author: <ryan_oracle_at_cox.net
> > > INET: ryan_oracle_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: <ryan_oracle_at_cox.net
> > INET: ryan_oracle_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).
>
>
> __________________________________
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard
> http://antispam.yahoo.com/whatsnewfree
> --
> 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_oracle_at_cox.net
  INET: ryan_oracle_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: Mercadante, Thomas F
  INET: thomas.mercadante_at_labor.state.ny.us

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).