Spring JDBC 2.5.6 + SimpleJdbcCall + Remote Oracle DB

[Tojo]

Hello,

We are having a problem with using Spring JDBC 2.5.6 to access an Oracle database when the database is physically placed in another part of the country. We have tested using a database in the same sub-net as the java web app running the queries and that works fine.

Our setup is:
Spring JDBC 2.5.6
Data source: org.apache.commons.dbcp.BasicDataSource
Oracle DB: 10.2.0.2.0
Method: SimpleJdbcCall using JdbcTemplate to execute stored procedures and stored functions in the database.

Problem:
When the database is located in the same sub-net as the web app everything is ok.

When the database is located far away in "the cloud" the database sometimes hangs when we execute a query. The hang can last for several minutes and sometimes even hours! the last entry in the spring debug log when the hang occurs is: Fetching JDBC Connection from DataSource

There does not seem to be a specific pattern of when the database hangs. Sometimes it happens 10 minutes after deploy, sometimes after 2 hours.


Does anyone have any idea how we can solve or work around the problem?


From application-context.xml:
<bean id="oracleDataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
<property name="url" value="${jdbc.oracleUrl}" />
<property name="username" value="${jdbc.oracleUserName}" />
<property name="password" value="${jdbc.oraclePassword}" />
<property name="validationQuery" value="SELECT 1 FROM DUAL" />
</bean>

Best regards
Tobias

[Tojo]

I have now changed the configuration of the BasicDataSource to:

<bean id="oracleDataSource" destroy-method="close"
    class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
    <property name="url" value="${jdbc.oracleUrl}" />
    <property name="username" value="${jdbc.oracleUserName}" />
    <property name="password" value="${jdbc.oraclePassword}" />
    <property name="validationQuery" value="SELECT 1 FROM DUAL" />
    <property name="defaultAutoCommit" value="true"/>
    <property name="maxActive" value="20"/>
    <property name="maxIdle" value="20"/>
    <property name="maxWait" value="3000"/>
    <property name="testOnBorrow" value="true"/>
    <property name="testWhileIdle" value="true"/>
    <property name="timeBetweenEvictionRunsMillis" value="300000"/>
</bean>

It seams to work better now. However I'm not sure I've solved the problem or if I'm just "hiding it". Maybe it is still there but harder to reproduce?

Does anyone have some thoughts on this?

/Tobias