3211034 - Recommended value for parameter statement_memory_limit

Recommended value for statement_memory_limit, which defines the maximum memory allocation per statement in GB.

statement_memory_limit
When statement_memory_limit is set as 0, this means there is no memory limitation on single SQL statement.
When statement_memory_limit is set as -1, (As of SAP HANA database 2.0 SPS06) SAP HANA will calculate the statement_memory_limit as 25% of the smaller of global_allocation_limit and process_allocation_limit. 

1. 1. To modify this parameter, the system privilege INIFILE ADMIN is needed.

      And enable_tracking and memory_tracking must first be enabled in the global.ini file . Additionally, resource_tracking must be enabled in this file to apply different settings for individual users.

   2. In the global.ini file, expand the resource_tracking section and set the following parameters to on:

      • enable_tracking = on
        
        
      • memory_tracking = on
        
        
      • By setting memory_tracking = off, statement_memory_limit will no longer work.
        
        
   3. statement_memory_limit - defines the maximum memory allocation per statement in GB. The parameter is not set by default.

      • In the global.ini file, expand the memorymanager section and locate the parameter. Set an integer value in GB between 0 (no limit) and the value of the global allocation limit. Values that are too small can block the system from performing critical tasks.

      • When the statement memory limit is reached, a dump file is created with 'compositelimit_oom' in the name. The statement is aborted, but otherwise the system is not affected. By default only one dump file is written every 24 hours. If a second limit hits in that interval, no dump file is written. The interval can be configured in the memorymanager section of the global.ini file using the oom_dump_time_delta parameter, which sets the minimum time difference (in seconds) between two dumps of the same kind (and the same process).

      • The value defined for this parameter can be overridden by the corresponding workload class property STATEMENT_MEMORY_LIMIT.

      • Parameter statement_memory_limit is respected only if total used memory exceeds the global allocation limit set by statement_memory_limit_threshold. The default value of statement_memory_limit_threshold is 0% (of the global_allocation_limit) so statement_memory_limit is always respected. 
        
        
      After setting this parameter, single statements that exceed the limit set on a host are stopped by running out of memory.

• SAP HANA database 1.0 SPS8 or newer
• SAP HANA database 2.0

statement_memory_limit; GENERIC_COMPOSITE_LIMIT; STATEMENT_MEMORY_LIMIT_FROM_GLOBAL_CONFIG  , KBA , HAN-DB-PERF , SAP HANA Database Performance , HAN-DB , SAP HANA Database , Problem