ODS transfer problem

[Former Member]

I noticed that we have NC data ODS transfer problem.

Fos some reason we have in ODS_TEMP_NC_DATA and ODS_TEMP_NC_DATA_REF_DES >350 000 rows.

Now when manually running the NC script it fails to java memory error.

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded

In ClientEnvironment.bat we have set the max. memory 2048.

I assume that we have too much data now in TEMP -tables?

I have tried to run the .bat file with only expansion part but no help:

@echo off
title ODS NonConformance
if not exist ClientEnvironment.bat goto noEnv
call ClientEnvironment

:loop
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSNCDataExtract %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSNCDataExpansion %*
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSNCDataSummary %*
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSDPMOExtract %*
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSDPMOExpansion %*
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSDPMOSummary %*
rem uncomment the two lines below to keep running until extract_date_time is greater than current time - 1 hour
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSCheckDate NC_DATA
if %ERRORLEVEL% EQU 1  goto loop

goto end
:noEnv
echo ERROR: Client Environment file "ClientEnvironment.bat" not found.
:end
pause

What can we do?

Br,

Pete

[Former Member]

Pete

Are you passing the batch job a number of minutes to process in each loop? If not, it will try to process the entire set.

[Former Member]

Hi,

I used 120 minutes.

Actually I have the same problem in 2 servers.

Other one runs forever with 5 minutes and the other one fails to this out of memory error in 2 minutes...

edit: It actually fails even with 1 minute

[Former Member]

The other one that does not go to out of memory -error looks in log like this:

Thu Sep 04 21:37:55 CST 2014 ODSNCDataExpansion: Started

Thu Sep 04 21:45:37 CST 2014 ODSNCDataExpansion: Inserted 55315 records in to ODS_NC_DATA with 0 errors

Thu Sep 04 22:47:14 CST 2014 ODSNCDataExpansion: Started

Thu Sep 04 22:54:51 CST 2014 ODSNCDataExpansion: Inserted 55315 records in to ODS_NC_DATA with 0 errors

I just try to expand the data. It inserts this 55315 records to NC_DATA but it will not add anything to NC_REF_DES even after 45minutes...

And the other one just fails almost immediately after starting the script.

[Former Member]

Actually, I think the Expansion ignores the passed time range, and it only applies to the initial extract and summaries. You can confirm this by turning on SQL trace.

What you need to do depends on your WIP data. If SFCs have been archived in this time range, their NC_DATA will be gone from WIP.

