오라클 Java heap space - olakeul Java heap space

When trying to load a huge set of data from MySQL using Oracle Data Integrator (ODI), the execution Agent fails with the following error:

목차 Show

java.lang.OutOfMemoryError: Java heap space

Cause

This issue is due to a MySQL JDBC Driver issue.

When selecting a large amount of data, the MySQL JDBC Driver loads the entire ResultSet returned by the select to the JVM's memory.

This seems to be the case for any version of MySQL JDBC Driver.

Solution

A work around for that issue would be to increase the Java Memory allocated to the Oracle Data Integrator (ODI) Agent, to allow it to be able to load the entire ResultSet.
To do so, increase the value of the ODI_MAX_HEAP parameter in the "odiparams.bat"/.sh file in the ODI/bin directory.
The MySQL JDBC Driver can handle "StremingResults" method which will allow the result set to be streamed by MySQL JDBC driver and inserted in work table as it comes rather than being loaded entirely in memory (which cause JVM heap overflows).
To force the JDBC Driver to use that method, the "LKM SQL to SQL (Jython)" Knowledge Module can be modified to add the "readSrc.enableStreamingResults()" command.
In the LKM "Load" step code, replace:
# Statement source + metadata readSrc = ConSrc.createStatement()
sqlSrc="""select <%=snpRef.getPop("DISTINCT_ROWS")%> <%=snpRef.getColList("", "[EXPRESSION] [ALIAS_SEP] [CX_COL_NAME]", ",", "", "")%> from <%=snpRef.getFrom()%> where (1=1) <%=snpRef.getFilter()%> <%=snpRef.getJrnFilter()%> <%=snpRef.getJoin()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"""
readSrc.setFetchSize(100) rqteSrc = readSrc.executeQuery(sqlSrc) metaDataSrc = rqteSrc.getMetaData()
by:
# Statement source + metadata readSrc = ConSrc.createStatement() readSrc.enableStreamingResults()
sqlSrc="""select <%=snpRef.getPop("DISTINCT_ROWS")%> <%=snpRef.getColList("", "[EXPRESSION] [ALIAS_SEP] [CX_COL_NAME]", ",", "", "")%> from <%=snpRef.getFrom()%> where (1=1) <%=snpRef.getFilter()%> <%=snpRef.getJrnFilter()%> <%=snpRef.getJoin()%> <%=snpRef.getGrpBy()%> <%=snpRef.getHaving()%>"""
rqteSrc = readSrc.executeQuery(sqlSrc) metaDataSrc = rqteSrc.getMetaData()

출처 : https://metalink2.oracle.com/metalink/plsql/f?p=130:14:7060163129457566007::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,737309.1,1,1,1,helvetica

I'm using SQL Developer on Windows Vista:

Oracle IDE	2.1.1.64.45
Plate-forme Java(TM)	1.6.0_11
Prise en charge de la gestion des versions	2.1.1.64.45

I always get "java heap space" error when I run against a 10.2.0.4 database (Oracle Client is Oracle XE):

select * from dba_scheduler_jobs;

Then I get:

OALL8 est dans un état incohérent

Is this a known issue ? Is there any workaround ?

Answers

You could try increasing or decreasing the heap size in \ide\bin\ide.conf (in small steps of about 100M):

AddVMOption -Xmx640M
Hope that helps,
K.
It works for one connection. But trying to run the same SQL statement for 2 differents database connections still fails with "Java heap space" even with:
```
AddVMOption  -Xmx1024M
```
I'm having a similar problem. The error first appeared when retrieving more than a million rows. I completely killed SQL Developer and rebooted XP, but the error won't go away. Now any query against any DB gives me alternating errors "Java heap space", "OALL8 is in an inconsistent state", "Bigger type length than Maximum", and "Protocol violation". Is there some kind of cache I need to clear?
EDIT: Solved. I had increased the SQL Array Fetch Size setting to a large number that SQL Developer didn't like. The error messages could have been a little more helpful.
Edited by: jarnold on Dec 29, 2010 10:47 AM
Thank you so much for posting this solution! This error has been bugging me ever since I set my fetch size to 20,000. It made the program nearly unusable.
Fetch size of 20,000? That's really not recommended...
K.
Thanks K.
So, what's recommended? Does it depend on the memory required per row? Is it tied to the heap space you allocate?
Ken
This is how many rows the jdbc driver will get at one time and hold in memory. So there's lots of factors like row size, client memory , bandwidth ,.. I'm going to put a limitor of 500 into the tool since I never thought about this case.
-kris
I also was getting the "Java heap space" error when using 30EA2 SQL Developer to migrate a large Access 2003 database. I changed the ide.conf file from the default of 640 to 1024 and closed most all other programs running on my 2GB RAM laptop. This allowed the migration to complete successfully.