(DI-2302) 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:
Aggregation mode (by default)
Detail records are aggregated into distinct expensive statements. It means that the same expensive statements are reported as one record in the detail table.
Non-aggregation mode
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 |
|---|---|---|---|
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 |
|---|---|---|
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 |
|---|---|
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. |