We have developed a huge J2EE application on ORACLE 9iAS (OC4J)for one of our clients. The basic framework uses JSP->Oracle Cleveland Architecture->Stateless Session Beans->DAO->Oracle 9i Database.

Now we had taken ample care to close all the Result Set,Prepared Statements and Connection Objects during coding and have gone through various testing phases. We have been facing this Open Cursor problem for a long time now. Everytime we dig the code and fix the some of it but still there are a lot of open cursors.

With the project progressing to near implementation, we are looking for a solution which can help us resolve this problem. The solution has to be non-invasive and most importantly should not cost us anythin (our budget is long over).

Towards this wanted to know either:
a) If there are any free open source tools which can be used to find out the code files which are keeping the cursors open. OR
b) Can we develop some custom-built tool which can find this out for us. Any pointers on such would be much appreciated. OR
c) If we can get some free tool or built some tool which sits quietly in the App Srvr and closes the open connections for us. Probably by interacting with the container or drivers???

One more constraint we are working with is that we can't take the risk of opening up the code to a large extent as all our testing phases are over.

Any help on any of the above will be much appreicated.

Regards
CZAR.

Hello Rohit,

Which version of OracleAS/OC4J are you using?(Stand Alone or part of the full OracleAS installation?)

Have your turn on the debug flag for the OC4J Datasource?
Just start your container with the Ddatasource.verbose=true option. e.g: java -Ddatasource.verbose=true -jar oc4j.jar

You can try http://www.p6spy.com/ that is an open source software that wrapp the JDBC Driver/Data Source and give you good feedback on what's happening in your application.

Generic comments/questions..
Are you coding yourself all the access to the DB or you are using EJB, Toplink or other persistence framework?

Regards
Tugdual

The implicit cursors opened by the Oracle are not closed until you close and set the connection to NULL. But if you are using Connection Pooling then Connections are closed after a specific when they are idle or Whatever technique you choose. We are also using connection Pooling and got the same problem but now we have implment a chck in our Pool class when ever we Got connection from Pool we first check either the limit of its cursor is exceeded the maximum limit that is 300 by default in Oracle and if it exceeds then we just close and set to NULL this connection and create a Fresh connection From the DB and put this in our Pool. Below is the snippet of code that we implement, I hope this might be helpful for you.
try
{
String query = "select a.value, b.name from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current'";
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(query);
rs.next();
long openedCursors = rs.getLong(1);
if(openedCursors > 270)
{
busyConnectionList.removeElement((ConnectionNode)con);
//closing the connect to release oppened cursors
closeDBConnection(con);
//creating new
newConnection = openConnection();
returnNode = new ConnectionNode(newConnection);
busyConnectionList.addConnection(returnNode);
}
rs.close();
rs = null;
s.close();
s=null;
}
catch(java.sql.SQLException ex){
ex.printStackTrace();
throw new ConnectionNodeException(ex.toString());
}

I have encounted that problem with Oracle as well... you also want to make sure that you are closing the connections in the finally block not in the try...

Connection conn = null;
Statement statement = null;
ResultSet rs = null;
try {
    con = getConnection();
    statement = conn.createStatement("...");
    rs = statement.executeQuery();
    ...
} catch(Exception ex){
    ... handle execption...
} finally {
    if(rs != null){
       try {rs.close();} catch(Exception ex){}
    }
    if(statement != null){
       try {statement.close();} catch(Exception ex){}
    }
    if(conn != null){
       try {conn.close();} catch(Exception ex){}
    }
}

that way they are always closed... this is especially important when the connections are pooled. We found code in our app that did not handle conns this way and it was the source of our cursor problem.

You're kidding right - your suggestion's just an AFD joke?
Otherwise, I'd hope nobody is taking that advice seriously ...

Stefan

If you feel there is something incorrect with the solution, you are free to provide your own or offer a correction... rather than ridiculing the contributions of others.

There's nothing wrong with YOUR solution Christopher. Sorry
if I offended you or anyone else, that was surely not my
intention. Ok, I didn't get the tone right, sorry for that
(had been a very long day).

What I was after, was that incredible

... {String query = "select a.value, b.name from v$mystat a, v$statname b where a.statistic# = b.statistic# and b.name = 'opened cursors current'";
Statement s = con.createStatement();
ResultSet rs = s.executeQuery(query);
rs.next();
long openedCursors = rs.getLong(1);
if(openedCursors > 270) ...

There is simply no need for such adventurous code if you
handle your resources like you did (irrespective of using
connection pooling, or not). Also, there's NO reason for
using home-grown connection pools at all.

The only time I have seen behavior like this (despite
correctly closing all resources) was a bug in an older
Oracle 8i JDBC driver that didn't close cursors when they
were "out" parameters in a stored procedure. That was a
long time ago and there existed a better hack for that
particular error.

BTW, I'd suggest that you move your resource cleanup in your
finally clause to a static helper function. That way, you'd
save on code duplication.

Kind regards,
Stefan

thx guys but i thought i'd clarify some points here:
a) We have written code to close all the connections objects, RS etc etc in the finally block. Its just that if 100 odd developers work someone's bound to miss that.
b) We are using the full OC4J app server.
c) we have Data access objects managing the interaction with oracle db.
c) there would be close to a 1000 DAOs in the system so any suggestion to scan the code files to rectify the problem would be impossile as well as prone to a lot of errors......

therefore what we are looking at is some component which sits outside the application monitoring the connections and tells us the culprit files...