DBCP woes (running out of cursors).

[Bill Davidson-6]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bill,

On 10/14/2009 2:17 PM, Bill Davidson wrote:
> Redhat 5.2 Server

Wow.

> 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).

I've moved a couple of things around, here. Sorry if it's disorienting.

>              maxActive="245"
>              maxIdle="16"
>              validationQuery="SELECT 1 FROM dual"
>              poolPreparedStatements="true"
>              accessToUnderlyingConnectionAllowed="true"

This config looks fine, though the maxIdle might be a little low. IIRC,
setting-up Oracle connections is a time-consuming operation. If you
really need 245 connections, perhaps you might want to increase the idle
count to improve the performance of your webapp when demand suddenly
hits a mostly-idle server.

> 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.

Similar capability is found in DBCP in the form of the "removeAbandoned"
and "logAbandoned" configuration parameters. See the configuration for
DBCP on this page:
http://commons.apache.org/dbcp/configuration.html

/Important note:/ the "removeAbandoned" configuration parameter will
merely remove the discarded connection from the pool. It will /not/
close the connection after that timeout, so it won't solve your problem:
it will only help you find the problems in your webapp.

> 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.

I wrote this a while back, which may be worth reading:
http://blog.christopherschultz.net/?p=68

> 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 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?

> 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.

Technically speaking, the JDBC specification requires that calling
Connection.close() also close any Statement (and therefore ResultSet)
objects that were opened as well. The lines become blurred a bit when
you're talking about pooled connections, because Connection.close()
doesn't really get called... it's a grey area in the spec if you ask me,
but I'd prefer that a pooled connection act like a non-pooled connection
in this case, but there's no "recycle" or "reset" method in the
java.sql.Connection class, and calling Connection.close() on the actual
connection is not appropriate (since it's pooled) so there may be no way
to actually implement this mimicry.

> These close() calls are happening immediately
> after the data is gathered from the ResultSet's.

Can you post an example of your finally blocks? If each xyz.close()
isn't in a try/catch block of its own, it could potentially fail to
return the Connection to the pool. In this case, logAbandoned will help
you a lot. I recommend enabling that setting even in production,
especially because that's where it's easiest to exercise your webapp's
DB access code.

> 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.

Hmm.

> 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?

Possible, but I think lots of people are using this driver without a
problem.

> 2. Should I change parameters for DBCP?

Other than enabling the 'abandoned' stuff just to convince yourself that
your code /is/ properly cleaning-up connections/statements/etc., I don't
think that mucking-around with DBCP's configuration will be useful...
there's nothing else DBCP can do except maybe:

>              poolPreparedStatements="true"

...try setting that to "false" and see if the problem clears-up.
Different drivers pool statements in different ways, and it's possible
that Oracle's driver either poorly implements the statement pooling, or
that it's being emulated by DBCP in a way that doesn't quite work with
Oracle. It wouldn't be a bad idea to ask on the Apache commons list if
anyone has had problems like this when using DBCP.

> - Should I get rid of poolPreparedStatements?  Note: That noticeably
> hurts performance.

:(

On the other hand, a working webapp is much better than a fast one that
is unreliable.

> - 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.

> - Should I reduce maxIdle?

I wouldn't do that. You might see your performance suffer even more if
connections need to be created all the time.

> 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?

Tomcat re-packages DBCP into a different package-space to avoid
confusion with the "real" DBCP if it's being used by a webapp or
whatever, so you have to do some back-flips if you want to "upgrade"
DBCP to a newer version.

Tomcat 6.0.11 updated to use commons-dbcp-1.2.2 (the current version)
and commons-pool-1.3 (not clear what the current version is to me, but
1.3 seems reasonable), so you should have basically the state-of-the-art
stable releases of those packages.

There is another option that you could try, though I suspect the problem
might be with your queries themselves: Filip announced a new beta
connection pool in this thread:
http://markmail.org/message/4anhrtu5om6nqcuu?q=new+connection+pool+list:org%2Eapache%2Etomcat%2Euser/

The link be posted is broken and I can't find an updated version, but it
might be lurking somewhere else. You might want to re-post with a
question about that.

Hope all that helps,
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrWH1kACgkQ9CaO5/Lv0PCpJQCgoE8Y4Oytl11eWa7yOdb9MSMq
fmwAn2AemyFzHcyF6LvlUM7qJMyKWeXC
=uRTH
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Dennis Lundberg-3]

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: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

--
Dennis Lundberg

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Bill Davidson-6]

