Re: connection pooling and oracle

On Fri, 12 Dec 2003 10:48:07 -0800, Ryan Gaffuri wrote:

> 1. Do you need to commit after every sql statement? If there are just
> a few sessions open and another user comes in. He commits, your data
> will be committed anyway. if so doesnt this kill recovery with all
> sorts of bad data problems and inconsistencies? Perfomance must be
> impacted also right?

No, you don't need to commit after every SQL statement. Connection pooling from oracle side means that client processes are using shared sockets. This saves you communication bandwidth at the expense of higher processing cost (listener, dispatchers and the network layer have more work to do). You still get the traditional MTS model when listener connects process to dispatcher, dispatcher puts request in the input queue, request is picked up by the first idle shared server, processed, and put back on the dispatcher queue, where dispatcher picks it up and returns back to the client process. Sessions are still separate entities and the fact that two client processes are using a shared socket to connect to the same dispatcher doesn't mean that their database sessions influence each other in any way. Commit in one session has zero effect in another. Pooling in iPlanet or WebLogic sense means that the application server has several (usually dedicated) sessions, all logged in under the same username, and can pick any of these connections to perform a given action. user is usually not tied to a session and can (ab)use any of the free sessions. That is, usually, a nightmare for a DBA and, if my advice is welcome, I must advise against application server pooling. Why is it such a nightmare? As I've said before, user is not tied to a session. When things work slowly, the DBA will suffer the first hit and his phone will start to ring. Question that will be yelled into the phone is usually: why is the database slow today? You cannot monitor particular session, because users are scattered over several sessions. The only thing you can do is to monitor the pool of dedicated sessions and try to conclude whether anything is wrong. Find the most expensive SQL, find how many sessions are idle, how many are burning CPU time. If all the sessions are busy, then there are more users then the pool of available sessions can handle. If all the sessions are idle, app server is either down, frozen or acting up (I have ample experience with a haunted iPlanet 6.5). To make things better, one user can use one session to lock few records and then attempt to update those records from another session. Voila, here we have a deadlock within a single logical session. Application server pooling is a really efficient way to shoot yourself in a foot, with a 155mm howitzer and do it repeatedly, until you run out of feet.

-- 
None of us is as dumb as all of us.
(http://www.despair.com/meetings.html)