(DI-2311) Collector for HANA Expensive statements

The technical name of the Collector for Expensive statements monitoring is /DVD/MON_HDB_CL_COL_SQL_STMT.

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

In this section:

  1. Aggregation mode (by default)

    1. Detail records are aggregated into distinct expensive statements. It means that the same expensive statements are reported as one record in the detail table.

  2. Non-aggregation mode

    1. Detail records are not aggregated, but top n statements according to their largest durations and memory sizes are reported in the detail table.

Before the monitoring of HANA expensive statements, the HANA configuration file needs to be set up properly. Otherwise, you might experience the following Error messages when monitoring HANA Expensive statements. Please make sure that all steps in the HANA Configuration file are set.

Default KPIs delivered with this collector

The following default KPIs are delivered with this collector:

KPI name

Description

Unit

Detail table

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 statements exceeding the memory limit

Count

Yes

Parameters

The following parameters are used in this collector:

Parameter name

Description

Default value

Parameter name

Description

Default value

AGGREGATE_STATEMENTS

Should be the expensive statements aggregated or not into detail table?

X

CHECK_ONLY_APP_STMT

If set to 'X’, only expensive statements triggered by any application are processed (application fields APP_USER and APPLICATION_SOURCE are not empty in M_EXPENSIVE_STATEMENTS). Otherwise the application related fields are ignored.

X

MEMORY_SIZE_LIMIT_IN_MB

Defines the memory size threshold in MB. Only statements which are exceeding this memory size threshold are processed.

500

TIME_DURATION_LIMIT_IN_MS

Defines the duration threshold in milliseconds. Only statements which are exceeding this threshold value are processed.

100

TOP_RECORDS

Defines how many maximum records can be reported into detail table.

100

Detail table

The collector provides a detail table of HANA expensive statements. The technical name of the detail table is /DVD/MON_HDB_SQL_EXP_STMT.
HANA expensive statements provide a list of statements which are exceeding the duration or memory size thresholds (based on selected KPI). Collector’s parameter AGGREGATE_STATEMENTS defines whether expensive statements are reported into detail tables in aggregation or non-aggregation mode.

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

    • the start time of the first statement execution

    • average duration time of the same executed statements since the last run of collector’s execution

    • average memory size of the same executed statements since the last run of collector’s execution

    • number of the same statement executions since the last run of collector’s execution

  • 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 is the total time of this statement execution

    • average memory size is the total memory size of this statement execution

    • number of statement executions is always 1 (one execution)

Technical name

Description

Technical name

Description

TIMESTAMP

Time when the records are saved into the detail table

SID

System ID

START_TIME

Statement Start time

DB_USER

DB user name for the statement

APP_USER

Application user name 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 microsecond

OBJECT_NAME

Related object names of the statement

STATEMENT_STRING

Statement string

STATEMENT_HASH

The unique identifier for SQL statement

STMNT_COUNT

Number of Statement executions

KPI

KPI name for which the detail record was reported.