oracle connection pool question

[Richard]

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

[Mark D Powell]

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 --

[Richard]

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

[Richard]

On May 13, 10:58 am, Mark D Powell <Mark.Pow...@eds.com> wrote:

hi mark,

[Mark D Powell]

> 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.