Hello,

I'm using session.iterate() over a set of records. When I don't scroll to the end of the resultset, the cursors stay open on the database. And they don't go away after session.close().

How can I close a cursor created by a session.iterate()?

Regards...

Serkan Soykan

Are you sure about that?!

They should get closed when you close the session! In fact I'm certain they do!

What evidence do you have for this?

Hi, I work with the owner of the first post on this subject,

I'm sure that we saw open cursors on the db (oracle). But I'm not sure now whether they were prepared statements or actual open cursors (we use the prepared statement cache feature of hibernate). It might turn out that the tool we use to list the cursors in a database session also shows the prepared statements for the session.

We need to check this again (first thing on Monday at the office).
Thanks for the answer,

PS. Once we find the answer, we'll post a reply under this topic, for future hibernator's reference.

Regards,

Bulent Erdemir

Oh yeah, this happens with Oracle. Its in the FAQ.

Quick answer: Do not use Hibernate's PreparedStatement cach in production.

To avoid any misunderstanding; do you suggest not to use prepared statement cache of hibernate, because that'll be a quick solution to our problem, or because you think that it's not mature (or reliable) enough for a production environment?

Hibernate's PreparedStatement cache and connection pool are not intended for use in production. Use a "real" connection pool for this stuff.

Actually, in the latest 2.1 code I removed the PreparedStatement cache entirely.

I have run into this not in hibernate, but in in a pool we in our Web apps that caches prepared statements. Since the prepared statment is never really closed (its being held in the cache for possible re-use) it holds the cursors open in Oracle. The Oracle cursors do not get released until the connection is actually closed. In this particular pool after a certian number of checkouts the connection is discarded and a new one is created. At this point the cursors are really released. If your app uses many unique prepared statments then you can eventually get a out of cursors error from Oracle.

I debugged the program and found that during the call to Session.close(), SessionImpl.disconnect() is called, in which there's a statement like batcher.closeStatements(). This call iterates through the open resultsets and prepared statements and attempts to close the one by one. The number of items in the statementsToClose list is equal to what I see in the database by the call;
select count(*) from v$session s, v$open_cursor o where s.sid=o.sid and s.program='javaw.exe' --count the number of open cursors
So I assume the iteration has correct info in it (it's not missing the active cursors and statements).

Gavin also said that he was certain that prep.stmt are all closed.

However, even though the Oracle Jdbc driver's close statement is called (in public class oracle.jdbc.driver.OracleResultSetImp extends oracle.jdbc.driver.BaseResultSet), the driver does not close the cursor in the database. I sniffed the TCP line, there's no activity during the close call, either.

So, this issue seems to be a problem of the Oracle 9i Jdbc driver, or the configuration of it (oracle jdbc driver has its own statement caching facility. I checked if this is enabled by getStmtCacheSize() method of OracleConnection and it returned 0).

Regards,
Bulent Erdemir


from BatcherImpl.class:
public void closeStatements() {
Iterator iter = resultSetsToClose.iterator();
while ( iter.hasNext() ) {
try {
logCloseResults();
( (ResultSet) iter.next() ).close();
}
catch (SQLException e) {
// no big deal
log.warn("Could not close a JDBC result set", e);
}
iter.remove();
}
resultSetsToClose.clear();

iter = statementsToClose.iterator();
while ( iter.hasNext() ) {
try {
closeQueryStatement( (PreparedStatement) iter.next() );
}
catch (SQLException e) {
// no big deal
log.warn("Could not close a JDBC statement", e);
}
iter.remove();
}
statementsToClose.clear();
}

I wonder if, contrary to the JDBC spec, Oracle requires that actual ResultSets be closed.


What happens if you call ScrollableResults.close() ?

(Or Hibernate.close(Iterator) in 2.1)

As this prep stmt behaviour is very important for our application (and we need prep stmt caching), I'm digging more. Here're my latest findings:

I'm using JDK1.4.2_01 and Oracle 9i Jdbc driver (ojdbc14.jar and nls_charset12.jar). I'm sure that jdbc driver prep stmt cache is disabled (OracleConnection.getImplicitCachingEnabled() returns false).

I have 3 load queries, and 3 iterate queries running in a for loop for 10 times.
When I access the db using oci driver, 33 open cursors (or prep statements) remain open after the app finishes.

When I change the access method to the thin driver, only 6 cursors remain. 3 of them are queries generated by load(), 3 of them are generated by iterate() command (iterate generates 3 queries, first one fetches the key set, the other 2 fetch from individual tables).

So Jdbc driver's behaviour is different between thin and oci versions.

Gavin, I'll try ScrollableResults.close() later. Need to go now. However, in the BatcherImpl.closeStatements(), first the resultSetsToClose HashSet is iterated and they are closed, then comes the statementsToClose HashSet. I mean, even if Oracle expects the actual resultset to be closed before closing a prep stmt, you are already doing that.

Regards,
Bulent Erdemir

OK. Here's the result of this thread:

For the environment, JDK1.4.2_01, Oracle 9i server, oracle 9i jdbc driver (ojdbc14.jar) and oci connection to the database (not thin driver);

There's no problem with hibernate's prep stmt cache nor with Oracle Jdbc driver with regard to cursors remaining open after Session.close().

Every Session.iterate() and Session.load() calls cause a prep stmt to be created on the server side. And after you close the session with Session.close(), hibernate explicitly calls appropriate driver methods to close the resultsets and prepared statements.

However, after you close the session, you won't see the cursors de-allocated (or closed) on the server side. While it seems to be a bug, it actually is not. The driver maintains a list of prep statements and open cursors in its memory and as soon as you re-use the same connection by opening a session on that very same connection, you'll see that the previous cursors and prep statements will go away.

To check this behaviour, open a session, run a few Sesson.load(), Session.iterate() methods and then call Session.close(). Run the below query and you'll see that the cursors are still there (in the statistic 'opened cursors current').

select n.*,s.* from v$sesstat s, v$statname n where s.statistic#=n.statistic# and sid=23 and n.name like '%cursor%'

However, in the same program, after the above calls, if you
call SessionFactory.getSession() and grab the same jdbc connection from the pool;
as soon as you call Session.load() or Session.iterate(), you'll see that the number of open cursors will be 0 or decrease dramatically. This means that the jdbc driver is smart enough to track the status of the cursor and prep stmt and reports the closed cursors in the next round-trip to the database, hence reducing network round-trips, which is a good thing for performance.

Regards,
Bulent Erdemir

Can you add this to the wiki Community Area for future reference?