You Asked

Tom,
First of all thanks for giving so much time to this forum. We appreciate all your excellent work.
In your books I read a lot about bind variables, reusing or cursors etc. According to you the correct way to use cursors is to parse it once, and then bind and use it mutiple times.
Now lets see how best can we apply this to JDBC. And in particular to the concept of connection pool. As we all know that when a connection pool is initialized at the application start up there a finite number of connections that are open. Now I was thinking can we take this concept of reuse of cursors this far:
At connection pool initialization time we parse all the packaged procedure calls against each and every connection from the connection pool. And at runtime we bind the statement, get the resultset, and then close the result set. And then DO NOT close the cursor. Next time we just reuse this cursor. And ofcourse for taking the care of the case when the connections in the connecton pool are exhausted we just put a check if the statment is null; and if it is null just parse the cursor again.
This was just a thought, I do not know if this is possible. Can we get your thoughts on this one?

Regards

and Tom said...

That would be an excellent enhancement to your code.

Actually, what would be perhaps even better would be to associate two hash tables with your connection object -- so when you grab a connection, you also get two hash tables.

These hash tables would contain Prepared and Callable statements. One hash table would be "persistent" statements -- the other "session only" statements (the difference being that persistent statements are expected to be used and reused by the majority of the users using that connection over time whereas session statements are not expected to have high reuse)

The application would, prior to parsing, look up the statement in the hash table. If not found, then parse and add -- else reuse.

Prior to giving the connection back -- you would iterate over the session hash table and close any cursors.

This session hash table is nice in all cases -- even if you decide to not use the persistent concept, simply to avoid "cursor leaks" which seem to run rampant in java code as exceptions leave result sets and cursors "in limbo" as they go out of scope. It lets you clean them up nicely.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Reuse of cursors in JDBC

Rizwan Qazi, May 24, 2004 - 9:11 am UTC

Tom,
In your statement: "The application would, prior to parsing, look up the statement in the hash table. If not found, then parse and add -- else reuse." I suppose by hash table here you mean the one with "persistent" statments, right?
Also, I am not still too clear about the hash table with "session only" statements. Will it be used only by the current session/transaction; because you say that it should be iterated and all CURSORS within it closed before returning the connection. In that case you mean your initial statement of "parse and add -- else reuse" will mean that we add the "not found" cursor to both the hash tables (persistent as well as session). Please comment.

May 24, 2004 - 9:17 am UTC

the application would have to decide whether it was going to look in the session or persistent hash table if it was using both.

I'm just saying "might be nice to have two hash tables -- one that has cursors that live for the life of the connection -- all users reuse over and over, another for cursors that the developer reasonably expects are somewhat "unique" and will not be reused very much if at all"

you decide which to use.

Reuse of cursors in JDBC

A reader, May 24, 2004 - 9:24 am UTC

Reuse of cursors in JDBC

Rizwan Qazi, May 24, 2004 - 9:24 am UTC

JDBC 3.0 Reuse of PreparedStatement (caching)

Alberto Dell'Era, May 24, 2004 - 12:49 pm UTC

Perhaps what you want is already implemented ?

Don't know whether it will survive in a connection pool ... but it may be worth a look (it's similar to the session cursor cache but on the client side afaik, never used it yet).

</code> http://otn.oracle.com/products/oracle9i/daily/jun24.html <code>

hth
Alberto

May 24, 2004 - 1:11 pm UTC

thanks very much -- that looks exactly like what I described :) awesome.

Reuse of cursors in JDBC

Rizwan Qazi, May 24, 2004 - 2:18 pm UTC

Great! This reinforces one of Tom's other constant reminders. Go through the manuals! Here is the link to the same in the Oracle9i JDBC Developer's Guide and Reference:

</code> http://download-west.oracle.com/docs/cd/B10501_01/java.920/a96654/stmtcach.htm#1056354 <code>

Closing JDBC cursor areas

Wayne, November 24, 2008 - 3:44 pm UTC

Tom,

I am researching a colleague's problem with jdbc causing cursor leaks in their application.

They are using jdbc as connection pooling and closing the session would impact more than one user.

Is there a way for me (DBA) to close open cursors in a session without closing the session? Is there a dbms package that I could use to close cursors in a session?

Thanks

Wayne

Thanks

Wayne

November 24, 2008 - 7:50 pm UTC

jdbc doesn't cause cursor leaks.
programmers cause cursor leaks.

why would you try to fix a bad program bug with a sledge hammer - many cursors SHOULD stay open (you need to parse a cursor at least ONCE and at MOST once)

You could seriously break stuff by closing cursors (someone might have coded things properly, they sort of expect their stuff to be there...)

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sessio.htm#i1010767

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Reuse of cursors in JDBC

Reuse of cursors in JDBC

Reuse of cursors in JDBC

JDBC 3.0 Reuse of PreparedStatement (caching)

Reuse of cursors in JDBC

Closing JDBC cursor areas

More to Explore

PL/SQL demos

PL/SQL docs