KETTLE_記憶體溢出錯誤，kettle溢出錯誤

KETTLE_記憶體溢出錯誤，kettle溢出錯誤

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明以下出處，否則追究著作權法律責任。

深藍的blog：http://blog.csdn.net/huangyanlong/article/details/42453831

 

kettle記憶體溢出錯誤解決

環境：
源端資料庫：oracle 10G R2
目標端資料庫：oracle 11G R2
kettle版本：5.0.1-stable

報錯：
抽取大資料量表時，報錯，日誌資訊如下：

2015/01/05 11:27:42 - Spoon - 轉換已經開啟.2015/01/05 11:27:42 - Spoon - 正在開啟轉換 [TABLE_BWWN]...2015/01/05 11:27:42 - Spoon - 開始執行轉換.2015/01/05 11:27:42 - TABLE_BWWN - 為了轉換解除補丁開始  [TABLE_BWWN]2015/01/05 11:27:44 - max.0 - Finished reading query, closing connection.2015/01/05 11:27:44 - max.0 - 完成處理 (I=1, O=0, R=0, W=1, U=0, E=02015/01/05 12:25:08 - 插入 / 更新.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : UnexpectedError:2015/01/05 12:25:08 - Spoon - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : 一個未預期的錯誤發生在Spoon: probable cause:在停止Spoon前，請先關閉其它spoon視窗! 2015/01/05 12:25:08 - Spoon - Java heap space2015/01/05 12:25:08 - 插入 / 更新.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : java.lang.OutOfMemoryError: Java heap space2015/01/05 12:25:08 - 插入 / 更新.0 -  at oracle.jdbc.driver.OraclePreparedStatement.bindBytesAsStream(OraclePreparedStatement.java:6713)2015/01/05 12:25:08 - 插入 / 更新.0 -  at oracle.jdbc.driver.OraclePreparedStatement.setBytesInternal(OraclePreparedStatement.java:6700)2015/01/05 12:25:08 - 插入 / 更新.0 -  at oracle.jdbc.driver.OraclePreparedStatement.setBytes(OraclePreparedStatement.java:6660)2015/01/05 12:25:08 - 插入 / 更新.0 -  at oracle.jdbc.driver.OraclePreparedStatementWrapper.setBytes(OraclePreparedStatementWrapper.java:150)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:4704)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.core.database.Database.setValue(Database.java:928)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.core.database.Database.setValues(Database.java:943)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.core.database.Database.setValuesInsert(Database.java:888)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:113)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:301)2015/01/05 12:25:08 - 插入 / 更新.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)2015/01/05 12:25:08 - 插入 / 更新.0 -  at java.lang.Thread.run(Unknown Source)2015/01/05 12:25:08 - Spoon - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : java.lang.OutOfMemoryError: Java heap space2015/01/05 12:25:08 - Spoon -  at java.util.Arrays.copyOf(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.StringCoding.safeTrim(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.StringCoding.access$100(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.StringCoding$StringDecoder.decode(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.StringCoding.decode(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.StringCoding.decode(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.String.<init>(Unknown Source)2015/01/05 12:25:08 - Spoon -  at java.lang.String.<init>(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.core.row.value.ValueMetaBase.convertBinaryStringToString(ValueMetaBase.java:1105)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.core.row.value.ValueMetaBase.getString(ValueMetaBase.java:1452)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.core.row.RowMeta.getString(RowMeta.java:230)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.trans.TransPreviewDelegate.showPreviewGrid(TransPreviewDelegate.java:302)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.trans.TransPreviewDelegate.refreshView(TransPreviewDelegate.java:268)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.trans.TransPreviewDelegate$1.widgetSelected(TransPreviewDelegate.java:188)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.TypedListener.handleEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Widget.notifyListeners(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.custom.CTabFolder.setSelection(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.custom.CTabFolder.onMouse(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.custom.CTabFolder$1.handleEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.EventTable.sendEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Widget.sendEvent(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Display.runDeferredEvents(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.eclipse.swt.widgets.Display.readAndDispatch(Unknown Source)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.Spoon.readAndDispatch(Spoon.java:1227)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.Spoon.waitForDispose(Spoon.java:7368)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.Spoon.start(Spoon.java:8673)2015/01/05 12:25:08 - Spoon -  at org.pentaho.di.ui.spoon.Spoon.main(Spoon.java:625)2015/01/05 12:25:08 - Spoon -  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)2015/01/05 12:25:09 - 插入 / 更新.0 - 完成處理 (I=20575, O=20574, R=20575, W=20574, U=0, E=12015/01/05 12:25:09 - TABLE_BWWN - TABLE_BWWN2015/01/05 12:25:09 - TABLE_BWWN - TABLE_BWWN2015/01/05 12:25:09 - 表輸入.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error2015/01/05 12:25:09 - 表輸入.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 2015/01/05 12:25:09 - 表輸入.0 - Couldn't get row from result set2015/01/05 12:25:09 - 表輸入.0 - ORA-03111: 通訊通道收到中斷2015/01/05 12:25:09 - 表輸入.0 - 2015/01/05 12:25:09 - 表輸入.0 -  at org.pentaho.di.core.database.Database.getRow(Database.java:2302)2015/01/05 12:25:09 - 表輸入.0 -  at org.pentaho.di.core.database.Database.getRow(Database.java:2270)2015/01/05 12:25:09 - 表輸入.0 -  at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:153)2015/01/05 12:25:09 - 表輸入.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)2015/01/05 12:25:09 - 表輸入.0 -  at java.lang.Thread.run(Unknown Source)2015/01/05 12:25:09 - 表輸入.0 - Caused by: java.sql.SQLException: ORA-03111: 通訊通道收到中斷2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1082)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:369)2015/01/05 12:25:09 - 表輸入.0 -  at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:273)2015/01/05 12:25:09 - 表輸入.0 -  at org.pentaho.di.core.database.Database.getRow(Database.java:2290)2015/01/05 12:25:09 - 表輸入.0 -  ... 4 more2015/01/05 12:25:09 - 表輸入.0 - Finished reading query, closing connection.2015/01/05 12:25:09 - 表輸入.0 - 完成處理 (I=20580, O=0, R=1, W=20579, U=0, E=12015/01/05 12:25:09 - TABLE_BWWN - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : 錯誤被檢測到!2015/01/05 12:25:09 - Spoon - 轉換完成!!2015/01/05 12:25:09 - TABLE_BWWN - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : 錯誤被檢測到!2015/01/05 12:25:09 - TABLE_BWWN - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : 錯誤被檢測到!2015/01/05 12:25:09 - TABLE_BWWN - TABLE_BWWN2015/01/05 12:25:09 - TABLE_BWWN - TABLE_BWWN

解決方案：
找到kettle目錄下的Spoon.bat檔案，找到如下部分：

REM ******************************************************************REM ** Set java runtime options                                     **REM ** Change 512m to higher values in case you run out of memory   **REM ** or set the PENTAHO_DI_JAVA_OPTIONS environment variable      **REM ******************************************************************if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xmx512m" "-XX:MaxPermSize=256m"set OPT=%PENTAHO_DI_JAVA_OPTIONS% "-Djava.library.path=%LIBSPATH%" "-DKETTLE_HOME=%KETTLE_HOME%" "-DKETTLE_REPOSITORY=%KETTLE_REPOSITORY%" "-DKETTLE_USER=%KETTLE_USER%" "-DKETTLE_PASSWORD=%KETTLE_PASSWORD%" "-DKETTLE_PLUGIN_PACKAGES=%KETTLE_PLUGIN_PACKAGES%" "-DKETTLE_LOG_SIZE_LIMIT=%KETTLE_LOG_SIZE_LIMIT%" "-DKETTLE_JNDI_ROOT=%KETTLE_JNDI_ROOT%"

修改參數，將記憶體值調大（256的倍數），調整後如下所示：

REM ******************************************************************REM ** Set java runtime options                                     **REM ** Change 512m to higher values in case you run out of memory   **REM ** or set the PENTAHO_DI_JAVA_OPTIONS environment variable      **REM ******************************************************************if "%PENTAHO_DI_JAVA_OPTIONS%"=="" set PENTAHO_DI_JAVA_OPTIONS="-Xmx1024m" "-XX:MaxPermSize=2048m"set OPT=%PENTAHO_DI_JAVA_OPTIONS% "-Djava.library.path=%LIBSPATH%" "-DKETTLE_HOME=%KETTLE_HOME%" "-DKETTLE_REPOSITORY=%KETTLE_REPOSITORY%" "-DKETTLE_USER=%KETTLE_USER%" "-DKETTLE_PASSWORD=%KETTLE_PASSWORD%" "-DKETTLE_PLUGIN_PACKAGES=%KETTLE_PLUGIN_PACKAGES%" "-DKETTLE_LOG_SIZE_LIMIT=%KETTLE_LOG_SIZE_LIMIT%" "-DKETTLE_JNDI_ROOT=%KETTLE_JNDI_ROOT%"

重新執行kettle抽取資料，不再報記憶體溢出錯誤。

小結：
        使用kettle時，如果遇到資料量大、大對象時很容易出現記憶體溢出現象，此時需要根據實際的硬體設定對預設的java堆棧大小進行調整。

 

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明以下出處，否則追究著作權法律責任。

深藍的blog：http://blog.csdn.net/huangyanlong/article/details/42453831