-
Hi Guys,
We are converting our present application(perl,html,java script) into
Oracle 9i AS environment.
At present in our web application, Every time user is connecting to the database and disconnecting, In the new
application we want to use Connection Pooling. I went through different documents on Connection pooling and i am
little bit confused to under stand the whole consept. (Because different versions are giving different examples).
First we will create the connection pool with
Min connections (2),
Max connection(5)
Then the other program(servlet or JSP) will send a request to connection pool
with username, password and SID details.(exm:SCOTT/TIGER/ORCL)
If this is the first connection then connection pool will open a physical connection
and will give a logical connection.
He is not closing the logical connection after the transaction.
Next
---------
Then the other program(servlet or JSP) will send a request to connection pool
with username, password .(exm:user1/userpass1)
What connection pool will do?
Whether it will open a new physical connection or
it will check the first connection is active or not, And if it's active then
it will create a new physical connection and it will give a logical connection
to user1?
If the first connection is not active then it will use the first physical connection and
it will give the second logical connection?
In this case if first logical connection want's to some transaction, Then what?
It will repeat the step agian(Like checking the physical connection)?
I am trying to understand what result i will get v$process or v$session.
My scenario is
-----------------------------
I want to create a connection pool(Or use connection pool).
I want to open some(2) connections through connection pool(I want to open it but i won't close)
by writing servlet.(scott/tiger)
Then actual application will send a request to connection pool, In this case all ready two physical and
2 logical connections are opened. But that logical connections are ideal(inactive) so i want use the first physical connection.Then application will send another request and i want to use the second physical connection. Then
application will send 3rd request, At this time i want to check the connections are active or inactive. If it's active then
open a new physical connection.
Once i reached the max connections then i wan't wait.
----------------------
I am also looking the proxy connections with JDBC 9.0.1 OCI8. But because of to-many examples i am not able to understand where to start.
So folks tell me what steps i have to follow. If you are unable to give me the scripts(all ready if you scripts then please mail me to sree_sri@hotmail.com), Then please tell me the steps.(Including test steps). If possible explain me how these steps will effect in the database.
Thanks.
-
Hi
The java servlet engine will have its own connection pooling manager, whenever a request comes it
allocates a free connection to that request. Once the request is complete, it returns the connection
to the connection pooling. if there is any ohter request waiting for connection it will be given the
same connection. In the meantime if there are more requests which needs a connection the connection
pooling manager allocates new connection, this goes on until it reaches the maximum number of
connections defined in the connection pooling manager. Once it reaches the limit, it starts queuing
the requests until a connection is freed by one of the previous requests. I hope this helps you.
-
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
-
Hi Sree,
Here is the link, which explains how the connection pooling works.
Hope this shoud help you.
http://developer.java.sun.com/develo.../J2EE/pooling/
-
Guru's Help me.
Hi Guru's,
Please give a look to this and give me some suggestion.
At present 100 user connect to the database through our web application. They connect 100 times every day. Our web application every time connect to the database and after retrieving the data it will disconnect. So in my case
100 users*100 times=10000 times application is connecting to
the database.
Now we are planing to convert our application in to Oracle 9iAS(JSP,SERVLET,BC4J,XML). We want to reduse the database connection hits. That's why we are planing to use
connection pooling.
For that i went through different docuementation and discussed with some people, But some where i am confusing.
1.I created a connection pooling with initial 5 connections and max connections 30.
2.We are using one default user(SCOTT) to open initial connections(5) and we are not closing that 5 connections.
3.Then Web application will send 5 requests to the connection pool. And pool will see how many connections are open and not use? In this case all 5 connections are ideal, So it will disconnect the default connection and make physical connection and logical connection to user1,user2,user3,user4,user5.
4.Then when user6 requested a connection pool then pool will see any connections are open and inactive? If it is then it will disconnect that connection and will make a physical and logical connection to user6.
This process will continue upto all 30 connections open and active. In that time if a user31 requested a connection and
the max 30 connections open and acitive it will wait for ideal connection. After one connection becoming inactive then pool will disconnect that inactive user connection and making physical connection and logical connection to user31.
So every time it's making physical connection to the database.
100 users*100 times=10000 connection hits
In my present application, Code is doing the same by using connect,disconnect.
So when i am speaking with java people they are telling me that the connection speed between in this two methods are different. Is it true?
I have noticed one more big difference in this two methods is
in my present application some time i will see more then 30 connections, But if i use connection pool never application won't request more then 30 connections.
When i am going with the documentation i have noticed that
in JDBC OCI 9.0.1 version will allow you to use
alter user user1 connect through scottt by using role1;
i am not sure about syntax
It will allow to pass user1 name as parameter and change the SCOTT connection .
The physical connection is not changing, I mean if you look in the v$session in will show scott but in the proxy_users table
it will show user1.
The next time if you send user2 if will replace the user1 to user2. But the physical connection is not going to change?
So what i am looking now is what is best way?
Is it connection pooling
or
The present code(connect and disconnect)
or
using the proxy connection pool.
I need guru's suggestions on this. I want to know i am understanding the connection pool stuff correctly. Thanks.
-
One other suggestion is that if your application is implementing connection pooling, and your database uses the MTS, you do not want to do a connection pooling on MTS. But I'm not sure whether MTS does this connection pooling when you do not specify the connection pooling option.
Sam
-
Hi Sambavan,
We are not using MTS, We are in dedicated server mode.
Please give me some more detail suggestion on my scenario.
Thanks.
