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?
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: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
