commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject Re: [dbcp] Re: Oracle session excess over maxActive
Date Tue, 09 Mar 2010 14:35:29 GMT

make sure you close also PreparedStatements, Statements and ResultSet objects.
Also, you might want to use testOnBorrow instead of testOnReturn
==>         <property name="testOnReturn" value="true"/>
        <property name="testOnBorrow" value="true"/>

You can monitor number of opened connections through sudo netstat -apn |grep <java_pid>


----- Mail Original -----
De: "Phil Steitz" <>
À: "Commons Users List" <>
Envoyé: Mardi 9 Mars 2010 15h06:56 GMT +01:00 Amsterdam / Berlin / Berne / Rome / Stockholm
/ Vienne
Objet: [dbcp] Re: Oracle session excess over maxActive

sic wrote:
> I'm using commons-pool-1.3, commons-dbcp-1.2.2
> Our business is quite simple. Using quartz, 30 simpleTriggers(5sec
> repeatInterval) are triggered by 10 thread on quartz threadPool.
> When any simpleTrigger is fired, it executes a job of doing DB(oracle)
> query(select several rows and then update for them).
> Configuration is as below :
> <bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
> 	<property name="quartzProperties">
> 		<props>
> 			<prop key="org.quartz.threadPool.threadCount">10</prop>
> 		</props>
> 	</property>
> 	<property name="triggers">
> 		<list>
> 			... 30 simple triggers ...		
> 		</list>
> 	</property>
> </bean>
> <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
> destroy-method="close">
>         <property name="driverClassName"
> value="oracle.jdbc.driver.OracleDriver" />
>         <property name="url" value="..." />
>         <property name="username" value="..." />
>         <property name="password" value="..." />
>         <property name="defaultAutoCommit" value="false"/>
>         <property name="initialSize" value="10"/>
>         <property name="maxActive" value="15"/>
>         <property name="maxIdle" value="10"/>
>         <property name="minIdle" value="10"/>
>         <property name="maxWait" value="60000"/>
>         <property name="testWhileIdle" value="false"/>
>         <property name="validationQuery" value="select 1 from dual"/>
>         <property name="testOnReturn" value="true"/>
>         <property name="timeBetweenEvictionRunsMillis" value="10000"/>
>         <property name="removeAbandoned" value="true"/>
>         <property name="removeAbandonedTimeout" value="10"/>
>         <property name="logAbandoned" value="true"/>
> </bean>
> by this configuration, 10 threads are executed by using 10~15 connections
> after firing at 30 triggers.
> On rare occasion, however, DB sessions(confusing connection and session) are
> not closed normally and the result is that they occupy a tremendous amount
> of sessions.
> As a result of inquiring of DB "select * from v$session", it presents the
> maximum number of session(more than 1000) allowed to this user and remains
> INACTIVE status. And this status cannot be resolved until we shutdown this
> process.
> What causes this abnormal states? We certainly expected that DB pool can
> limit the number of Active session by the "maxActive" property no matter
> what may happen, but it seems not to work occasionally.
> Any advice about this matter..

When posting to this list, please prefix the subject line with the
name of the component, in this case [dbcp].  The list is shared by
all commons components and it makes it easier for us to find and
respond to posts if they do this.  Thanks!

Now to your question.

1) Your configuration will likely lead to a lot of connection churn.
 Setting maxIdle = minIdle = 10 when maxActive = 15 will force the
pool to be constantly destroying excess connections and then adding
new ones.  You would likely be better off dropping both of these or
at least setting maxIdle to a negative value (no limit).  Your
removeAbandonedTimeout is also set to a very low value.  If your
code is closing all connections (something you should check in any
case), you should not need to use abandoned connection cleanup and
in any case running the evictor every 10 seconds and timing out idle
connections every 10 seconds is extreme.  Your code should close its
own connections and not require such aggressive cleanup.  In pool
1.3, evictor runs lock the pool, so eliminating this if you can
clean up the client code will improve throughput.

2) The latest versions of dbcp and pool are dbcp 1.3 (JDK 1.4-1.5)
or dbcp 1.4 (JDK 1.6+) and pool 1.5.4.  Upgrading to the latest
versions may resolve your problem.  You should address 1) in any case.


> regards,
> sic

To unsubscribe, e-mail:
For additional commands, e-mail:

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message