I have the following JNDI connection to a Oracle database from a Tomcat 5.5 server.



Context



accessToUnderlyingConnectionAllowed="true"

auth="Container"

driverClassName="oracle.jdbc.driver.OracleDriver"

maxActive="100"

maxIdle="15"

maxWait="10000"

name="jdbc/OracleDB"

type="javax.sql.DataSource"

url="jdbc:oracle:thin:@127.0.0.1:1521:MINE"

username="username"

password="password"

validationQuery="select sysdate from dual

"/>



Web.xml



Oracle database

connection

jdbc/OracleDB

javax.sql.DataSource

Container







The problem is that sometimes the connectionpool "runs out" and it's not near the 100 connection.



The way I do it is that each function gets a connection from the pool



public boolean func1()

{

try

{

Connection c = getConnection();

//sql call(s)

}

catch(SQLException e)

{

}

finally

{

//close rs, preparedstatement and connection

}



and thease function may be nested. So funct 1 calls funcc 2 that calls funct 3 etc. So I guess a single user may get multiple connection to the database for a short time.



But the website can have around 5 visitors that makes calls, and the getConnection function can take long time to return a connection. I guess that this means that all connection in the pool is gone (all 100?) and it waits up to 10 sec for a new connection.



Should not 100 connection be enough? Does any have any tip so I can work this problem out?







Another problem I had is that when the user first comes to the site it takes a long time to load it. This is also becouse of the connection pool. My guess was that there is no empty connection in the pool and a new (many new?) was created. Is there a way to specify a minimum number of connection to hold in the pool?





When I look in oracle and sessions, there is only 1 or 2 connection/session but with many cursors. I assume this is the way it should be. When is a new connection/session set up? Is the any maximum cursors on a single connection/session?



Greatful for any suggestions...