Christopher Schultz wrote:
 >On 10/14/2009 2:17 PM, Bill Davidson wrote:
 >>Redhat 5.2 Server
 >Wow.

Maybe I should have said RHEL 5.2.  5.3 would be the current
version, so it's actually not that old.  RedHat's starting over with
the numbers does get confusing.

 >This config looks fine, though the maxIdle might be a little low. IIRC,
 >setting-up Oracle connections is a time-consuming operation. If you
 >really need 245 connections, perhaps you might want to increase the idle
 >count to improve the performance of your webapp when demand suddenly
 >hits a mostly-idle server.

Usually, we don't need that many, but sometimes, we get hit really hard
with a lot of traffic and do need that many.  BTW, this is load balanced
across 4 servers that can each do 245 connections.

 >Similar capability is found in DBCP in the form of the "removeAbandoned"
 >and "logAbandoned" configuration parameters. See the configuration for
 >DBCP on this page:
 >http://commons.apache.org/dbcp/configuration.html
 >
 >/Important note:/ the "removeAbandoned" configuration parameter will
 >merely remove the discarded connection from the pool. It will /not/
 >close the connection after that timeout, so it won't solve your problem:
 >it will only help you find the problems in your webapp.

I thought that was for Connection leaks.  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 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.

 >Technically speaking, the JDBC specification requires that calling
 >Connection.close() also close any Statement (and therefore ResultSet)
 >objects that were opened as well. The lines become blurred a bit when
 >you're talking about pooled connections, because Connection.close()
 >doesn't really get called... it's a grey area in the spec if you ask me,
 >but I'd prefer that a pooled connection act like a non-pooled connection
 >in this case, but there's no "recycle" or "reset" method in the
 >java.sql.Connection class, and calling Connection.close() on the actual
 >connection is not appropriate (since it's pooled) so there may be no way
 >to actually implement this mimicry.

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 = ?".

 >Can you post an example of your finally blocks? If each xyz.close()
 >isn't in a try/catch block of its own, it could potentially fail to
 >return the Connection to the pool. In this case, logAbandoned will help
 >you a lot. I recommend enabling that setting even in production,
 >especially because that's where it's easiest to exercise your webapp's
 >DB access code.

Basic pattern:

    public Vector someFinder( Connection conn, long param )
    {
        PreparedStatement statement = null;
        ResultSet rs = null;
        Vector result = new Vector();

        try{
            statement = conn.prepareStatement(SomeSQLString);
            statement.setLong(1, param);
            rs = statement.executeQuery();
            while ( rs.next() ){
                // findResult does the ORM in our app.
                result.add(findResult(rs));
            }
        }catch ( Exception e ){
            // logging, plus possible wrap-and-re-throw
        }finally{
            // sometimes the ResultSet is explicitly closed,
            // other times not.  It shouldn't matter.
            if ( rs != null ){
                try{
                    rs.close();
                }catch ( SQLException ex ){
                    // log it.
                }
            }
            // Statement's are always explicitly closed.
            if ( statement != null ){
                try{
                    statement.close();
                }catch ( SQLException ex ){
                    // log it.
                }
            }
        }
        return result;
    }

 >>- 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.



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
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 hard
> 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
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrWeVsACgkQ9CaO5/Lv0PClbgCgnYUGJ/Uzh/UvTDeT8NpdzD/p
94sAoKjGV9j3GA01nbZZaBGIdFaC6nlA
=9VFy
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[mgainty]

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

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?
Begin_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
END

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: [hidden email]
> To: [hidden email]
> CC: [hidden email]
> Subject: Re: DBCP woes (running out of cursors).
>
> -----BEGIN PGP SIGNED MESSAGE-----
> 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 hard
> > 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
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrWeVsACgkQ9CaO5/Lv0PClbgCgnYUGJ/Uzh/UvTDeT8NpdzD/p
> 94sAoKjGV9j3GA01nbZZaBGIdFaC6nlA
> =9VFy
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

     
_________________________________________________________________
Your E-mail and More On-the-Go. Get Windows Live Hotmail Free.
http://clk.atdmt.com/GBL/go/171222985/direct/01/

[Bill Davidson-6]

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: [hidden email]
For additional commands, e-mail: [hidden email]

[Bill Davidson-6]

Martin Gainty wrote:
 >are you running as a Transaction?

In some cases, but a lot of these lingering cursors are on very simple
queries, with no insert/update/delete involved.  As I said before,
I'm finding lingering cursors on things as simple as "SELECT * FROM
some_table WHERE id = ?".

That does make me wonder though if there are Connection's getting sent
back to the pool that had a pending transaction without a commit/rollback
and if that could be making any cursors on that connection after that
linger?  It might explain the randomness of the queries showing up in
the list.  It also gives me a question I can send to my Java-challenged
Oracle DBA.  Thanks for the idea.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bill,

On 10/15/2009 2:15 PM, Bill Davidson wrote:
> 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

Right... but presumably your code is doing something useful with that
connection, rather than simply fetching it from the pool and then
discarding it. IIRC, all SELECT queries allocate a cursor. Others may as
well.

> 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.

Agreed. But, I wonder if there's a max cursors globally or something
like that, so that when you have all those connections going at once,
their sum total cursors exceeds that limit?

When this happens, does Tomcat continue running otherwise unscathed? Or,
do you get an avalanche of exceptions after the first one occurs. What
about on the other members of the cluster (just a descriptive term, not
a technical one)?

>>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.

Hah, okay. Well, it doesn't look like commons-dbcp has a similar
configuration option; you can only evict connections that have been
sitting idle, not those that have simply lived too long. Perhaps the
commons-dbcp folks have some ideas.

>>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.

Gotcha. I wonder if somehow your app-based queries are allocating
cursors and never freeing them. Simple SELECT queries (or even complex
ones) shouldn't really be allocating cursors that aren't freed when you
call ResultSet.close or Statement.close (whichever actually kills them,
I'm not sure). Also, "closing" a pooled connection should call
setAutoCommit(true) (this is the default setting for autocommit) which
will commit any in-progress transactions lingering, which I would
imagine results in all cursors being closed as well. I must admit, I'm
at the limit of my (quite limited) Oracle knowledge at this point. Got a
DBA handy?

>>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.

Agreed.

> 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.

Yeah, I agree. The only thing I can think of is that you are pooling
prepared statements. If you have more than 300 different queries in your
webapp (not at all a stretch IMO), and they are all being pooled, it's
possible that each one of those cached prepared statements consumes a
cursor. In that case, you'll easily consume your cursor allocation per
request. I'm just grasping at straws, now.

What happens, other than an awful slowdown, if you disable statement
pooling?

>>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.

Yeah, see what the commons-dbcp list has to say. Things are ... pretty
slow on the commons-user list, so you may have to wait a bit for a
reply. You could also ask on some Java-oriented Oracle forums.
Definitely post back if you find a satisfactory explanation so everyone
can ready about your new-found sage advice ;)

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrXih0ACgkQ9CaO5/Lv0PCiXACgjMwYfWJIuILB0NfPe0VMzLie
IMAAn0sYF+cpl3JMef9oXUugj2dvLuOd
=xZV9
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bill,

On 10/15/2009 2:24 PM, Bill Davidson wrote:
> That does make me wonder though if there are Connection's getting sent
> back to the pool that had a pending transaction without a commit/rollback
> and if that could be making any cursors on that connection after that
> linger?

Probably not. DBCP calls setAutoCommit(true) by default in order to
reset the connection as it goes back into the pool. Any pending
transaction is committed (!) when that happens, so there shouldn't be
any in-progress transactions lingering around.

If you set autoCommit="false" in your DBCP configuration, that might
change things. Are your transaction finally blocks and commit/rollback
logic blocks sane? You can refer to my previously-mentioned blog post
for my thoughts on how to do it properly.

> It might explain the randomness of the queries showing up in
> the list.

But what is the likelihood of "SELECT * FROM foo WHERE foo.id=?"
appearing in a transaction?

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrXiuYACgkQ9CaO5/Lv0PDX1QCfUhGfjpNnU6mnff8g6WqRb7MN
vxMAoK3xes9KEfE5V8J/ZSGKCY2SqMRp
=6lz1
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Bill Davidson-6]

