Re: Experiences setting OPEN_CURSORS for Java applications

Hi there,

I've supported a number of systems implemented in Java running on Oracle and on nearly all of the projects the developers have asked me to increase the number of open_cursors. In most of the cases I have found that the developers were not using the JDBC API efficiently. The main problem being that Statement and ResultSet objects where never explicitly closed. If the developer does not close these objects explicitly, the garbage collector in the Java Virtual Machine will eventually close them. However the problem is that sometimes the garbage collector does not do this soon enough and you exhaust the number of open cursors you have allocated. Even the most diligent developer's sometimes make subtle mistakes like reassigning ResultSet variables and hence losing the reference to the original ResultSet, as I found out in JBoss4.0 DR2.

I've encountered this problem so often that I decided to write a tool (called JDBC Expert) that would help us DBAs (and developers) detect Statement and ResultSet "leaks" in Java applications. I've found this tool so useful and effective at finding resource leaks that I insist any in house developed or third party Java applications are tested with it before we release them.

JDBC Expert installs like any other JDBC driver and does not generally require modifications to your application. The tool analyses how your application is using the JDBC API and reports various types of problems (such as resource leaks). I can send you a copy if you are interested - you will have to forward me your JDK version and details about any app server that you are using.

Regards,
Craig Munday.

At 08:34 AM 26/09/2003 -0800, you wrote:

>I would just like to know what are your experiences setting OPEN_CURSORS
>for Java applications / middle-tier application servers ?
>
>We're rolling out a bunch of applications on WebMethods, Tivoli Identity
>Manager,
>Plumtree, Documentum etc --- all non-Oracle clients accessing the database
>through JDBC connections.
>
>The WebMethods consultant wanted me to set OPEN_CURSORS to 500.
>Plumtree also requries OPEN_CURSORS to 250 or so.
>
>
>Hemant K Chitale
>Oracle 9i Database Administrator Certified Professional
>My personal web site is : http://hkchital.tripod.com
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Hemant K Chitale
> INET: hkchital_at_singnet.com.sg
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Craig Munday
  INET: cmunday_at_bigpond.net.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).