If WIP still has the data, you will have to truncate the temp table - uncomment the Extract and Summary in teh bat file (you don't need the DPMO jobs at all unless you are using that functionality). set the extract_date_time back to the oldest extract_date_time for the NC_DATA table in ods_extract_date_time and then rerun the batch job, in a loop, for 120 mins.

[Former Member]

Can you turn on SQL trace?

Edit ODS.properties and add:

sqltrace.enabled=true

sqltrace.threshold=0

and rerun the batch job

[Former Member]

Hi,

The data in both cases is still in WIP.

I left the batch job running on the other server and now it seems to go ok. From log file we can see that for some reason the first batch was huge compared to the next ones and that is why it took so long time:

Thu Sep 04 22:47:14 CST 2014 ODSNCDataExpansion: Started

Thu Sep 04 22:54:51 CST 2014 ODSNCDataExpansion: Inserted 55315 records in to ODS_NC_DATA with 0 errors

Fri Sep 05 00:59:02 CST 2014 ODSNCDataExpansion: Inserted 102637 records in to ODS_NC_DATA_REF_DES with 0 errors

Fri Sep 05 00:59:02 CST 2014 ODSNCDataExpansion: Completed

Fri Sep 05 00:59:06 CST 2014 ODSNCDataExpansion: Started

Fri Sep 05 00:59:13 CST 2014 ODSNCDataExpansion: Inserted 348 records in to ODS_NC_DATA with 0 errors

Fri Sep 05 00:59:55 CST 2014 ODSNCDataExpansion: Inserted 348 records in to ODS_NC_DATA_REF_DES with 0 errors

Fri Sep 05 00:59:55 CST 2014 ODSNCDataExpansion: Completed

Fri Sep 05 00:59:58 CST 2014 ODSNCDataExpansion: Started

Fri Sep 05 01:00:18 CST 2014 ODSNCDataExpansion: Inserted 2268 records in to ODS_NC_DATA with 0 errors

Fri Sep 05 01:04:08 CST 2014 ODSNCDataExpansion: Inserted 3072 records in to ODS_NC_DATA_REF_DES with 0 errors

Fri Sep 05 01:04:08 CST 2014 ODSNCDataExpansion: Completed

Fri Sep 05 01:04:10 CST 2014 ODSNCDataExpansion: Started

Fri Sep 05 01:04:22 CST 2014 ODSNCDataExpansion: Inserted 1344 records in to ODS_NC_DATA with 0 errors

Fri Sep 05 01:06:15 CST 2014 ODSNCDataExpansion: Inserted 2208 records in to ODS_NC_DATA_REF_DES with 0 errors

Fri Sep 05 01:06:15 CST 2014 ODSNCDataExpansion: Completed

Now it has been running all night  ~14hours and it has processed more than 100 000 rows. It is still  running and in there is ~100 000 rows to be processed in TEMP -tables. When we get these processed I will put the script running in 1-2 hour chunks. I have a fear that the processing of 1 hour data takes more than 1 hour... But let's see.

The other server we need to find time for restarting everything to see if it helps. Because like I wrote the transfer will fail in gew minutes to out of memory error.

[Former Member]

After truncating the TEMP -tables and changing the extract -date the transfer started to work.

However running the NC transfer takes too long.

Running the script with 10 minutes chunks takes easily 20-40 minutes...

And this is only NC data. We need to transfer production_log as well.

Anything we can do to get better performance?

[Former Member]

1. Stop the batch job

2. Turn on SQL trace for just slow SQL by editing the ODS.properties file and adding :

sqltrace.enabled=true

sqltrace.threshold=250

(value of 250ms is abitrary really, and you can revise it if too much/not enough SQL is traced)

3. restart the job

4. Post any slow SQL here - it will appear in the usual log file

Probably we can add temporary indexes to help, also removing/disabling indexes on the destination table (especially custom ones added for custom reports) will speed up inserts. But this table set receives updates, so some core indexes are still needed and you can't just disable those without analysis.

[Former Member]

That one I can see directly from ODS DB activity monitor.

There is this update that takes forever:

ODS_NC_DATA_REF_DES SET ODS_MODIFIED_DATE_TIME = @P0 ,REF_DES = @P1 ,NC_DATA_BO = @P2 ,HANDLE = @P3 ,COMPONENT_REVISION = @P4 ,COMPONENT = @P5  WHERE HANDLE = @P6                                                        

[Former Member]

Right - that will kill the performance completely.

Looks like there's no core index on the handle of this table. You should get your DBA to add one manually and open an incident to report it. This should be a unique index.

[Former Member]

I got response that Handle is not Unique. Added that anyway.

This is the case with NC_DATA -table as well. Ther are SAP added indexes that are non-unique (handle+some other indexes).

[Former Member]

Indexing really helped. I had the job running for over 2hours withouth finish. Now with index it took like 10 minutes. I still need to keep monitoring if this totally solves this issue.

[Former Member]

Really, the ods_nc_data handle should be unique too. It will mean you've got duplicate records in these tables now somehow, so you should look at these tables and eliminate the duplicates.

[Former Member]

Hi,

We did some investigation and found a lot of duplicate handles from ODS_NC_DATA

I checked in several environments and found this working at least in one environment with no duplicates. The only difference I found is that for some reason the duplicate handles the sequence number is always 1.

So for some reason the sequence number does not always get updated and the row with not updated sequence will come as duplicate.

Some statistics:

One environment I found ~80 000 duplicates out of ~2 million rows

the other 2 duplicates out of ~2,8 million rows

and one 0 duplicates in ~ 80 000 rows

and one 120 000 duplicate from ~11 million rows

Any idea what can be the root cause for this? This sequence comes from WIP and is not updated there.