Hi Prasad,

Thanks for information.

You mentioned that java servlet engine will have it's own connection pooling manager.
Is it available under Oracle 9iAS?
I am planning to use JSP application,Servlet engine connection
pooling manager will work for me.

I saw that i can use a pool in JDBC 2.0, But i don't know
the connection manager.

You told that whenever a request comes, It allocates a free
connection to that request?

That mean all ready the connection manager opened a physical connection by default user(SCOTT) and it allocates
that connection to the user1?

In that case again user1 establish a physical connection and logical connection?

Before the request v$session is showing that SCOTT connected to the database, Now After the request(After allocate this connection to user1) what result i will see in v$session.

If it shows USER1 then, It's making physical connection to the
database.

In the mean time If user2 requested a connection then it will give the same connection to the user2(If the user1 connection
is ideal)?

If the user1 connection busy then it will make a new connection and it will allocate that connection to user2.

In that case i will see 2 sessions(user1,user2).

In the mean time if SCOTT request a connection then it will see any above 2 connecions are inactive(or ideal) then it will give that connection to SCOTT.

That mean it will make physical connection to SCOTT.

So every time it's making the physical connection.

Once it's reached max connections it will wait for ideal connection and when it got ideal connection it will allocate that
connection to the requested user.

So one day if 100 users requested a connection 10 times then
1000 times connection pool or manager will make a physical
connection to the database.


Right now in my old application every time user connecting to the database, after retrieving the data he is disconnecting.

In the above scenario(Using Pool) 1000 times users are making physical connection to the database. In my old application also users making 1000 times physical connection.

So both are same?

My plan
------------
1.make a connection pool(initial 5 and max 30)

2.open 5 intial connection(physical connection)

3. Then my application will send request. If it's send 6 requests, The first 5 requests need a logical connection with database.
The 6 request can make a physical and logical connection. The 31st request has to wait for free connection.
Once it's got free connection then it need a logical connection.
The same thing for 32nd connection.

In the mean time 26 users request completed then pool automatically close the 25 connections(Keep open the 5 initial connections) and give a logical connection from the initial connections. For the 33rd request it will see for ideal connection in initial 5 connections and it's not available then it can make a physical and logical connection. After that another 2 users request completed then it can close 1 conneciton and keep open 5 initial connections.

So in my case every time i am opening 5 connections and when request came i am giving logical connection from that 5 connections and if that 5 connections are busy then i am making 6 physical and logican connection.
-------------------
Is it possible?
If possible then what steps i have to follow to make above environment?
Which JDBC version and which OCI version will help me.

The big question is Did i understand this connection pool consept? If not some body please help me by giving the
examples. It's not nesseary to give the code, I want to know

what happens when application request a connection from connection pool?

what happens in DATABASE when connection pool make a physical connection?

what happens in DATABASE when connection pool allocate a logical connection?

Any suggestion will help me. Thanks