By default , single SAP HANA query can consume 100% of available memory. If you want to
safeguard your system from uncontrolled expensive queries then it’s a good idea
to limit the memory consumption of single statement per host.
Starting HANA SP08
SAP introduced parameter statement_memory_limit to restrict the query at
predefined memory consumption limit.
Queries will be aborted with OOM dump ("compositelimit_oom OR
[MEMORY_LIMIT_VIOLATION] ") , if it
reaches the limit specified by this parameter - statement_memory_limit .
Requirement: You
want to limit query from consuming limitless HANA memory.
Prerequisite: You need database user with INIFILE ADMIN
system privilege assigned.You also need statement memory tracking feature
enabled. You need to change following parameters , these parameters do not need
restart.
global.ini >
[resource_tracking] > enable_tracking = on
global.ini >
[resource_tracking] > memory_tracking = on
This will populate
the MEMORY_SIZE column of expensive statement trace( M_EXPENSIVE_STATEMENTS)
Step:
- Connect to HANA system and open SQL console in HANA studio
- Execute following SQL
OR change following parameter.
Administration
perspective > Configuration > global.ini
> [memorymanager] -> statement_memory_limit =
<maximum_memory_allocation_in_gb>
If you want to
disable this parameter , simply remove the parameter.
Starting SP09 we got
flexibility this parameter in accordance of percentage of global allocation
limit. Below parameter make sure that
the statement_memory_limit only takes effect if the overall SAP HANA memory
allocation exceeds a defined percentage of the global allocation limit:
global.ini >
[memorymanager] > statement_memory_limit_threshold =
<percentage_of_global_allocation_limit>
Reference :