Memory allocation

[matma24]

Hello,

In my system i have the limit of memory set as 150GB.

There is an procedure that actually returns error due to not being able to allocate enough memory.

I've found that if i ran this procedure in a debug mode then everything works fine, but when I try to run it as a standard procedure or function (for MAP_REDUCE) then I'm getting those errors, the same thing happens when I ran it as an anonymous block - if it's ran in debug mode then everything is fine, otherwise not.

As my understanding goes that can be caused by a parallel execution if there is an assignment, e.g. https://developers.sap.com/tutorials/xsa-sqlscript-parallel.html

short example:

lt_test = SELECT * FROM test_table;


lt_query2 = SELECT * FROM test_query q
JOIN :lt_test t 
ON t.id = q.id;

I'd like to ask if anyone could confirm that and it is caused because in debug mode it's ran in steps, where in normal mode it's ran in parallel.

Is there anything I can do with it?

BR,

M

[pfefferf · Accepted solution]

For test purposes you can add the statement "SEQUENTIAL EXECUTION" to your procedure definition (check the docu here). With that you can verify if the OOM dump occurs, because of parallel execution and resulting increased memory load.

If the procedure is not time critical you probably can use the statement for production execution; but I would recommend to analyze your OOM dump in detail, create an execution plan and a PlanViz analysis to get an overview about the execution and memory consumption blocks. Based on the analysis you potentially see points to improve.

[matma24 · Accepted solution]

You are right - everything seems to be working just fine after adding "SEQUENTIAL EXECUTION", before 150GB was not enough, now it's taking less than 65.

Shouldn't it be marked in the documentation that "PARALLEL EXECUTION" is the default mode and if nothing will be specified it will be used?

Nevertheless I'm taking your answer because everything seems to be working. Thank you a lot!