commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Gainty <>
Subject RE: DBCP woes (running out of cursors).
Date Thu, 15 Oct 2009 02:09:22 GMT

 StatementHandle(this is where the READONLY/UPDATABLE/FORWARD/REVERSE cursor is defined)
  close ResultSet

closing ResultSet closes the ResultSet only but has no effect  on Statement Handle
closing StatementHandle closes ResultSet and StatementHandle
closing Connection closes all \

are you running as a Transaction?
 Select into Buffer
 UPDATE/DELETE/Insert with dynamic variables
 COMMIT will flush to disk and close all handles
 Rollback will restore Transaction state to before Begin_Transaction state

if you display the queries here and we can help you show how to properly structure the statements
you're using 
Martin Gainty 
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni
et de confidentialité
 Ez az
üzenet bizalmas.  Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett.  Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs.  Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten
wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist
unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet
keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen
wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire
prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe
quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information
seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les
email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune
responsabilité pour le contenu fourni.

> Date: Wed, 14 Oct 2009 21:22:35 -0400
> From:
> To:
> CC:
> Subject: Re: DBCP woes (running out of cursors).
> Hash: SHA1
> Bill,
> On 10/14/2009 5:05 PM, Bill Davidson wrote:
> > Usually, we don't need that many [connections], but sometimes, we get hit really
> > with a lot of traffic and do need that many.  BTW, this is load balanced
> > across 4 servers that can each do 245 connections.
> Is it possible that your server just doesn't want to allocate 245 * 4
> cursors, and that you are just hitting that barrier? I don't believe the
> JDBC driver cares at all how many cursors are allocated, so it's
> unlikely to be a client-side exception being thrown (or, if you prefer,
> it's a server-side error being represented by a client-side exception).
> > I thought [logAbandoned and removeAbandoned] was for Connection leaks.
> They are. I just thought it would be a good idea to enable these, just
> in case there was a case where leaks were occurring.
> > If we had Connection leaks, then the old pools wouldn't work properly
> > either, because the old pools only kill connections when the servlets
> > "free" the Connection (the same as close() on a DBCP connection).
> > The Connection's are being sent back to the pool, but apparently with
> > open cursors lingering.
> I though you said that after a connection was checked-out for 120
> seconds, it was forcibly closed by the connection pool.
> >>I don't know a thing about Oracle-specific queries, but what does:
> >>
> >>>      v$open_cursor a
> >>
> >>mean? Does this explicitly open a new cursor, or use an existing one
> >>called a?
> > 
> > v$cursor is a view in the Oracle data dictionary that shows currently
> > open cursors in the current Oracle instance.  The sql_text column shows
> > the first 40-50 characters or so of SQL being executed for that cursor.
> > It shows them for both active and inactive sessions.  I'm only guessing
> > that the inactive sessions are from Connection's that are closed without
> > having all of their ResultSet's closed.  That might be incorrect.
> > Finding concrete information is difficult.
> 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.
> > Maybe, but as I said, I've tracked down the SQL for all of the open
> > cursors that don't seem to go away and they all have guaranteed close
> > calls on the Statement's, and many also have them on the ResultSet's.
> > A lot of the SQL is not that funky either.  A lot of it is as simple
> > as grabbing a single record "SELECT * FROM some_table WHERE id = ?"
> > or a few records like "SELECT * FROM some_table WHERE some_col = ?".
> 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:
> PreparedStatement ps = null;
> PreparedStatement ps2 = null;
> try {
>   ps  = conn.prepareStatement(...);
>   ps2 = conn.prepareStatement(...);
>   ...
> }
> finally
> {
>   ps.close();
> }
> >        }finally{
> >            if ( rs != null ){
> >                try{
> >                    rs.close();
> >                }catch ( SQLException ex ){
> >                    // log it.
> >                }
> >            }
> 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?
> >>>- Should I use timeBetweenEvictionRunsMillis - What's an "eviction" run?
> >>
> >>It's what happens every so often to flush-out all the connections that
> >>have been (for instance) idle too long, etc.
> > 
> > That might help.  The stuff I'm finding more recently is implying
> > to me that connections are never closed if I don't enable eviction
> > runs.
> 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 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.
> 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.
> Good luck,
> - -chris
> Version: GnuPG v1.4.9 (MingW32)
> Comment: Using GnuPG with Mozilla -
> 94sAoKjGV9j3GA01nbZZaBGIdFaC6nlA
> =9VFy
> ---------------------------------------------------------------------
> To unsubscribe, e-mail:
> For additional commands, e-mail:
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message