When the statement_memory_limit is set to XX GB does any of the expensive SQL statements gets captured ? even though 'enable_tracking' and 'memory_tracking' is ON. In our case none of the expensive SQL statements is being captured. Please advise.
Enabling resource tracking will provide additional information on captured statements.
But the decision whether a statement execution is captured or not is still primarily the runtime threshold. In addition to that you can define filters, but the runtime threshold must be exceeded to get a statement traced.
How much memory the statement used is not considered in this decision.
Thanks, as you described the statement_memory_limit is no way connected to the expensive SQL statements capturing. I tried with defaults and was able to see the expensive statements getting recorded.
Is there any alerting system I can configure to know if any statement exceeds my threshold limit in the Expensive SQL trace ? What is the impact if I leave the trace ON during peak usage hours ?
statement_memory_limit is a safety belt parameter.
The idea here is that before a single query allocates all memory and thereby interferes with other sessions on the same system, SAP HANA stops this query. This ends in a dump for the stopped query but other sessions can continue to work.
Expensive statement trace on the other side is an option to gather more insight about the queries that run on your system.
It's all about the total runtime of a statement.
When you want to monitor statements that get close to your threshold value of statement_memory_limit you can set the tracing to a smaller value in order to capture more of those statements.
Typically, due to the amount of data involved, you should be able to see a correlation between large memory usage and long runtime (note: this is not necessarily true the other way around!).
So, by catching long running statements, you may find most of the big memory using statements.
Up to my knowledge there is no automated alert available for this - but I haven't looked into this in a long time.
Concerning the tracing overhead: it affects your processes less than not knowing which statements run too long and by that being unable to address this issue.
You can and should leave the data collection of the expensive statements trace on at all time.
whenever a OOM situation is reached by a statement (cannot allocate more memory), a runtime dump file is written by HANA. The file name has the string "compositelimit_oom" in the name. This happens for example, if the statement_memory_limit threshold is exceeded.
The HANA Studio "alert tab" allows to configure alerting. It also includes a check if a new RTEdump file is written. Further, the "configure" function (and then "configure receipients .." in the dialog), allows to send an email whenever a dumpfile has been written (checkID 46). That might help for your request.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.