Re: RE: connection pooling from an application server to oracle
I might be totatly off here, but I think it depends on the type of the pool. For example in OCI land there are two - connection pool and session pool. The former is stateful, while the latter is stateless. Again unless I am totally off base, with a session pool I think your session gets returned to a pool manager (for potential re-use) after _about_ every statement, effectively making tx:stmt relationship to become about 1:1. This is exactly what I am dealing with here and as a result when user navigates on the screen the app server instead of issuing say 30 selects, adds additional 30 implicit rollbacks, one after each select. This is also the reason I think I see pattern like this for example all over on DMLs: XCTEND rlbk=0, rd_only=0-- explicit commit here WAIT #0: nam='SQL*Net message to client'... WAIT #0: nam='SQL*Net message from client'... XCTEND rlbk=1, rd_only=1-- implicit rollback right after commit due to OCITransRollback() call Thanks, Boris Dali. --- [EMAIL PROTECTED] wrote: > doesnt this force you to commit after every single > DML statement? > > > > From: "Mercadante, Thomas F" > <[EMAIL PROTECTED]> > > Date: 2003/12/15 Mon AM 08:36:09 EST > > To: "'[EMAIL PROTECTED]'" > <[EMAIL PROTECTED]> > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] > 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). __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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, se
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 <[EMAIL PROTECTED]> > Date: 2003/12/15 Mon AM 09:04:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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. > > > > --- [EMAIL PROTECTED] wrote: > > doesnt this force you to commit after every single DML statement? > > > > > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > > > Date: 2003/12/15 Mon AM 08:36:09 EST > > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > 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: <[EMAIL PROTECTED] > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Net
Re: RE: connection pooling from an application server to oracle
application connection level pooling = nothing for the dba to worry about (except making sure processes parameter is large enough) MTS= DBA has to manage it all and to your question, the answer is yes --- [EMAIL PROTECTED] wrote: > 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 <[EMAIL PROTECTED]> > > Date: 2003/12/15 Mon AM 09:04:26 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > 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. > > > > > > > > --- [EMAIL PROTECTED] wrote: > > > doesnt this force you to commit after every single DML statement? > > > > > > > > > From: "Mercadante, Thomas F" > <[EMAIL PROTECTED]> > > > > Date: 2003/12/15 Mon AM 08:36:09 EST > > > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > > 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: <[EMAIL PROTECTED] > > > > 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] (not
Re: RE: connection pooling from an application server to oracle
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 <[EMAIL PROTECTED]> > Date: 2003/12/15 Mon AM 09:04:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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. > > > > --- [EMAIL PROTECTED] wrote: > > doesnt this force you to commit after every single DML statement? > > > > > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > > > Date: 2003/12/15 Mon AM 08:36:09 EST > > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > > 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: <[EMAIL PROTECTED] > > > 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: <[EMAIL PROTECTED] > > 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 mai
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. --- [EMAIL PROTECTED] wrote: > doesnt this force you to commit after every single DML statement? > > > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > > Date: 2003/12/15 Mon AM 08:36:09 EST > > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] > 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!? 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: [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).
Re: RE: connection pooling from an application server to oracle
doesnt this force you to commit after every single DML statement? > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > Date: 2003/12/15 Mon AM 08:36:09 EST > To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > CC: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > 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: <[EMAIL PROTECTED] > 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: <[EMAIL PROTECTED] 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).
