on 04-02-2015 7:58 AM
Hi,
In one of our factories we are getting an OutOfMemory error during ODS transfer for Production Log Summary.
It started appearing already in end of November, but it has not been noticed, since we don't have any current reports in
that factory that uses the summary data.
I have tried running the ODSProductionETL script with just 1 minute parameter, but I still get the same error.
Exact error message is:
D:\SAP\SAPME\Clients\scripts>call ODSProductionETL.bat -1
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceede
d
at java.util.Hashtable.put(Hashtable.java:420)
at com.sap.me.frame.Data$DataRow.put(Data.java:1812)
at com.sap.me.frame.Data.put(Data.java:718)
at com.sap.me.ods.database.ODSBaseDataSource.getData(ODSBaseDataSource.j
ava:201)
at com.sap.me.ods.database.ODSBaseDataSource.getData(ODSBaseDataSource.j
ava:160)
at com.sap.me.ods.database.ODSBaseDataSource.executeQuery(ODSBaseDataSou
rce.java:115)
at com.sap.me.ods.olap.ODSProductionLogSummary2.getSummaryData(ODSProduc
tionLogSummary2.java:158)
at com.sap.me.ods.olap.ODSProductionLogSummary2.summary(ODSProductionLog
Summary2.java:124)
at com.sap.me.ods.olap.ODSProductionLogRollupProcess.ODSSummary(ODSProdu
ctionLogRollupProcess.java:457)
at com.sap.me.ods.ODSProductionLogSummary.main(ODSProductionLogSummary.j
ava:60)
The java options in ClientEnvironment is set to:
set JAVA_OPTIONS=-Xms1024m -Xmx2048m -Dsce.home=D:\SAP\SAPME\Clients\scripts -DoutOfContainer=true -Dtemp.dir=D:\SAP\SAPME\Clients\scripts -Ddb.vendor=SQLSERVER -Djava.naming.factory.initial=com.sap.engine.services.jndi.InitialContextFactoryImpl -Djava.naming.provider.url=localhost:50004
I turned on SQL trace and for the ODSProductionLogSummary 2 part this is the result:
Wed Apr 01 20:10:46 CST 2015 ODSProductionLogSummary 2: Started
Wed Apr 01 20:10:48 CST 2015 ODSProductionLogSummary 2: [609]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_LAG_TIME' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:48 CST 2015 ODSProductionLogSummary 2: [17]{1} SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE WHERE TABLE_NAME ='PRODUCTION_LOG' AND TRANS_TYPE ='1'
Wed Apr 01 20:10:48 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE WHERE TABLE_NAME ='PRODUCTION_LOG' AND TRANS_TYPE ='2'
Wed Apr 01 20:10:48 CST 2015 ODSProductionLogSummary 2: [2]{1} SELECT EXTRACT_DATE_TIME FROM ODS_EXTRACT_CONTROL_DATE WHERE TABLE_NAME ='PRODUCTION_LOG' AND TRANS_TYPE ='4'
Wed Apr 01 20:10:48 CST 2015 ODSProductionLogSummary 2: 5 UPDATE ODS_EXTRACT_CONTROL_DATE SET LAST_EXTRACT_DATE_TIME = '2014-11-30T11:35:11.000Z' , EXTRACT_DATE_TIME = '2015-04-01T12:05:01.000Z' WHERE TABLE_NAME = 'PRODUCTION_LOG' AND TRANS_TYPE = '4'
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [2958]{1} SELECT DISTINCT SITE FROM ODS_PRODUCTION_LOG WHERE PARTITION_DATE > '2014-11-30T11:35:11.000Z' AND PARTITION_DATE <= '2015-04-01T12:05:01.000Z' ORDER BY SITE
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [4]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_ROUTER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_ROUTER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [2]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_RESOURCE' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_RESOURCE' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_SHOP_ORDER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_SHOP_ORDER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_CUSTOMER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_CUSTOMER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_CUST_ORDER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [4]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_CUST_ORDER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [2]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_STEP_ID' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_STEP_ID' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [2]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_COST_CENTER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_COST_CENTER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_REP_CENTER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_REP_CENTER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_USER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [4]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_USER' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_LCC' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_LCC' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{0} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='CNSZ1' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_ROLLUP_CC' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT SYSTEM_RULE.VALUE_OBJECT_TYPE, SYSTEM_RULE_SETTING.SYSTEM_RULE_SETTING FROM SYSTEM_RULE, SYSTEM_RULE_SETTING WHERE SYSTEM_RULE_SETTING.SITE='*' AND SYSTEM_RULE.SYS_RULE='ODS_PCT_SUM_BY_ROLLUP_CC' AND SYSTEM_RULE.HANDLE=SYSTEM_RULE_SETTING.SYSTEM_RULE_BO
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT TIME_GRANULARITY_BO FROM TIME_GRANULARITY_ASSIGNMENT WHERE SITE='CNSZ1' AND SUMMARY_NAME='PRODUCTION_CYCLE_TIME'
Wed Apr 01 20:10:51 CST 2015 ODSProductionLogSummary 2: [3]{1} SELECT GRANULARITY, TYPE, START_DATE, END_DATE, RECURRING_YEARLY FROM TIME_GRANULARITY WHERE HANDLE='TimeGranularityBO:DAY'
Does anyone have an idea on what is happening or ideas on how I can troubleshoot this further?
Best regards,
Johan
Hi Johan,
How many records do you have in
ODS_TIME_DIM
ODS_OPERATION_CYCLE_TIME
tables?
How often do you execute WIP and ODS Aging scripts? What are the settings in ODS Rule Maintenance to retain data?
Could you also double check versions in lib directory of ME Clients?
Br, Alex.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Johan
Once you've hit out of memory, you can't recover this way. There is now too much data in the temp table to process.
Actually, the parameter to pass in minutes is just the minutes, without the "-" sign.
Run this SQL:
alter session set nls_date_format = 'DD-MON-YYYY HH24:Mi:ss';
select * from ods_extract_control_date
order by 1,2;
and post the output here
Hi Stuart,
The output from the query is:
TABLE_NAME TRANS_TYPE LAST_EXTRACT_DATE_TIME EXTRACT_DATE_TIME
DPMO_TRANSFER 1 2-Apr-2015 7:37:45 2-Apr-2015 8:38:17
DPMO_TRANSFER 2 2-Apr-2015 7:37:45 2-Apr-2015 8:38:17
DPMO_TRANSFER 3 2-Apr-2015 7:37:45 2-Apr-2015 8:38:17
NC_DATA 1 2-Apr-2015 7:36:49 2-Apr-2015 8:36:56
NC_DATA 2 2-Apr-2015 7:36:49 2-Apr-2015 8:36:56
NC_DATA 3 2-Apr-2015 7:36:49 2-Apr-2015 8:36:56
PRODUCTION_LOG 1 2-Apr-2015 7:35:3 2-Apr-2015 8:35:4
PRODUCTION_LOG 2 2-Apr-2015 7:35:3 2-Apr-2015 8:35:4
PRODUCTION_LOG 3 1-Apr-2015 11:53:52 1-Apr-2015 12:5:1
PRODUCTION_LOG 4 30-Nov-2014 10:35:7 30-Nov-2014 11:35:11
PRODUCTION_LOG 5 30-Nov-2014 10:35:7 30-Nov-2014 11:35:11
PRODUCTION_LOG 6 1-Apr-2015 10:35:3 1-Apr-2015 11:35:3
PRODUCTION_LOG 7 1-Apr-2015 10:35:3 1-Apr-2015 11:35:3
SHOP_ORDER 1 2-Apr-2015 7:35:31 2-Apr-2015 8:35:32
SHOP_ORDER 2 2-Apr-2015 7:35:31 2-Apr-2015 8:35:32
Best regards,
Johan
Johan
Can you confirm there is data from 02 April in ODS_PRODUCTION_LOG? If so, then we're OK to just fix the summarising
How frequently was the bat file running before it failed?
Looks like it is just the summary that is failing. If you have no reports on the summaries, then actually it's a waste of time to summarise at all (you can always start this later if you have a business need).
But to fix, stop the scheduled job. Edit the .bat to run in a loop by commenting out the 2 rem statements as commented in the bat file itself, and pass a number of minutes much less than the time between executions when it was scheduled.
e.g if it was running every hour, try 10 mins:
ODSProductionETL.bat 10
Hi Stuart,
Yes, there is data in ODS_PRODUCTION_LOG from 2nd of April.
Yes, it is only the ProductionLogSummary that is not working. We decided to add summary to all sites, so that we have historical data as well when we have a global report that we want to roll out.
The ODS transfer bat file runs every hour. If I try to set it to a lower value I still get the OutOfMemory error. Even if I set the parameter to just 1 minute. The fail scenario is that I can see with SQL trace that a few selects are run and after about 30 seconds the CPU goes up to 95% usage for about a 1 minute and then comes the OutOfMemoryError.
Best regards,
Johan
Hello Johan,
I assume it might be a bug in the code when operating HashMap/Hashtable object similar to what's described here:
hashmap - java.lang.OutOfMemoryError: GC overhead limit exceeded - Stack Overflow
Please submit a support ticket if nothing helps. I don't believe it's related to amount of data.
Regards,
Konstantin Chernega
Now I have it running again and running it in a loop to have it catch up to the current date.
I don't know the exact root cause and why it started working, but I suspect that it was the "dash" character that I placed before the parameter to the script.
After running it with Stuart's suggestion "ODSProductionETL 10" it started working and now I am running it with "ODSProductionETL 720" without any problem.
Best regards,
Johan
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.