JDBC connection pooling help please
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: JDBC connection pooling help please

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    203
    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.
    sree

  2. #2
    Join Date
    Aug 2001
    Posts
    18
    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.

    Prasad

  3. #3
    Join Date
    Feb 2001
    Posts
    203
    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


    sree

  4. #4
    Join Date
    Aug 2001
    Posts
    18
    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/

    Prasad

  5. #5
    Join Date
    Feb 2001
    Posts
    203

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

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Feb 2001
    Posts
    203
    Hi Sambavan,

    We are not using MTS, We are in dedicated server mode.
    Please give me some more detail suggestion on my scenario.
    Thanks.
    sree

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width