How to identify the SQL statement text from compositelimit oom trace

[sapfun08]

The compositelimit out of memory trace

In SAP HANA there is a way to limit the memory that can be allocated by a single SQL statement (1999997 - FAQ: SAP HANA Memory):

1. Starting with SAP HANA 1.0 SPS 08: As a prerequisite you need to have the statement memory tracking feature enabled ( global.ini -> [resource_tracking] -> enable_tracking = on | global.ini -> [resource_tracking] -> memory_tracking = on ). Additionally you have to set the following parameter in order to define the maximum permitted memory allocation per SQL statement and host:global.ini -> [memorymanager] -> statement_memory_limit = <maximum_memory_allocation_in_gb>


2. Starting with SAP HANA 2.0 SPS 00 you can define the amount of allocated memory per host for all concurrent database requests:global.ini -> [memorymanager] -> total_statement_memory_limit = <maximum_memory_allocation_in_gb>

After setting the parameter, you may receive a trace like this: <service>_<host>.<port>.rtedump.<timestamp>.compositelimit_oom.trc, which indicates an out of memory situation for a single SQL query.

Since we cannot be always certain from only looking at the trace file, which statement of ours triggered it, we have to do further checks to gather the statement text.

First steps

When opening the compositelimit out of memory trace, the first data we have to note is the "Runtime dump time: 2020/09/09 09:09:09". We will make good use for this later.

In the table of contents you can usually find the following three entries:

• [BUILD] Build information


• [MEMORY_MANAGER] Information about memory manager


• [MEMORY_LIMIT_VIOLATION] Information about current memory composite-limit violation

We have to go to the section [MEMORY_LIMIT_VIOLATION] to gather further details about the out of memory situation. The following can be seen in the trace:

"[MEMORY_LIMIT_VIOLATION] Information about current memory composite-limit violation: (YYYY/MM/DD HH24:MM:SS 123 Local)
Composite limit violation (OUT OF MEMORY) occurred.
Composite limit=500gb (530000000000b)
Root allocator name=Connection/123456/Statement/0123456789101112
..."

We have to note the highlighted part, which tells us the connection id, which is in this example '123456', and the statement id = '0123456789101112'.

With these three piece of data, we can move on to the next phase of our procedure.

Using the SQL Statement Collection

For the next part we will be using statements from SAP Note 1969700 - SQL Statement Collection for SAP HANA.
Do not forget to always use the statement which is appropriate for your HANA version.

(In case of errors with the statements from the note, we can check for the exact error in the table of SAP Note 1969700 - SQL Statement Collection for SAP HANA.)

Collecting the statement hash

Once we download the SQL statement collection, we have to search for the statement: "HANA_Threads_ThreadSamples_FilterAndAggregation". Depending on our HANA revision, we have to open the correct one. With the help of this statement, we will be able to filter the thread sample histories.

We have to go to the "/* Modification section */" of the statement, where we have to alter the corresponding parameters to get the result we seek:

• 'MIN' BEGIN_TIME ->'2020/09/09 09:09:00' BEGIN TIME


• 'MAX' END_TIME -> '2020/09/09 09:15:09' END_TIME
  (Based on our example, "Runtime dump time: 2020/09/09 09:09:09")
  


• '%' STATEMENT_ID -> '0123456789101112'  STATEMENT_ID


• -1 CONN_ID -> '123456' CONN_ID
  (Based on our example: "Connection/123456/Statement/0123456789101112")

After we set the new parameters, we can execute the statement, to receive the results.

Example output of HANA_Threads_ThreadSamples_FilterAndAggregation statement

We have to search for the statement which started at the noted timestamp. The STATEMENT_HASH column is important for us for the final part of the process. Do not forget to note it. In this example we can see the following statement hash: '3x4mp13st4t3m3nth4shf0rqu3ry4n4ly'.
The exact full statement text cannot be seen form the results, so we have to continue searching for it.

Gathering the statement text

The SQL statement collection offers a statement to gather a collection of details for a specific SQL statement. We will make use of this now.

