ScrollableResults OutOfMemoryError as soon as I touch it.

angry planet · **Joined:** Thu Mar 24, 2005 11:15 am **Posts:** 8

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Aug 25, 2006 2:44:36 PM by Hibernate Tools 3.2.0.beta7 -->
<hibernate-mapping>
    <class name="com.parago.dao.ImCode" table="IM_CODE" schema="IM" lazy="true">
        <id name="codeid" type="long">
            <column name="CODEID" precision="22" scale="0" />
            <generator class="increment" />
        </id>
        <property name="updatedate" type="calendar" insert="true" update="true">
           <column name="UPDATE_DATE" />
        </property>
        <property name="insertdate" type="calendar" insert="true" update="false">
           <column name="INSERT_DATE" />
        </property>
        <property name="gameid" type="long">
            <column name="GAMEID" precision="22" scale="0" />
        </property>
        <property name="code" type="string">
            <column name="CODE" length="25" unique="true" />
        </property>
        <property name="redeemflag" type="boolean">
            <column name="REDEEMFLAG" />
        </property>
        <property name="usedflag" type="boolean">
            <column name="USEDFLAG" />
        </property>
        <property name="playerid" type="long">
            <column name="PLAYERID" precision="22" scale="0" />
        </property>
        <property name="batchid" type="long">
            <column name="BATCHID" precision="22" scale="0" />
        </property>
    </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close(): N/A

Full stack trace of any exception that occurs:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space

Name and version of the database you are using: Oracle 9i

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I am trying to randomly access elements in a very large ScrollableResult Set (approx 12million records). I get the ScrollableResults, generate a random number in my set size, and try to setRowNumber(rndnum). It throws the OutOfMemory error the first time. I have use_scrollable_resultset set to true.

As a test, I tried to call results.last(), and it threw the same error.

From my reading of the forum search results and the documentation, I can see how I could get this error if I don't flush the session periodically while operating on the set elements, but this error is occurring the first time I try to touch the results. I thought this was the correct way to access a result set without eagerly fetching the entire set.

What obvious simple piece of information am I missing?

angry planet · **Joined:** Thu Mar 24, 2005 11:15 am **Posts:** 8

As a follow-up, it looks like it works if the random number is sufficiently small (so setRowNumber is relatively early in the resultSet). If the next random number is large enough to put me too far into the ScrollableResults, it fails as before.

scarface · **Joined:** Sun Jun 04, 2006 1:58 am **Posts:** 136

angry planet wrote:

What obvious simple piece of information am I missing?

Not to use hibernate to process 2 million rows :)

angry planet · **Joined:** Thu Mar 24, 2005 11:15 am **Posts:** 8

Quote obviously, I am not.

I am using Hibernate to process about a dozen out of 12 million rows.

max · **Posted:** Thu Dec 28, 2006 6:15 am

maybe your db/driver implements scrollable resultset's in a bad way...

Did you try it with pure jdbc ?

david · **Posted:** Thu Dec 28, 2006 8:01 pm

Need to be careful - as Max pointed out - the concept is great but often (in fact almost all providers) don't implement their JDBC drivers optimally. For example, the majority load the whole/majority of the set into memory then walk the set rather then using the native db cursors to do the work. Check you JDBC driver feature set and implementation carefully. Your likely better off selecting a subset and page through the data.

zaplitny · **Posted:** Thu Jan 18, 2007 12:13 pm

Had the same problem.

Quote:

Because the underlying server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer.
It is important to be aware that this is accomplished by using a client-side memory cache to store rows of a scrollable result set.
Important:
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, or very large columns might cause the client-side Java virtual machine to fail. Do not specify scrollability for a large result set.
Scrollable cursors in the Oracle server, and therefore a server-side cache, will be supported in a future Oracle release.

Fixed by using Query.scroll(ScrollMode.FORWARD_ONLY) instead of Query.scroll(). It is possible if you don't use methods specific for ScrollableResults(isLast(), isFirst(), etc.)