commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bill Davidson <bill...@gmail.com>
Subject Re: DBCP woes (running out of cursors).
Date Thu, 15 Oct 2009 18:15:10 GMT
Christopher Schultz wrote:
 >Is it possible that your server just doesn't want to allocate 245 * 4
 >cursors, and that you are just hitting that barrier?

cursor != connection

Oracle is set up to allow up to 300 cursors per session (connection).
I could up that limit, but it probably won't fix the problem, as these
things seem to just keep accumulating.

 >I though you said that after a connection was checked-out for 120
 >seconds, it was forcibly closed by the connection pool.

Only when it is sent back to the pool by the servlet.  The pool manager
doesn't have a background thread looking for old connections to kill.
It's not a work around for connection leaks.  Apparently it's a work
around for cursor leaks.

 >Oh, so this query is intended to find out what is happening on the
 >server side, so you can see what cursors are open and what their queries
 >are. I thought you meant that a query such as this was being executed
 >from your webapp.

Correct.  Sorry if I didn't make that clear.  Those queries are not in
my webapp.  They are only used to help track down lingering cursors.
For people not familiar with Oracle, special characters like '$' in FROM
clauses are usually an indication of something being in Oracle's "data
dictionary" which keeps track of everything in Oracle.  The other one
in that query was "v$session" which keeps track of session information.
A session, in this context, is a connection.

 >Do some of those methods have multiple queries being executed? If so,
 >it's possible that one statement remains open while the second one is
 >closed. For example:

A very small number do, but the vast overwhelming majority do not.
I'm finding these lingering cursors on all sorts of things.  On the ones
that do, they are all still closed.

 >This is perfect. I noticed that I don't see a conn.close in there (which
 >is probably appropriate, given that the Connection object is a parameter
 >to the method). I assume you have similar finally blocks in calling
 >methods, right?

Correct.  I'm not seeing anything resembling a connection leak problem.
This is strictly a cursor problem.

 >The eviction run will only to remove connections from the pool: it won't
 >fix any resource allocation problems. Your webapp and server ought to be
 >able to tolerate all connections being open and active at once (so, a
 >full 245 connections in each webapp instance, and 980 connections on the
 >server).

The Oracle instance is set up to handle 1000 connections.  Tomcat has
maxThreads=240, just because I'm paranoid and want to leave a little slop
factor.  I shouldn't ever see more than 240 actual connections per server.

 >The only thing the eviction will really help with is reducing the memory
 >being used on both the client and server. I suppose that calling a
 >"true" close() on the connection might clean-up any sloppiness going on
 >in the client OR the server, and thus might solve your problem, but I
 >believe it will be merely hiding the symptom, not actually solving the
 >underlying problem.

I suspect that you are correct.  I'm baffled as to why I have old cursors
lying around.  The close call on the statements has to happen.

 >Do you have access to an Oracle DBA? They may be able to help uncover
 >the implications of some of the queries being run... it's possible that
 >cursors are being allocated that you didn't expect, or that aren't being
 >closed for /other/ reasons.

The Oracle DBA that I have access to doesn't know much about Java/JDBC
which is why I was hoping I could find some Oracle expertise in the
commons or tomcat lists.



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


Mime
View raw message