Christopher Schultz wrote:
 >Probably not. DBCP calls setAutoCommit(true) by default in order to
 >reset the connection as it goes back into the pool. Any pending
 >transaction is committed (!) when that happens, so there shouldn't be
 >any in-progress transactions lingering around.
 >
 >If you set autoCommit="false" in your DBCP configuration, that might
 >change things. Are your transaction finally blocks and commit/rollback
 >logic blocks sane? You can refer to my previously-mentioned blog post
 >for my thoughts on how to do it properly.

I'm a little confused about auto-commit.  I don't want my transactions
auto-committed.  Sometimes there's a need for a rollback of an entire
transaction.  To my thinking, auto-commit implies that you don't have
true transactions.

The transactions in the app are a lot more complex, and a transaction
can hold onto a connection for quite a while as a user is editing things.
The queries themselves have the same try-catch-finally situation but
there can be quite a few different calls on that connection for one
transaction.

 >But what is the likelihood of "SELECT * FROM foo WHERE foo.id=?"
 >appearing in a transaction?

Very low.  The scenario I was describing was:

1. Thread 1 returns a connection with an uncommitted transaction to the
pool.
2. Thread 2 gets the same connection from the pool and does a query on it
3. The query from Thread 2, unrelated Thread 1, ends up in cursor limbo
   because of the uncommitted transaction on the connection.

