[TechNote] OutOfMemoryError caused by large ScrollableResultSet with Oracle JDBC Driver

OutOfMemoryError caused by large ScrollableResultSet with Oracle JDBC Driver

Problem(Abstract)

Attempts to put large data in ScrollableResultSet through Oracle JDBC Driver results in an OutOfMemoryError on IBM WebSphere Application Server.

Symptom

OutOfMemoryError would happen due to Java heap exhaustion. If the OutOfMemoryError happens during a large object allocation, the top of the current stack trace in a javacore is similar to the following:

Current Thread Details
----------------------
"WebContainer : 1"
at java/util/Vector.growByOne(Vector.java:393)
at java/util/Vector.addElement(Vector.java:178)
at oracle/jdbc/driver/OracleResultSetCacheImpl.put(OracleResultSetCacheImpl.java:63)
at oracle/jdbc/driver/ScrollableResultSet.putCachedValueAt(ScrollableResultSet.java:2429)
at oracle/jdbc/driver/ScrollableResultSet.cacheCurrentRow(ScrollableResultSet.java:2111)
at oracle/jdbc/driver/ScrollableResultSet.cacheAllRows(ScrollableResultSet.java:2151)
at oracle/jdbc/driver/ScrollableResultSet.getLastRow(ScrollableResultSet.java:2204)
at oracle/jdbc/driver/ScrollableResultSet.last(ScrollableResultSet.java:477)
at com/ibm/ws/rsadapter/jdbc/WSJdbcResultSet.last(WSJdbcResultSet.java:2408)
at xxx/yyy/zzz/CallScrollableResultSet.query(Bytecode PC:xx)

Cause

Because all rows of any scrollable result set are stored in the client-side cache, a situation where the result set contains many rows, many columns, very large columns might cause the client-side JVM to fail because of OutOfMemoryError.

Diagnosing the problem

Identify a large accumulation of ScrollableResultSet objects by loading a heapdump (or a system dump) into IBM HeapAnalyzer or Memory Analyzer Tool. The analyzer output will show that the oracle/jdbc/driver/OracleResultsSetCacheImpl owns a java/util/Vector that is using a lot of memory in the Java heap. The java/util/Vector is holding a large array of java/lang/Object that consists of java/util/Vector objects containing oracle/jdbc/driver/CachedRowElement objects.

2,109,894,456 (79%) [1,640] 77 oracle/jdbc/driver/T4CConnection
2,109,814,992 (79%) [872] 17 oracle/jdbc/driver/T4CStatement
2,109,543,192 (79%) [144] 5 oracle/jdbc/driver/ScrollableResultSet
2,109,543,000 (79%) [40] 1 oracle/jdbc/driver/OracleResultSetCacheImpl
2,109,542,960 (79%) [48] 1 java/util/Vector
2,109,542,912 (79%) [10,485,776] 1,690,570 array of java/lang/Object
1,392 (0%) [48] 1 java/util/Vector
1,344 (0%) [96] 14 array of java/lang/Object
120 (0%) [56] 2 oracle/jdbc/driver/CachedRowElement

Resolving the problem

One of the ways to resolve the problem is to not use the scroll-ability feature with large scrollable result sets in the caller application codes. Changing the Oracle JDBC driver property Type_Scroll_Insensitive to Type_Foward_Only might help to reduce the memory usage. Please consult to JDBC Driver vendor for more details.

Related information

Oracle JDBC Implementation for Result Set Updatability