Register Login

HANA Expensive Statement Trace Interview Question and Answer

Updated Oct 14, 2019

1. What purpose does expensive statements trace serve?

To record execution details for database requests that exceed defined thresholds, the expensive statements trace is used. When doing SQL optimization it is one of the information sources that can be used. It is recommended to activate this trace on a permanent basis due to the significant added value and the small overhead (in case of reasonable thresholds).

2. What are the common indications for problems with the expensive statements trace?

For one of the following individual checks SQL: "HANA_Configuration_MiniChecks" returns a potentially critical issue (C = 'X')

Check ID Details
620 Minimum expensive SQL trace threshold (ms)
623 Traced expensive SQL statements (last day)

3. To record a database request in the expensive statements trace what kinds of criteria can be used?

The performance indicators are available to mark a SQL statement as expensive:

Indicator Validity Parameter Unit Details
Runtime general global.ini -> [expensive_statement] -> threshold_duration us SQL statements exceeding the configured runtime limit are recorded.
CPU consumption Rev. >= 94 global.ini -> [expensive_statement] -> threshold_cpu_time us

SQL statements exceeding the configured CPU consumption are recorded.

As a prerequisite CPU time measurement must be activated with the following parameter

global.ini -> [resource_tracking] -> enable_tracking
global.ini -> [resource_tracking] -> cpu_time_measurement_mode

Memory consumption Rev. >= 94 global.ini -> [expensive_statement] -> threshold_memory byte

SQL statements exceeding the configured memory consumption are recorded.

As a prerequisite memory tracking must be activated with the following parameters

global.ini -> [resource_tracking] -> enable_tracking
global.ini -> [resource_tracking] -> memory_tracking

Read here Differences between SAP HANA and Oracle SQL

4. What are the parameters that control the expensive statements trace?

In the service specific trace files (e.g. indexserver.ini) up to SPS 06 the expensive statements trace was configured per default. The configuration has moved to global.ini starting with SPS 07.

For controlling the expensive statements trace the following main parameters exist:

Parameter Default Unit Details
global.ini -> [expensive_statement] -> application

 

 

Possibility to restrict trace to specific application name

global.ini -> [expensive_statement] -> application_user

 

 

Possibility to restrict trace to specific application user name

global.ini -> [expensive_statement] -> enable

false

 

Main switch, 'true' activates the expensive statements trace

global.ini -> [expensive_statement] -> in_memory_tracing_records

30000

records

Maximum number of trace records (per service) stored in memory

global.ini -> [expensive_statement] -> maxfiles

10

files

Maximum number of trace files

global.ini -> [expensive_statement] -> maxfilesize

1000000

bytes

Maximum file size in byte (default: 1 MB)

global.ini -> [expensive_statement] -> object

 

 

Possibility to restrict trace to specific objects (tables, views, procedures)

global.ini -> [expensive_statement] -> passport_tracelevel

 

 

Possibility to restrict trace to specific passport tracelevels (medium, high)

global.ini -> [expensive_statement] -> threshold_cpu_time

 

us

Threshold for minimum CPU utilization (Rev. >= 94)

global.ini -> [expensive_statement] -> threshold_duration

1000000

us

Threshold for minimum SQL statement duration (default: 1 s)

global.ini -> [expensive_statement] -> threshold_memory

 

byte

Threshold for minimum SQL memory allocation (Rev. >= 94)

global.ini -> [expensive_statement] -> trace_flush_interval

10

records

Interval for writing trace data from memory buffer to trace files on disk (Rev. >= 100)

global.ini -> [expensive_statement] -> trace_parameter_values

true

 

Possibility to trace bind values

global.ini -> [expensive_statement] -> use_in_memory_tracing

true

 

Possibility to use memory caching

  • true -> cache information in memory
  • false -> write data into files directly

global.ini -> [expensive_statement] -> user

 

 

Possibility to restrict trace to specific database user name

5. How can you activate and deactivate the expensive statements trace?

The following approaches can be used to activate and deactivate the expensive statements trace:

Tool

Operation 

Details

Parameter setting

Activation

global.ini -> [expensive_statement] -> enable = true

 

Deactivation

global.ini -> [expensive_statement] -> enable = false

SAP HANA Studio

Activation

Administration
-> Trace Configuration
-> Expensive Statements Trace
-> Trace Status: Active

 

Deactivation

 Administration
-> Trace Configuration
-> Expensive Statements Trace
-> Trace Status: Inactive

SAPGUI

Activation

DBACOCKPIT
-> Configuration
-> Trace Configuration
-> ExpensiveStatements
-> Flag 'Status'

 

Deactivation

DBACOCKPIT
-> Configuration
-> Trace Configuration
-> ExpensiveStatements
-> Unflag 'Status'

Read more SAP HANA LOBs Interview Question and Answer

6. Where does the result of expensive statements trace can be found?

In the following ways, the results of the expensive statements trace can be evaluated:

Tool

Details

M_EXPENSIVE_STATEMENTS

This SAP HANA monitoring view contains the collected expensive statements information and can be queried directly.

SAP HANA Studio

Administration
-> Performance
-> Expensive Statements Trace

SAPGUI

