java.lang.OutOfMemoryError: 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:

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

1. 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.
   
   
   
2. 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