Re: oracle connection pool question

On May 13, 1:15 pm, Richard <richard_p_frank..._at_yahoo.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

How often dbms_application_info is called will depend on who wrote the application and/or the framework the application was developed using.

But yes, the application would announce a transaction, execute dbms_applicaton_info to pass in the real user, pass in one or more SQL statements, and end the transaction. Repeat.

A few years ago I encounted a web type application that used connection pooling where sceens developed in the application were assigned to a specific connection. If the screen query ran 2 seconds every time this is workable but when it is 2 seconds one time but 2 minutes another based on input data then this method does not work well at all when it only supported 10 connections to the database and there were 40 users.

HTH -- Mark D Powell -- Received on Thu May 15 2008 - 11:41:40 CDT