[스크랩] Setting a Memory Limit for SQL Statements

help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/1.0.12/en-US/3f9dfb00-5a9a-4164-8d68-57330ee57b47.html

Setting a Memory Limit for SQL Statements

Setting a Memory Limit for SQL Statements

The statement memory limit allows you to set a limit both per statement and per SAP HANA host.

Prerequisites

To apply these settings you must have the system privilege INIFILE ADMIN.

Context

You can protect an SAP HANA system from uncontrolled queries consuming excessive memory by limiting the amount of memory used by single statement executions per host. By default, there is no limit set on statement memory usage but if a limit is applied statement executions that require more memory will be aborted when they reach the limit. To avoid canceling statements unnecessarily you can also apply a percentage threshold value which considers the current statement allocation as a proportion of the global memory currently available. Using this parameter, statements which have exceeded the hard-coded limit may still be executed if the memory allocated for the statement is within the percentage threshold.

You can also create exceptions to these limits for individual users (for example, to ensure an administrator is not prevented from doing a backup) by setting a different statement memory limit for each individual.

This limit only applies to single statements, not the system as a whole. Tables which require much more memory than the limit applied here may be loaded into memory.

You can view the (peak) memory consumption of a statement in M_EXPENSIVE_STATEMENTS.MEMORY_SIZE.

Note that M_EXPENSIVE_STATEMENTS.REUSED_MEMORY_SIZE is not used as of SPS 09.

For these options enable_tracking and memory_tracking must first be enabled in the global.ini file. Additionally, resource_tracking must be enabled in this file if you wish to apply different settings for individual users.

Procedure

1. Enable statement memory tracking.

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

   • enable_tracking = on
   • memory_tracking = on
2. In the global.ini file, expand the memorymanager section and set the parameter statement_memory_limit. Set a statement memory limit in GB (integer values only) with a value between 1 and some fraction of the global allocation limit.

   NoteValues 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).

   Statements that exceed the limit you have set on a host are stopped by running out of memory.

3. In the global.ini file, expand the memorymanager section and set the parameter statement_memory_limit_threshold as a percentage of the global allocation limit (global_allocation_limit).

   This parameter provides a means of controlling when the statement_memory_limit is applied. If this parameter is set, when a statement is issued the system will determine if the amount of memory it consumes exceeds the defined percentage value of the the overall global_allocation_limit parameter setting.

   This is a way of determining if a particular statement consumes an inordinate amount of memory compared to the overall system memory available. If so, to preserve memory for other tasks, the statement memory limit is applied and the statement fails with an exception.

4. To set a user-specific statement limit and exclude a user from the global limit use the ALTER USER statement as shown here:

   ALTER USER <user_name> SET PARAMETER STATEMENT MEMORY LIMIT = <gb>

   • If both a global and a user statement memory limit are set, the user-specific limit takes precedence, regardless of whether it is higher or lower than the global statement memory limit.
   • If the user-specific statement memory limit is removed the global limit takes effect for the user.
   • The value of the parameter is shown in USER_PARAMETERS (like all other user parameters)

   NoteSetting the statement memory limit to 0 will disable any statement memory limit for the user, or, to reset a user-specific limit use the CLEAR option:ALTER USER <user_name> CLEAR PARAMETER STATEMENT MEMORY LIMIT

Results

The following example and scenarios show the effect of applying these settings:

Table 11: Table 18: Example showing statement memory parametersParameterValue

Physical memory	128 GB
global_allocation_limit	Default: 90% of the first 64 GB of available physical memory on the host plus 97% of each further GB; or, in the case of small physical memory, physical memory minus 1 GB.
statement_memory_limit	1 GB
statement_memory_limit_threshold	60%

Scenario 1:

A statement allocates 2GB of memory and the current used memory size in SAP HANA is 50GB.

• 0,9 * 128GB = 115,2 (global allocation limit)

• 0,6 * 115,2 = 69,12 (threshold in GB)

• 50 GB < 69,12 GB (threshold not reached)

The statement is executed, even though it exceeds the 1GB statement_memory_limit

Scenario 2:

A statement allocates 2GB and the current used memory size in SAP HANA is 70GB

• 70 GB > 69,12 GB (threshold is exceeded)

The statement is cancelled, as the threshold is exceeded, the statement_memory_limit is applied.