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