Hi we are experiencing problems with our web application. We are increasingly getting Oracle - NO More Open cursors error. On investigating our code we found that there is a piece of code where we are executing a Prepared Statement in a loop with different parameters.We are not closing the statement between the executions. Instead it is closed after the loop. Does any one know what the best practice is. I am not sure if there is a gain/loss in performance in keeping the statement open. We are using JDBC 2.0. I would appreciate any pointers. Thanks
Are you closing the ResultSet each time? I would expect that there's no need to close the Connection or PreparedStatement (in fact it's usually best to avoid doing so until you're sure you don't need then anymore) but each ResultSet probably needs closure. Garbage collection will probably take care of this eventually for you, but maybe not as promptly as you need.
Originally posted by Srihari Injeti: We are increasingly getting Oracle - NO More Open cursors error.
This is definitely due to a resource leak -- a failure to close ResultSets or Statements. Been there, done that, got the tar and feath... I mean T-shirt.
On investigating our code we found that there is a piece of code where we are executing a Prepared Statement in a loop with different parameters. We are not closing the statement between the executions. Instead it is closed after the loop.
This is fine. But you must make sure that you do close the ResultSets faithfully. Use finally clauses to ensure they get closed whatever the code path is -- even if there are errors (exceptions). Ditto for any statements you use. It can be convenient to know that when you close a statement, you automatically close any ResultSet associated with it.
Does any one know what the best practice is. I am not sure if there is a gain/loss in performance in keeping the statement open. We are using JDBC 2.0.
There is a small performance gain compared to using a new PreparedStatement every time. Even if you create a new PreparedStatement object every time, as long as the SQL statement text is exactly the same Oracle will retrieve the compiled statement and execution plan from its cache, so you still get virtually all the benefit of using prepared statements. But re-using the same statement is not the root cause of your problems, I think.
Jim Yingst wrote: Garbage collection will probably take care of this eventually for you, but maybe not as promptly as you need.
That's what I thought, but apparently it's not true. In a project, I used an O/R mapping tool (Castor) that had a bug causing statements not to be closed under rare circumstances. After weeks of uptime, the application would eventually run into the "too many open cursors" problem. It seems that these cursors were slowly leaking away over long periods of time. This is only possible if they are not eligible for garbage collection. They probably would have been closed if the Connection was ever closed or gc'd, but I used a connection pool so Connections would be very long-lived. I'm not 100% sure, but it would appear that you cannot count on garbage collection in this case. Upgrading to a more recent version of Castor fixed the problem, BTW. - Peter  And when I say exactly the same I mean exactly the same, including upper/lowercase and number of spaces. [ February 08, 2003: Message edited by: Peter den Haan ]
Joined: Jan 30, 2000
Well, I did say "probably". GC can never really be absolutely guaranteed to do anything really, but it usually works pretty well if the code doesn't prevent an object from even being eligible for GC. Interesting to know that about re-using a PreparedStatement in Oracle. I recall I did once get about a 30% performace gain from a system by changing code to reuse a PreparedStatement rather than recreate it each time it was to be executed. (To my mind recreating the PS just defeated the whole point of using a PS rather than a regular Statement; I never knew why the code had been written that way in the first place.) This was using Oracle 8i I believe (or maybe it was still just 8 at this point and we transitioned to 8i later). Perhaps the statement cache was added (or improved) in a later version? Anyway though - it's cool that Oracle can retrieve previous statements like this, but I see no reason to depend on Oracle to do it; re-using a PreparedStatement is what the class was designed for after all.