Re: Memory loss with JDBC and PreparedStatements

Hi Rob,
  nope, haven't seen that problem. When support suggested to close the prepared statement, that would make sense to me, but I'm picturing the scenario that the prepared statement is opened in some method. If this is the case, you should be closing the prepared statement right before the method is being exitedor right after the loop that is using it is exited. Now, I would expect to close the prepared statement before exiting my app (application/applet) if I had a global prepared statement opened upon starting the app. But, that may be the problem. I say that because I have seen an occasion where my sql was failing because my connection seemd to be "old". I was making a global connection when I started my app and after various selects/inserts/updates, my app would fail when trying to run a stored procedure. I figured out that if I closed the global connection and reopened a connection in my method, right before trying to run the stored procedure, it would work. To me, that indicates a bug in the driver, but hey, no big deal.
  So, my advice to you is move the prepared statement usage into a method. Open and close it within that method. Also, you didn't mention what version of java you have developed with, so if you are using jdk1.1.x, your driver would be in the classes111.zip file and if that is the case, rebuild in jdk1.2.x and try the classes12.zip driver. I don't believe you have to upgrade your db for any of the reasons you've described, just possibly your jdbc driver. You may want to upgrade your db for other bug fix reasons.

cheers!
Cindy

Rob Dickinson wrote:

> We're using Oracles 8.0.3 "thin" JDBC driver and are seeing serious
> memory loss when we use PreparedStatements in our system.
>
> Basically, every time we execute a PreparedStatement query, JDBC
> gobbles up 20,000 DbItem objects and around 2M of Java heap
> space. We can't seem to find any way to reclaim that memory, even
> though we close the resulting ResultSet. We've been told we can
> close the PreparedStatement, but that would defeat the purpose.
>
> Has anyone else seen this problem?
>
> Is there any known workaround for this?
>
> Oracle tech support told us that the 8.0.3 server and driver were bad,
> and to upgrade to 8.0.4. I'm suspicious if this will help on this
> problem.
> Does anyone have any idea if these could be related.
>
> Thanks for any information.
>
> Rob Dickinson
> Perspecta, Inc.
> robd_at_perspecta.com
> 415-437-4164
Received on Mon Sep 20 1999 - 19:07:41 CDT