• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

open cursors monitoring help required

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using resin2.1.x connection pooling and oracle9i,
i want to know is it possible to remove a certain connection from the pool manually. Infact i will observer if a certain connection is using more than 100 cursors, then just remove that connection pool, and get some new.
it will be use ful to prevent MAX OPEN CURSOR EXCEEDED,
i am also interested to know how to track, how many cursors are involved with a certain connection,

best regards,
waseem
waseem.aslam@advasysinc.com
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
On the database side, if you have the necessary privileges, you can see the number of open cursors per connection with:
select a.sid, a.serial#, a.username, b.cursor_count
from v$session a,
(select sid, count(*) cursor_count
from v$open_cursor
group by sid) b
where a.sid = b.sid


(The v$ views are actually mostly internal Oracle memory sturctures rather than tables; data consistency is guaranteed by locking memory briefly, so great care should be taken in designing SQL that will query them. A complex, poorly written query can relatively rarely bring performance to an absolute crawl).

I don't know how you could tie this information to the connection objects in the Java world though...
 
Ranch Hand
Posts: 775
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Not familiar with Resin specifics, but most J2EE containers have JMX services that have info like this. You may want to look to the Resin docs for anything that sounds JMX/MBean-ish for similar info. If not, you can always write your own JDBC driver implementation that proxies around the real driver to install your own tracking. I had to do that once for a hideous legacy webapp that was leaking connections and it isn't that hard. Basically the same thing as creating mock objects for unit testing.

Exercise the app, find the places that are leaking result sets, fix them, and go back to the original driver. Much more effective than trying to hack active connections, unless you are trying to be some form of hosting site and the problem is from code you can't fix.
[ March 09, 2006: Message edited by: Reid M. Pinchback ]
reply
    Bookmark Topic Watch Topic
  • New Topic