After opening the corresponding "HANA_SQL_StatementHash_DataCollector" statement for our SAP HANA version, our next stop is the "/* Modification section */" again.

The BEGIN_TIME and END_TIME parameters can be altered to the same values we set earlier for the "HANA_Threads_ThreadSamples_FilterAndAggregation" statement.

Our latest find should also be used, instead of the given 'e1df1b69227985b845d4f3f8135c6688' STATEMENT_HASH, we can insert the statement hash we identified: '3x4mp13st4t3m3nth4shf0rqu3ry4n4ly' STATEMENT_HASH.

The only thing left for us is to execute the statement to get the issues SQL statement, which caused the compositelimit out of memory trace. The full statement text can be found at the result's "STATEMENT TEXT" section, as shown below.

Example output for Statement Text

Follow up actions

Once we identify the issued statement, the next step can be to check SAP KBA 2000002 - FAQ: SAP HANA SQL Optimization, in order to avoid receiving further compositelimit out of memory dumps.

To understand the execution of a SQL statement in detail, there is an option to generate a PlanViz file. We can collect a PlanViz of the specific query as per SAP KBA 2073964 - Create & Export PlanViz in HANA Studio.

Further options exist apart from the PlanViz file generation. We can check them in SAP KBA 2000002 - FAQ: SAP HANA SQL Optimization - answer 9.

Step by Step Instructions

This section is added to sum up the procedure for those, who are already familiar with the process and wish to apply it again quickly at a later time.

1. Collect the connection id, the statement id, and the timestamp from the "...compositelimit_oom.trc" trace


2. Execute HANA_Threads_ThreadSamples_FilterAndAggregation
   -> enter the timestamp you wish to check (in the modification section): YYYY/MM/DD HH24:MM:SS (from the comp oom dump)
   -> enter the statement id (in the modification section): '0123456789101112'  (from the comp oom dump) OR the thread_id and conn_id
   -> execute it
   -> in the Result tab, find and note the statement hash of the query


3. Execute HANA_SQL_StatementHash_DataCollector
   -> enter the statement hash (in the modification section): '3x4mp13st4t3m3nth4shf0rqu3ry4n4ly'
   -> enter the begin and end times to filter it based on the timestamp from earlier (in the modification section)

Hope it was helpful!

Please leave a comment or suggestion!

[former_member829220]

Hello sapfun08 Good blog, good information, but I have a doubt about how to increase the composite limit size?

We are receiving the same error but in our case the composite limit is = 30 GB

this is relationated to statement_memory_limit or total_statement_memory_limit parameters?

thank you in advance for your response.

Best regards.

[sapfun08]

Hello Luis,

If you can see in the trace that your composite limit is = 30GB, it means that your statement_memory_limit is set to 30 GB.

In some rare cases, you will see that your statement_memory_limit is higher than what is shown in the trace as composite limit.
In this case, most probably you have created a workload class, and assigned a specific statement memory limit to it (which overrides the basic settings).

The recommended value for statement_memory_limit should be somewhere between 10-30% of the global allocation limit. I advise you to start from 10% and raise it by experience if needed. But please read the related note about statement_memory_limit, before acting: 2222250 - FAQ: SAP HANA Workload Management, answer 4.

If you have the recommended settings and still get the dump, you have to check the statement, why it consumes so much memory.

I hope this answers your questions.

Best Regards,
Anna

[former_member829220]

Hi Anna!! thank you for your answer, it was very helpfull for us, but in this case, the statement that consumes the composite limit of 30 gb shown in the trace is a process relationated with sys_statystics user, I have read your recommended note but when we try to modify the workload class named Sys_statistics_user to 50 GB instead of 30 we are receiving an error that says " invalid argument: statement-level properties and application-level properties cannot be configured simultaneously"

 

We are executing the following query: ALTER WORKLOAD CLASS "_SAP_SYS_STATISTICS_USER" SET 'STATEMENT MEMORY LIMIT'='50';

I hope you can help us with any comment about this.

 

Thanks in advance

Best regards.

Enrique.