ODSProductionLogSummary OutOfMemory error

[Former Member]

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

[alexander_teslyuk · Accepted solution]

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.

[Former Member · Accepted solution]

Hi Alex,

ODS_TIME_DIM = 4509 records

ODS_OPERATION_CYCLE_TIME = 168837 records

We never execute WIP or ODS aging scripts.

ODS Retention settings is 9999 days

The jar files in Clients\Lib directory have the version 6.0.4.20

Best regards,

Johan

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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

[former_member182330 · Accepted solution]

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

[Former Member · Accepted solution]

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