oracle connection pool question

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 Received on Tue May 13 2008 - 09:52:41 CDT