Re: oracle connection pool question

From: Richard <>
Date: Tue, 13 May 2008 09:27:55 -0700 (PDT)
Message-ID: <>

On May 13, 10:58 am, Mark D Powell <> wrote:
> On May 13, 10:52 am, Richard <> 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 --

hi mark,

thank you very much for the help and information. I think I grasp this better now.

if I understand how you are explaining it to me, it works like this:

  • if I were to examine the three wired connections between the application and database, I'd see lots of calls to dbms_application_info that inform the database of whom the subsequent statements are on behalf of. You might see a call to dbms_application_info followed by a mission SQL statement(s) followed by another call to dbms_application_info followed by another mission SQL statement(s), etc, etc. each set of statements has an owner explicitly enumerated thru the call to dbms_application_info.
  • so as I understand it, dbms_application_info is called very frequently?

do I have this right?

any recommendations for further reading on my part?

thank you very much for the help; it is apreciated as I learn.

rpf Received on Tue May 13 2008 - 11:27:55 CDT

Original text of this message