Table of Contents
HANA expensive statements monitoring
This monitor is dedicated to the statements recorded by the HANA expensive statements trace. On every run it reads the statements ended since the previous run, then reports the workload per host, database user, application user, application and operation, ranks the most expensive statements, and raises an alarm when a statement exceeds a duration or a memory threshold.
Note: the monitor reads the view M_EXPENSIVE_STATEMENTS. HANA only records a statement there when the expensive statements trace is enabled and the statement ran longer than the trace threshold. A statement shorter than threshold_duration is therefore never seen by this monitor, whatever its own thresholds. To watch the statements running right now, use the HANA active statements monitor.
Prerequisites
- The expensive statements trace must be enabled in
global.ini, sectionexpensive_statement:enable = true. threshold_durationof the same section is in microseconds. For example1000000records the statements running longer than 1 second.- The peak memory of a statement is only recorded when resource tracking is on in
global.ini, sectionresource_tracking:enable_tracking = onandmemory_tracking = on. Without it the memory is reported as 0 MB and a memory threshold can never be reached. - The monitoring user needs the
CATALOG READsystem privilege, included in theMONITORINGrole, to read the statements of every user and the trace settings.
Configuration hints
- Use the surveillance table to adjust the monitoring settings:
- Thresholds
- Severity
- Filters
- The statements are read once per run and shared by all surveillance lines. Each line only filters them, so adding a line does not add any load on the database.
- Each run reads the statements that ended between the start of the previous successful run and the start of this run, both rounded down to 5 minutes. A statement ending after that time is read by the next run, so no statement is missed or read twice. The first run, or a run more than one hour after the previous successful one, reads the last hour.
- A statement is selected on its end time, not its start time: HANA records a statement only when it finishes, so a statement running longer than the schedule is still collected by the run that follows its end.
Global parameters
| Parameter | Default | Description |
|---|---|---|
| Send metrics | enabled | If enabled, the workload metrics and the top statement metrics described below are sent to the configured plugins/integrations. Metrics do not depend on the surveillance table: they are sent even when the table is empty. |
| Send metadata | disabled | If enabled, the collected statements are sent as metadata to the configured plugins/integrations. It can usually be retrieved as logs or tabular data within the corresponding integrations. All collected statements are sent, not only the statements breaching a surveillance line. Each row carries the host, port, connection, statement hash, DB user, application user, application name and source, object, operation, start time, duration, CPU time, memory, lock wait, records, error code and text, and the statement text cut at 1000 characters. |
| Max statements | 500 | Defines the maximum number of statements fetched per run, the longest first. When more statements were recorded during the period, the shortest ones are dropped. Metrics, metadata and alarms are computed on the statements kept. |
| Top statements | 20 | Defines how many statements get their own metric series, tagged with the statement hash and the beginning of the statement text. The executions of the same statement hash on the same host are combined, then ranked by their total duration over the period. Set it to 0 to disable the per statement metrics. This setting caps the number of series sent to the integrations: the number of statement hashes of a database is not bounded. |
| Check trace enabled | enabled | If enabled, a WARNING alarm is raised when the expensive statements trace is disabled in global.ini. Without the trace the monitor collects nothing, and a silent monitor would look healthy. The effective value is the one of the most specific layer: HOST, then DATABASE, then SYSTEM, then DEFAULT. When the setting cannot be read, no alarm is raised and the collection continues. |
Surveillance table
| Parameter | Description |
|---|---|
| Active | Use this field to activate or deactivate a line of configuration. |
| Host | A filter to match only a given host (multi instance case). |
| DB user | A filter on the database user running the statement. |
| App user | A filter on the application user, for example the ABAP user. |
| Application | A filter on the application. It is matched against the application name, for example ABAP:ERP, and the application source, for example a report name, so either can be used. An exclusion such as !ABAP excludes the statement when either of them contains the text. |
| Object | A filter on the object accessed by the statement, for example SAPABAP1.VBAK. |
| Statement | A filter on the statement text, case insensitive. The text is matched with its spaces and line breaks collapsed into single spaces. |
| Max duration (s) | The threshold for the duration of a statement, in seconds. A statement running at least this long breaches the line. Leave at 0 to disable this criterion. |
| Max memory (MB) | The threshold for the peak memory of a statement, in MB. A statement using at least this much memory breaches the line. Leave at 0 to disable this criterion. |
| Max count | Aggregate mode only. The alarm is raised when the number of breaching statements since the previous run reaches this value. |
| Aggregate | If checked, one alarm is sent per line with the number of breaching statements. If not checked, one alarm is sent per statement hash. |
| Severity | The severity of the alarm. |
| Auto clear | If checked, the alarm is cleared automatically once its condition is no longer met. |
| Alarm tag | A text used as a prefix of the alarm message of this line. |
| Alarm | If checked, this line of surveillance will be used for alarm generation. |
Note: filters match any value containing the text, case insensitive. * matches any value and a leading ! excludes the values containing the text. To exclude several unrelated values, join them with +: !SYSTEM+!_SYS_STATISTICS. Do not use a comma for this: !SYSTEM,!_SYS_STATISTICS only excludes the first one.
Note: with both Max duration and Max memory at 0, every statement matching the filters breaches the line. This is the case of the default line: with Aggregate checked and Max count at 1, it alarms as soon as one expensive statement is recorded. Set a threshold before activating such a line on a busy system.
Note: the thresholds support the severity syntax, for example G2W:10 W2M:30 for WARNING from 10 and MAJOR from 30. The letters are G (clear), I (informational), W (warning), m (minor), M (major) and C (critical). When both criteria are set, the statement takes the highest severity of the two.
Note: in aggregate mode the severity of the alarm comes from Max count only, not from the duration and memory thresholds. Use the severity syntax on Max count to raise the severity with the number of statements.
Alarm content
- Without Aggregate, one alarm is raised per statement hash and host, using the worst execution of the period. The message contains the statement hash, the duration and the memory with their thresholds, the host, the users, the application and the beginning of the statement text:
Expensive statement 7a3f9c1d2e4b5a60 ran 45.0 s (>=30 s) using 2048 MB on hana01 by SAPABAP1 / JSMITH (ABAP:PRD): SELECT * FROM VBAK WHERE VBELN = ?
- The alarm of a statement hash stays open while that statement keeps breaching the line, and with Auto clear checked it closes on the first run that finds no breaching execution.
- With Aggregate, one alarm is raised per line. The message contains the number of breaching executions, the period, the count threshold and the filters of the line:
5 expensive statement(s) in the last 5 min (>= 3 statements) for DB user SAPABAP1
- A disabled trace raises its own WARNING alarm, independently of the surveillance table:
Expensive statements trace is disabled (global.ini [expensive_statement] enable = false), no statement can be collected. - In integrations that show the metric of an alarm, an alarm per statement is reported under
EXPENSIVE_STATEMENTS_TOP_MAX_DURATION_SEC, an aggregate alarm and the trace alarm underEXPENSIVE_STATEMENTS_COUNT.
Examples
Example 1
| Active | Host | DB user | App user | Application | Object | Statement | Max duration (s) | Max memory (MB) | Max count | Aggregate | Severity | Auto clear | Alarm tag | Alarm |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| true | * | * | * | * | * | * | 30 | 0 | 1 | false | MAJOR | true | true |
Effect: Sends a MAJOR alarm for each statement hash that ran 30 seconds or more since the previous run. The alarm closes once the statement no longer runs that long.
Example 2
| Active | Host | DB user | App user | Application | Object | Statement | Max duration (s) | Max memory (MB) | Max count | Aggregate | Severity | Auto clear | Alarm tag | Alarm |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| true | * | SAPABAP1 | * | ABAP | * | * | 0 | 4096 | G2W:3 W2M:10 | true | MAJOR | true | MEM | true |
Effect: Sends one alarm when ABAP statements of user SAPABAP1 used 4 GB of memory or more: WARNING from 3 such statements since the previous run, MAJOR from 10. The duration criterion is disabled.
Generated metrics
| metricId | metricUnit | metricTarget | metricDescription |
|---|---|---|---|
| EXPENSIVE_STATEMENTS_COUNT | Statements | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the number of expensive statements of the period. |
| EXPENSIVE_STATEMENTS_MAX_DURATION_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the longest duration of the period. |
| EXPENSIVE_STATEMENTS_AVG_DURATION_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the average duration of the period. |
| EXPENSIVE_STATEMENTS_MAX_MEMORY_MB | MB | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the highest peak memory of the period. |
| EXPENSIVE_STATEMENTS_TOTAL_CPU_TIME_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the total CPU time of the period. |
| EXPENSIVE_STATEMENTS_LOCK_WAIT_TIME_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the total lock wait time of the period. |
| EXPENSIVE_STATEMENTS_ERROR_COUNT | Statements | [HOST][USER][APP_USER][APP_NAME][OPERATION] | Sends the number of statements of the period that ended with an error. |
| EXPENSIVE_STATEMENTS_TOP_EXECUTIONS | Executions | [HOST][USER][APP_USER][APP_NAME][OPERATION][OBJECT][STATEMENT_HASH][STATEMENT] | Sends the number of executions of a top statement. |
| EXPENSIVE_STATEMENTS_TOP_MAX_DURATION_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION][OBJECT][STATEMENT_HASH][STATEMENT] | Sends the longest execution of a top statement. |
| EXPENSIVE_STATEMENTS_TOP_TOTAL_DURATION_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION][OBJECT][STATEMENT_HASH][STATEMENT] | Sends the total duration of a top statement, the value used for the ranking. |
| EXPENSIVE_STATEMENTS_TOP_MAX_MEMORY_MB | MB | [HOST][USER][APP_USER][APP_NAME][OPERATION][OBJECT][STATEMENT_HASH][STATEMENT] | Sends the highest peak memory of a top statement. |
| EXPENSIVE_STATEMENTS_TOP_CPU_TIME_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][OPERATION][OBJECT][STATEMENT_HASH][STATEMENT] | Sends the total CPU time of a top statement. |
| EXPENSIVE_STATEMENTS_TOP_RECORDS | Records | [HOST][USER][APP_USER][APP_NAME][OPERATION][OBJECT][STATEMENT_HASH][STATEMENT] | Sends the total number of records of a top statement. |
Note: values in MB are rounded to whole megabytes.
Note: the period is the time since the previous run. A combination of host, users, application and operation is only sent when at least one statement of the period has it: a quiet period sends nothing rather than zero.
Note: a target without a value, for example the application user of a statement run by a technical user, is left out of the series rather than sent empty.
Note: the STATEMENT target holds the first 60 characters of the statement text, with spaces and line breaks collapsed. It is meant for humans reading a dashboard, the STATEMENT_HASH target identifies the statement.
