Re: oracle connection pool question

On May 13, 10:52 am, Richard <richard_p_frank..._at_yahoo.com> wrote:
> hello,
>
> sorry if this is a simple question. i'm trying to learn some oracle
> fundamentals.
>
> in an oracle connection pooled environment, one or more physical
> connections are shared by multiple users.
>
> as context, my understanding is that:
>
> - the application creates connection(s) to the database, intending to
> reuse these as much as possible without tearing them down
> - each connection in the pool must have been created using the same
> authentication credentials
> - sessions (say SAP_USER, as a fictitious example)
> - users of the connections _may_ signal their true user identity
> using, for example, oracle's SET_CLIENT_INFO stored procedure
> (fictitious example: SET_CLIENT_INFO 'richard')
>
> my question is this:
> do application sessions jump around on multiple physical connections
> or do they persist on a physical connection once using that connect?
> if there are three connections to the database, once a session starts
> on physical connection 1, will it ever move to physical connection 2?
> If it does so, is SET_CLIENT_INFO called again?
>
> it's easy to imagine that if you were watching the connections from a
> network (sniffer) perspective, you'd see a series of commands on
> behalf of a user going thru one session. My question is whether you'd
> see commands on behalf of that same user going across multiple
> database connections in a way that might be described as parallel.
>
> i'm just trying to learn, comments appreciated.
>
> thx,
> rpf

Generally applications sessions jump from one Oracle connection to another with each submitted set of SQL statements depending on the front-end connection pooling in use. That way the connection pool management logic can assign a statement to any available connection for fastest scheduling. I the application transactions should be set up so that a set of statement are submitted so that the call to dbms_application_info, then an insert of a header row followed by the insert of child rows all take place as one transaction using one pooled connection as a single Oracle transaction. This generally means taking control of the transaction in the code.

HTH -- Mark D Powell -- Received on Tue May 13 2008 - 10:58:03 CDT