Collector for HANA Expensive Statements

The technical name of the Collector for HANA Expensive Statements is /DVD/MON_HDB_CL_COL_SQL_STMT.

This collector collects information about HANA expensive statements that exceed the duration or the memory size limit. Details for expensive statements can be collected in two modes:

  • Aggregation mode (default): Detail records are aggregated into distinct expensive statements. This means that the same expensive statements are reported as one record in the detail table.

  • Non-aggregation mode: Detail records are not aggregated. The top n statements according to the largest durations and memory sizes are reported in the detail table.

Before monitoring HANA expensive statements, you must set up the HANA configuration file properly. Otherwise, you might experience the following error messages when monitoring HANA expensive statements. Make sure that all the steps in the HANA configuration file are complete.

Default KPIs Delivered with This Collector

The following default KPIs are delivered with this collector:

KPI name

Description

Unit

Detail table

HDB_SQL_EXP_STMT_DUR_NUM

Number of expensive SQL statements exceeding the time limit

Count

Yes

HDB_SQL_EXP_STMT_NUM

Number of expensive SQL statements exceeding the memory limit

Count

Yes

Parameters

The following parameters are used for this collector:

Parameter name

Description

Default value

AGGREGATE_STATEMENTS

Defines whether the expensive statements are aggregated to the detail table.

X

CHECK_ONLY_APP_STMT

If set to X, only expensive statements triggered by any application are processed. Otherwise, the application-related fields are ignored. Note that the application fields APP_USER and APPLICATION_SOURCE are not empty in the table M_EXPENSIVE_STATEMENTS. This table provides all statements with a duration longer than the specified threshold.

X

MEMORY_SIZE_LIMIT_IN_MB

Defines the memory size threshold in MB. Only statements that exceed this memory size threshold are processed.

500

TIME_DURATION_LIMIT_IN_MS

Defines the duration threshold in milliseconds. Only statements that exceed this threshold value are processed.

100

TOP_RECORDS

Defines the maximum number of records that can be reported in the detail table.

100

Detail Table

The collector provides the detail table HANA expensive statements. The technical name of the detail table is /DVD/MON_HDB_SQL_EXP_STMT.

The detail table HANA expensive statements provides a list of statements that exceed the duration or memory size thresholds based on the selected KPI. The collector parameter AGGREGATE_STATEMENTS defines whether expensive statements are reported in the detail tables in aggregation mode or non-aggregation mode.

Aggregation mode: If the records are aggregated, the same statements are reported as one record, and this record contains some additional details:

  • Start time of the first statement execution

  • Average duration time of the same executed statements since the last collector execution

  • Average memory size of the same executed statements since the last collector execution

  • Number of the same statement executions since the last collector execution

Non-aggregation mode: If the records are not aggregated, each record in the detail table represents one executed statement with the following details:

  • Start time when the specific statement was executed

  • Average duration time: The total time of this statement execution

  • Average memory size: The total memory size of this statement execution

  • Number of statement executions: This value is always 1 (one execution)

It contains the following fields:

Technical name

Description

TIMESTAMP

Time when the records are saved to the detail table

SID

System ID

START_TIME

Statement start time

DB_USER

DB username for the statement

APP_USER

Application username for the statement

APPLICATION_SOURCE

Application source call of the statement

MEMORY_SIZE

Average peak memory usage across all involved hosts

DURATION_MICROSEC

Average statement duration in microseconds

OBJECT_NAME

Related object names of the statement

STATEMENT_STRING

Statement string

STATEMENT_HASH

Unique identifier for the statement

STMNT_COUNT

Number of statement executions

KPI

KPI name for which the detail record was reported

Setting up the HANA Configuration File

Step 1:

Enable expensive statement monitoring

  1. Start transaction DB02.

  2. Click Configuration > INI files.

  3. Expand the node INIFILE PARAMETER LIST.

  4. Navigate to the following path: Global.ini > expensive_statement > enable.

  5. Double-click the node enable.

  6. Set the value to true.

Step 2:

Set a duration threshold for the expensive statement monitoring

  1. Start transaction DB02.

  2. Click Configuration > INI files.

  3. Expand the node INIFILE PARAMETER LIST.

  4. Navigate to the following path: Global.ini > expensive_statement > threshold_duration.

  5. Double-click the node threshold_duration.

  6. Set a valid system value that is greater than zero.

Step 3:

Enable resource tracking for the monitoring

  1. Start transaction DB02.

  2. Click Configuration > INI files.

  3. Expand the node INIFILE PARAMETER LIST.

  4. Navigate to the following path: Global.ini > resource_tracking > enable_tracking.

  5. Double-click the node enable_tracking.

  6. Set the system value to on.

Step 4:

Enable memory tracking for the monitoring

  1. Start transaction DB02.

  2. Click Configuration > INI files.

  3. Expand the node INIFILE PARAMETER LIST.

  4. Navigate to the following path: Global.ini > resource_tracking > memory_tracking.

  5. Double-click the node memory_tracking.

  6. Set the system value to on.

Memory Threshold for Expensive Statement

Since the 2211 release, it is not mandatory to set this configuration parameter for HANA expensive statements monitoring. However, when this configuration parameter is not set, the Collector for HANA Expensive Statements processes all the reported expensive statements and compares their memory sizes with the collector parameter MEMORY_SIZE_LIMIT_IN_MB. If there are a lot of expensive statements of all memory sizes reported on HANA, this can decrease the performance of the collector.

Proceed as follows to set a memory threshold for the expensive statement monitoring:

  1. Start transaction DB02.

  2. Click Configuration > INI files.

  3. Expand the node INIFILE PARAMETER LIST.

  4. Navigate to the following path: Global.ini > expensive_statement > threshold_memory.

  5. Double-click the node threshold_memory.

  6. Set a valid system value that is greater than zero.