Re: connection pooling and oracle

On 12 Dec 2003 10:48:07 -0800, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:

>Bare with me on this. I dont know much about connection pooling. My
>take on connection pooling is that you open a small number of sessions
>to the database and all users connect to a middle tier which decides
>who connects to the database. Am I correct? I have some questions
>about how this affects oracle.
>
>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?
>
>2. In shared server you really want to use very short transactions due
>to the fact that there are only a few connections to the database. Im
>assuming the same is true for conneciton pooling?
>
>Any comments would be appreciated.

The problem here is you have various levels of connection pooling. You are describing connection pooling by means of an application server. Oracle also has connection pooling available in MTS Connection pooling means a connection can be reused by a different client session. There is no limit on the number of sessions, the connection pooling mechanism determines whether a client has been idle too long and reuses the connection automatically

1 You don't need to commit after every statement. You will commit every statement if you have a stateless connection and automatically disconnect after every statement. This is not a requirement for connection pooling. In fact stateless connections are as far as I am concerned one of the reasons to start using MTS to limit wasting resources.

2 In MTS you want to use short transactions because the number of shared servers is limited, NOT the number of connections. Long transactions will block a shared server and hence cause contention As connection pooling on the Oracle side is implemented on top of MTS this applies to connection pooling as well.

Hth

--
Sybrand Bakker, Senior Oracle DBA