commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dennis Lundberg <denn...@apache.org>
Subject Re: DBCP woes (running out of cursors).
Date Wed, 14 Oct 2009 19:43:24 GMT
Bill Davidson wrote:
> Redhat 5.2 Server
> Java: Sun JDK 1.6.0_16 (64-bit)
> Tomcat 6.0.20 (and whichever version of DBCP that includes)
> Oracle 10g  (10.2.0.3)
> JDBC: ojdbc14.jar
> 
> I've been trying to convert an old J2EE application to use DBCP connection
> pools from an old custom connection pool class (not a DataSource
> interface).
> 
> The old pool worked acceptably, and DBCP seemed to work fine in
> development, but then I got it into a test environment and had a bunch
> of people beating on it and I started running out of cursors.  Looking
> through the old code, it turns out that the old pool manager, when
> freeing a Connection, had kept track of when it created that Connection
> and if it was more than 120 seconds old, it closed it and made a new one.
> As a result, a given Connection wouldn't hit the cursor limit because it
> wouldn't get reused for more than 2 minutes.
> 
> So, I think: "We must have code that's not closing ResultSet/Statement
> objects" so I go off looking for them.  With a little research, I come up
> with queries like this:
> 
> SELECT a.sid,
>       b.status,
>       b.osuser,
>       b.machine,
>       to_char(b.logon_time, 'dd-mon-yyyy hh24:mi:ss') logon_time,
>       a.user_name,
>       a.sql_id,
>       a.sql_text
>  FROM v$session b,
>       v$open_cursor a
> WHERE a.sid = b.sid
>   AND a.user_name = 'APPUSERNAME'
> ORDER BY upper(a.sql_text),
>         b.status;
> 
> SELECT count(a.sql_text) count,
>       a.sql_text
>  FROM v$session b,
>       v$open_cursor a
> WHERE a.sid = b.sid
>   AND a.user_name = 'APPUSERNAME'
>   AND b.status = 'INACTIVE'
> GROUP BY a.sql_text
> ORDER BY count desc,
>         upper(a.sql_text);
> 
> I run these against production (which is still using the old pools) and
> I find all sorts of queries lying around on inactive sessions, which
> I'm guessing are Connections that were closed.  I then start tracking
> the SQL down and every single one has an iron clad close() call in
> a "finally" clause.  There is absolutely no way the close() is not being
> called on the Statement objects.  Many of them even explicitly close()
> the ResultSet objects even though that should be handled by closing
> the Statement objects.  These close() calls are happening immediately
> after the data is gathered from the ResultSet's.
> 
> I then went looking in active sessions in the test environment and it's
> the same thing.  I can't find any lingering cursors for SQL that doesn't
> have a Statement.close() call that's guaranteed to run.  I've got lots
> of open cursors.  I can't explain why.
> 
> The config I'm using for DBCP (other than connection info):
>              maxActive="245"
>              maxIdle="16"
>              validationQuery="SELECT 1 FROM dual"
>              poolPreparedStatements="true"
>              accessToUnderlyingConnectionAllowed="true"
> 
> Since I've eliminated the possibility that close() is not being called
> on Statement/ResultSet's, I'm wondering a number of things:
> 
> 1.Could this be a bug in the Oracle JDBC driver?

Yes! We're not using DBCP, but are using Oracle's own JDBC-driver
against an old Oracle 8 instance. You cannot rely on Oracle's driver to
close everything for you. You need to explicitly close all your
ResultSets, Statements and Connections. :-(

> 2. Should I change parameters for DBCP?
> 
> - Should I get rid of poolPreparedStatements?  Note: That noticeably
> hurts performance.
> - Should I use timeBetweenEvictionRunsMillis - What's an "eviction" run?
> - Should I reduce maxIdle?
> 
> 3.  Is there a newer version of DBCP than the one that comes with
>    Tomcat that might mitigate this problem and is it a good idea to
>    drop that into Tomcat?
> 
> Any useful advice would be appreciated.
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
> 
> 


-- 
Dennis Lundberg

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


Mime
View raw message