Re: oracle connection pool question

On May 13, 10:58 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

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.

thx,
rpf Received on Tue May 13 2008 - 12:15:25 CDT