I don't know that this is happening in the app, and it's going to be very
difficult to find it if it is.  I don't even know that this scenario
could cause this
cursor limbo.  It's just a hypothesis which is hard to test.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Bill Davidson-6]

Christopher Schultz wrote:
 >Uh, oh. Are you doing something like this:

Possibly.  I didn't write that code.

 >If you're doing that, then you're probably making a big mistake. Are you
 >storing connections in sessions or anything like that? Yuk.

For certain transactional operations, I think it is.

 >>1. Thread 1 returns a connection with an uncommitted transaction to
the pool.
 >Mistake: see above.

I know it's a mistake.  I'm not sure that that is what is happening.  It's
just a hypothesis.

 >I'm still interested in what happens if you disable prepared statement
 >pooling.

I finally figured out how to make the app use up the cursors consistently
so I tried disabling PreparedStatement pooling a few minutes ago.  That
makes the problem go away.  I can make cursors accumulate with it turned
on.  I can't with it turned off.

That makes me think it's not the app after all.

However, it also makes me concerned about performance.  Interactive
performance on my test machine is nice and fast.  However, the performance
problems show up when the system is being pummeled by hundreds or
thousands of visitors to the site simultaneously (I have a script that can
simulate such pummeling against the server).

Would this then be considered a problem with DBCP's PreparedStatement
pooling?



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Bill Davidson-6]

Just thinking about this some more....

So apparently, when I was using poolPreparedStatements="true", and
I did conn.prepareStatement(SomeSQLString), I was getting back a
Statement object created by DBCP to be pooled.  When I called close()
on that statement, it did not really close(), which was leaving lots of open
cursors around.

Could maxOpenPreparedStatements possibly fix this?





---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Bill Davidson-6]

Bill Davidson wrote:
 >Could maxOpenPreparedStatements possibly fix this?

Apparently it does.

The DBCP config docs need a better warning on poolPreparedStatements:

"*NOTE* - Make sure your connection has some resources left for the
other statements."

just doesn't quite cut it.  Something more like:

"Pooling PreparedStatement's may keep their cursors open in the database,
causing you to run out of cursors.  You should also set
maxOpenPreparedStatements to some value less than the maximum number
of cursors you can have on a Connection."

or something along those lines.

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[Phil Steitz]

Bill Davidson wrote:

> Bill Davidson wrote:
>>Could maxOpenPreparedStatements possibly fix this?
>
> Apparently it does.
>
> The DBCP config docs need a better warning on poolPreparedStatements:
>
> "*NOTE* - Make sure your connection has some resources left for the
> other statements."
>
> just doesn't quite cut it.  Something more like:
>
> "Pooling PreparedStatement's may keep their cursors open in the database,
> causing you to run out of cursors.  You should also set
> maxOpenPreparedStatements to some value less than the maximum number
> of cursors you can have on a Connection."
>
> or something along those lines.

