• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

OutOfMemoryError caused by PreparedStatements held in Connection

 
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I'm experiencing OutOfMemoryError in my application, and it's almost always caused by calling one query too many times. For some reason, the statements executed by that query do not get garbage collected. Note: it is not one instance of this query that causes OutOfMemoryError, it is a memory leak that builds up until the application runs out of memory.

Here's the setup:
- using JBoss connection pooling with Oracle oracle.jdbc.OracleDriver
- default statement cache size
- non-scrollable and non-updatable result sets
- standard query access: build the statement, execute the statement, create a new object, populate the object with data that is retrieved from the result set, close the result set, close the statement, release the connection

I've tried many things to narrow down the cause, none of which changed the problem:
- reverted to my custom connection pooling (this ruled out JBoss connection pooling as the cause)
- set the statement cache size to 0 (this ruled out statement caching as the cause)
- verified that the result sets are non-scrollable and non-updatable (this ruled out Oracle caching large amounts of data on the client)

I've posted a screenshot of a heap dump that shows the object allocation here:

Heap Dump Screenshot

I do not know what the JVMPI_GC_ROOT_MONITOR_USED means, but others have mentioned that it probably means the GC thinks these objects are definitely referenced, so it will not garbage collect them. I've verified that other queries in my application do not cause this memory leak.

Any help would be greatly appreciated!
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
seems you have done enough efforts. I have only one suggesion that check if connection is being closed properly and query have valid joins in your query.
 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
can you post a snippit of your code to show where you are opening the connection all the way through where you are closing your connection?

Thanks,
Rene
 
Steven Ostrowski
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's generally what is happening:

In JBoss, I have a datasource file:
<datasources>
<local-tx-datasource>
<jndi-name>jdbc/MyDS</jndi-name>
...


Then, I have my connection retrieval code in its own class that is used to start a transaction:
InitialContext ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:/jdbc/MyDS"));
Connection conn = ds.getConnection();
conn.setAutoCommit(false);
...
return conn;

Then, the code that uses the connection:

>>> builds up SQL statement by appending clauses into a StringBuffer
...
buffer.append("SELECT ...?..?.");
...

>>> prepares statement, and sets values, etc.
...
PreparedStatement stmt = conn.prepareStatement(buffer.toString());
...
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
MyObject obj = new MyObject();
obj.setX(rs.getString("X"));
...
aList.add(obj);
}

finally {
rs.close();
stmt.close();
}



Then, when I end the transaction:
...
conn.commit();
conn.close();
...





Hope that is enough snippets for you! =)
 
Rene Smith
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The code looks good to me. The only thing you could do is set the obj = null, rs = null, statement = null, and connection = null at the end. I don't thing any of those would cause a memory leak though....
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
even the code sounds good for me

can you tell us that how many rows resultset contains generally .

It may be possibile that you have too many rows in resultset and multile access to code causes lack in heap memory.
 
Steven Ostrowski
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Rene Smith:
The code looks good to me. The only thing you could do is set the obj = null, rs = null, statement = null, and connection = null at the end. I don't thing any of those would cause a memory leak though....



Yep, tried that, didn't do anything. I also think the GC is guaranteed to run before you get an OutOfMemoryError anyway.
 
Steven Ostrowski
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
btw - I tried this out with the Oracle 10g drivers as well, which are a very different implementation of the JDBC driver.

The screenshot of the new heap dumps are interesting in that it looks more like a deep recursion issue now:

http://www.dealraider.com/heapDump10a.jpg
http://www.dealraider.com/heapDump10b.jpg

I still haven't gotten a good definition anywhere of what JVMPI_GC_ROOT_MONITOR_USED means. However, like another poster said, it does seem to appear that this is an object the GC believes cannot be garbage collected. I've verified with -verbosegc on that even after a full GC, these objects do not get deleted.

Thanks
 
Steven Ostrowski
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sure enough, this turned out to be one of our developers not closing ResultSet and PreparedStatement. They have since been fired (j/k).
 
Cob is sand, clay and sometimes straw. This tiny ad is made of cob:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic