Expensive Statements Trace in HANA

Here , I Explained about the Expensive statements trace in HANA and the expensive statements are individual SQL statements, whose execution time exceeded a configured threshold value . we are using expensive statements trace records for the information about these statements for further analysis and It is inactive by default.

From SAP HANA 1.0 revision 80 (SPS8), you can now track and monitor the amount of memory used by statements, via the "Expensive Statements" feature of SAP HANA. Expensive statements are those SQL queries that consume the most amount of resources.

Please follow below steps for Enabling  Expensive Statements trace in HANA.

Step 1 - alter system alter configuration ('global.ini', 'system') set ('expensive_statement', 'use_in_memory_tracing') = 'false' with reconfigure;

Step 2 - alter system alter configuration ('global.ini', 'system') set ('expensive_statement', 'threshold_duration') = '1000000' with reconfigure;

Note - Threshold duration is the Threshold execution time in microseconds (default 1,000,000)

Step 3 - alter system alter configuration ('global.ini', 'system') set ('expensive_statement', 'enable') = 'true' with reconfigure;

Note - for disabling expensive statements trace in HANA Please follow below steps.

Step 1 - alter system alter configuration ('global.ini', 'system') unset ('expensive_statement', 'use_in_memory_tracing') with reconfigure;

Step 2 - alter system alter configuration ('global.ini', 'system') unset ('expensive_statement', 'threshold_duration') with reconfigure;

Step 3 - alter system alter configuration ('global.ini', 'system') unset ('expensive_statement', 'enable') with reconfigure;

Expensive Statements Monitoring from HANA Studio -

Administration editor -- Performance tab --- expensive statements.

Limiting expensive statements:

Step 1- Add the below section to the global.ini configuration file for enabling per-statement memory tracking .

[resource_tracking]
enable_tracking = on
memory_tracking = on

---OR -----Using the SQL console, you can do this as follows:

alter system alter configuration ('global.ini', 'SYSTEM') set ('resource_tracking', 'enable_tracking') = 'on' with reconfigure;

alter system alter configuration ('global.ini', 'SYSTEM') set ('resource_tracking', 'memory_tracking') = 'on' with reconfigure;

Once memory tracking is enabled, the expensive statement trace (see the SAP HANA Administration Guide for more details) will also show the peak memory size (MEMORY_SIZE) used to execute the expensive statements.

Step 2 -  In addition, it is now also possible to protect a SAP HANA system against excessive memory usage by uncontrolled queries, by limiting the amount of memory used by single statement executions per host.

You accomplish this by adding the following section to global.ini:

[memorymanager]
statement_memory_limit =
 
the numeric value, in giga bytes. Using SQL (example: 10 GB):

alter system alter configuration ('global.ini','SYSTEM') SET ('memorymanager', 'statement_memory_limit') = 10 with reconfigure;

Note - Statement executions that would require more memory than this number will be aborted when they reach the limit. By default, there is no limit on statement memory.