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 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.
Monday, October 02, 2006
I hate select max clauses with Oracle. My company used to write subqueries which such expressions. It's slow and unreadable. Conditions ...
I've just received my first arduino platform. It's a DCcduino board (a clone of Arduino Uno). As I had some difficulties to hav...
I'm an old user and addict of JRebel. I started to use it on open source projects and in professional contexts. From the beginning, I...
This is the challenge I had to perform. I have to open services to business partners and I want to secure them. I use a Java based applicati...