DBACOCKPIT
-> Performance
-> Expensive Statements

Additionally the following analysis SQL statements are provided in SAP  1969700:

SQL Statement

Details

SQL: "HANA_SQL_ExpensiveStatements"

Flexible access (including filters, aggregation and sorting) of expensive statements

SQL: "HANA_SQL_ExpensiveStatements_BindValues"
SQL: "HANA_SQL_ExpensiveStatements_BindValues_CommaList"

Display of bind values recorded in expensive statements trace

SQL: "HANA_Expensive_Statements_SQLText"

Display of SQL text of SQL statements recorded in expensive statements trace

7. What types of problems can exist in the area of the expensive statements trace?

When using the expensive statements trace; the following issues and limitations can be considered:

Issue

Details

Overhead in case of small thresholds

A potentially very high number of database requests are traced if the threshold parameters are set too low. Some overhead and increased resource consumption is introduced. The following are the reasonable lower limits for the parameters:

Parameter

Suggested Value Range

global.ini -> [expensive_statement] -> threshold_cpu_time

1000000 us (1 s) and higher

global.ini -> [expensive_statement] -> threshold_duration

1000000 us (1 s) and higher

global.ini -> [expensive_statement] -> threshold_memory

104857600 byte (100 MB) and higher

 

Memory leak in Pool/RowEngine/MonitorView

The heap allocator Pool/RowEngine/MonitorView can grow significantly due to a memory leak if in memory tracing is used because of the following parameter setting

global.ini -> [expensive_statement] -> use_in_memory_tracing = true

Risk of crash during startup if trace object is defined

There is a risk for a crash during startup of SAP HANA with SAP HANA Rev. 80 and below if one or more trace objects are defined with the following parameter

global.ini -> [expensive_statement] -> object

Times may be recorded more than once

Depending on the operation, in several records the execution times in the expensive statements trace may be recorded

For example, OPERATION = 'AGGREGATED_EXECUTION' also covers the times for individually recorded entries with OPERATION = 'SELECT'. This is the intended behavior. If you want to make sure that you don't count times twice, you have to restrict for specific, cumulative operations, e.g. 'AGGREGATED_EXECUTION' and 'CALL'.

8. How much can you retain trace information in the expensive statements trace?

See SAP  2088971 for more details.

Read more about SAP HANA Modeling-Overview

9. Can we permanently activate the expensive statements trace?

The performance overhead of the expensive statements trace is minimal when reasonable threshold values are chosen. The added analysis value is significant at the same time. Therefore on a permanent basis it is possible and has been recommended to activate the expensive statements trace.

10. Which columns are important in expensive statements trace?

M_EXPENSIVE_STATEMENTS is the most important columns in the monitoring view

Column

Unit

Details

STATEMENT_HASH

 

For a SQL statement the statement hash is a unique identifier and can be used for the same SQL statement from other monitoring views to retrieve performance data.

DURATION_MICROSEC

us

Duration of the particular operation

OBJECT_NAME

 

Names of the accessed tables

OPERATION

 

Type of recorded operation. The most important operations are:

Operation

Details

AGGREGATED_EXECUTION

Overall execution time of an individual database request

CALL

Execution time of procedure calls

COMPILE

Preparation / parse time

CURSOR_CLOSE

Cursor closing time

FETCH

Fetch time

SELECT
INSERT
UPDATE
DELETE

Execution time of SELECT / INSERT / UPDATE / DELETE operation

 

RECORDS

 

Number of processed records

STATEMENT_STRING

 

SQL text

PARAMETERS

 

Used bind values

ERROR_CODE
ERROR_TEXT

 

Error details in case of termination of database request

LOCK_WAIT_DURATION

us

Transactional lock wait time

MEMORY_SIZE

byte

Size of memory allocated by database request (e.g. for storing intermediate results), only populated if the following parameters are activated

global.ini -> [resource_tracking] -> enable_tracking = on
global.ini -> [resource_tracking] -> memory_tracking = on

This value is the total memory consumption across all hosts in a scale-out environment, so it can be higher than the host specific statement_memory_limit configuration

CPU_TIME

us

CPU time required for processing the statement, only populated if the following parameter is activated, otherwise -1:

global.ini -> [resource_tracking] -> cpu_time_measurement_mode

APP_USER
APPLICATION_NAME
APPLICATION_SOURCE

 

Application details (user, name and module)

NETWORK_MESSAGE_ID

 

Identifier for mapping expensive statements trace records to SQL client network I/O trace records (M_SQL_CLIENT_NETWORK_IO.MESSAGE_ID).

11. How can the top SQL statements in terms of factors like elapsed time, CPU or memory consumption be determined?

You can use SQL to identify the top SQL statements in the expensive statements trace in terms of specific key figures:

Set "HANA_SQL_ExpensiveStatements" in "Modification section" with a specific ORDER_BY setting.

ORDER_BY

Details

Column 

CPU

Sorting by CPU consumption

CPU_TIME

DURATION

Sorting by total runtime

DURATION_MICROSEC

MEMORY

Sorting by memory consumption

MEMORY_SIZE

Read here: Steps to Download and Install SAP HANA Studio


×