Sorry for the slow response.  Sounds like you have solved your
problem.  I assume you are now getting SQLExceptions when you go
past the maxOpenPreparedStatements limit. If not, there must be
something else going on.

Thanks for the documentation feedback.  Fixed in trunk.

This example makes me think that better behavior here would be for
the prepared statement pool to act as an LRU cache (rather than
throwing SQLException when maxOpenPreparedStatements is exceeded).
Interested in other opinions on this.

Phil


>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

[mgainty]

Thanks Phil-

there is some undocumented behaviour noticed with the (presumably DBCP) cursors
i suspect the poster is allocating n number of (presumably updateable) cursors and then not using them
the connection starves becuase the inactivity period is reached and consequently the cursors are released (as the statement handle is released)
then the poster attempts to reuse the cursor (presumably for update)

for this to be *solved* we're going to need a complete matrix of
inactivity_period=1sec

step1
allocate 1 read cursor per connection and test
allocate 2 read cursors per connection and test
allocate n read cursors per connection and test

next
alloocate 1 updateable cursor per connection and test
allocate 2 updateable currsors per connection and test
allocate n updateable cursors per connection and test

next
allocate 1 read cursor and 1 updateable cursor per connection and test
allocate 2 read cursors and 2 updateable cursors per connection and test

increment inactivity_period by 1sec
goto step1

i can test this out with mysql or oracle here
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
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: Sun, 18 Oct 2009 13:10:45 -0400
> From: [hidden email]
> To: [hidden email]
> Subject: Re: DBCP woes (running out of cursors).
>
> Bill Davidson wrote:
> > Bill Davidson wrote:
> >>Could maxOpenPreparedStatements possibly fix this?
> >
> > Apparently it does.
> >
> > The DBCP config docs need a better warning on poolPreparedStatements:
> >
> > "*NOTE* - Make sure your connection has some resources left for the
> > other statements."
> >
> > just doesn't quite cut it.  Something more like:
> >
> > "Pooling PreparedStatement's may keep their cursors open in the database,
> > causing you to run out of cursors.  You should also set
> > maxOpenPreparedStatements to some value less than the maximum number
> > of cursors you can have on a Connection."
> >
> > or something along those lines.
>
> Sorry for the slow response.  Sounds like you have solved your
> problem.  I assume you are now getting SQLExceptions when you go
> past the maxOpenPreparedStatements limit. If not, there must be
> something else going on.
>
> Thanks for the documentation feedback.  Fixed in trunk.
>
> This example makes me think that better behavior here would be for
> the prepared statement pool to act as an LRU cache (rather than
> throwing SQLException when maxOpenPreparedStatements is exceeded).
> Interested in other opinions on this.
>
> Phil
>
>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [hidden email]
> > For additional commands, e-mail: [hidden email]
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>

     
_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
http://clk.atdmt.com/GBL/go/177141664/direct/01/

[Bill Davidson-6]

Phil Steitz wrote:
> Sorry for the slow response.  Sounds like you have solved your
> problem.  I assume you are now getting SQLExceptions when you go
> past the maxOpenPreparedStatements limit. If not, there must be
> something else going on.
>  

Things are working fine now that I have it limited.  No more SQLException's
since I no longer run out of cursors.

> Thanks for the documentation feedback.  Fixed in trunk.
>  

Thanks for fixing it; though I think it would be nice if the note went
out to
the current documentation page, since this may bite other people just as it
did me.

> This example makes me think that better behavior here would be for
> the prepared statement pool to act as an LRU cache (rather than
> throwing SQLException when maxOpenPreparedStatements is exceeded).
> Interested in other opinions on this.
>  

I'm confused.

It's not throwing exceptions when maxOpenPreparedStatements is exceeded.
I thought that was the point of maxOpenPreparedStatements.  As I understand
it, when the pooling mechanism hits the maximum and it can't reuse
anything that's
in the pool, it closes older PreparedStatement's in the pool that are
not being used
at the moment so that it can create a new one and stay under the maximum. At
least, that would be consistent with what I'm seeing in my database when
I check
on the number of open cursors.




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]