Monday, October 02, 2006

Hibernate 3 and Oracle 9i cursors : how does it work?

Here is a post I found on the hibernate forum. It explains why cursors opened are not released on session.close() :

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 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.

Bulent Erdemir

1 comment:

Rahul said...

Thanks for the great information in your blog Selenium Training in Chennai