====== HANA active statements monitoring ====== This monitor is dedicated to the statements executing on the HANA database right now. On every run it lists the running statements with the database user, the application user, the application and the client host behind each of them, reports the workload, sends the metrics of the statements you select, and raises an alarm when a statement runs too long or holds too much memory. **Note:** the monitor takes a snapshot of ''M_ACTIVE_STATEMENTS'' at each run, joined with ''M_CONNECTIONS'' and ''M_SESSION_CONTEXT'' for the users and the client. A statement that starts and ends between two runs is never seen. To analyse finished statements, use the HANA expensive statements monitor. ===== Prerequisites ===== * The monitoring user needs the ''CATALOG READ'' system privilege, included in the ''MONITORING'' role. ''DATA ADMIN'' is accepted as well. * Without it HANA only shows the statements of the monitoring user's own session: the monitor then runs without error but sees nothing. See **Check privilege** below. ===== 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. * The running time of a statement is the time since the start of its current execution. Its memory is the memory currently allocated to it, in MB. * Internal statements of HANA and the statement of the monitor itself are left out. ===== Global parameters ===== ^ Parameter ^ Default ^ Description ^ | Send workload metrics | enabled | If enabled, the workload metrics described below are sent to the configured plugins/integrations: number of running statements, longest running time and memory, per host, DB user and application. \\ They do not depend on the surveillance table: they are sent even when the table is empty. \\ The per statement metrics do not depend on this setting. They are driven by the **Metric** column of the surveillance table. | | Send metadata | disabled | If enabled, the statements breaching a surveillance line are sent as metadata to the configured plugins/integrations. It can usually be retrieved as logs or tabular data within the corresponding integrations. \\ Only the breaching statements are sent, each one once even when several lines match it, with the severity, the alarm tag and the breach of its worst line. \\ Each row carries the host, port, connection, statement hash and status, DB user, application user, application name and source, client host, IP and process, workload class, running time, memory and the full statement text cut at 8000 characters. \\ Nothing is sent when the monitor runs with the Test button. | | Max statements | ''500'' | Defines the maximum number of statements fetched per run, the biggest memory first. | | Top statements | ''20'' | Defines the maximum number of statements per run that get their own metric series, tagged with the statement hash and the beginning of the statement text. \\ Only the statements selected by a surveillance line with **Metric** checked are candidates. They are ranked by allocated memory. \\ 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. | | Include suspended | disabled | If enabled, the statements whose cursor is open but idle (''SUSPENDED'') are counted as well in the workload metrics, which then carry a STATUS target to tell them apart. \\ The surveillance table always evaluates the executing (''ACTIVE'') statements only: an idle cursor never raises an alarm and never gets per statement metrics. | | Check privilege | enabled | If enabled, a WARNING alarm is raised when the monitoring user has neither ''CATALOG READ'' nor ''DATA ADMIN''. Without them the monitor only sees its own session, and a monitor seeing nothing would look healthy. | ===== 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 an ABAP program and line, so either can be used. An exclusion such as ''!ABAP'' excludes the statement when either contains the text. | | Statement | A filter on the statement text, case insensitive. The text is matched with its spaces and line breaks collapsed into single spaces. | | Max running time (s) | The threshold for the running time of a statement, in seconds. A statement running at least this long breaches the line. Default is ''60''. Leave at ''0'' to disable this criterion. | | Max memory (MB) | The threshold for the memory of a statement, in MB. A statement holding 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 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 running statement. | | 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. | | Metric | If checked, each statement matched by the line and reaching one of its thresholds gets its own running time and memory metrics. Default is unchecked. Not used when **Aggregate** is checked. | **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. A statement without a value, for example without application user, is kept by ''*'' and by an exclusion, and dropped by any other filter. **Note:** with both **Max running time** and **Max memory** at ''0'', every executing statement matching the filters breaches the line. **Note:** the thresholds support the severity syntax, for example ''G2W:60 W2M:300'' for WARNING from 60 and MAJOR from 300. 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:** **Metric** and **Alarm** are independent. A line with **Metric** checked and **Alarm** unchecked only sends metrics, so a line with a low threshold can feed the metrics while another line with a higher threshold raises the alarms. The severity of the line plays no role for the metrics. With the severity syntax, the metrics are sent from the lowest threshold. **Note:** with **Metric** checked and both **Max running time** and **Max memory** at ''0'', every executing statement matching the filters gets its metrics, up to **Top statements**. Keep this for a narrow filter: in normal times most statements run for 0 seconds and each new statement hash creates a new series. **Note:** in aggregate mode the severity of the alarm comes from **Max count** only, not from the running time 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 running statement, identified by its host, connection and statement id. The message contains the statement hash, the running time and the memory with their thresholds, the host, the users, the application, the client host and the beginning of the statement text: * ''Active statement 7a3f9c1d2e4b5a60 running for 420 s (>=300 s) using 2048 MB on hana01 by SAPABAP1 / JSMITH (ABAP:PRD) from appsrv01: SELECT * FROM VBAK WHERE VBELN = ?'' * The alarm stays open while the statement keeps running, and with **Auto clear** checked it closes on the first run after the statement ended. A new execution of the same statement raises a new alarm. * With **Aggregate**, one alarm is raised per line. The message contains the number of breaching statements, the count threshold and the filters of the line: * ''3 active statement(s) (>= 2 statements) for DB user SAPABAP1'' * A missing privilege raises its own WARNING alarm, independently of the surveillance table: ''Monitoring user MONITOR has no CATALOG READ privilege, active statements of other sessions are not visible''. * In integrations that show the metric of an alarm, an alarm per statement is reported under ''ACTIVE_STATEMENTS_TOP_DURATION_SEC'', an aggregate alarm and the privilege alarm under ''ACTIVE_STATEMENTS_COUNT''. ===== Examples ===== ==== Example 1 ==== ^ Active ^ Host ^ DB user ^ App user ^ Application ^ Statement ^ Max running time (s) ^ Max memory (MB) ^ Max count ^ Aggregate ^ Severity ^ Auto clear ^ Alarm tag ^ Alarm ^ Metric ^ | true | * | * | * | * | * | G2W:300 W2M:900 | 0 | 1 | false | MAJOR | true | | true | false | **Effect**: Sends a WARNING alarm for each statement running for 5 minutes or more, raised to MAJOR from 15 minutes. The alarm closes when the statement ends. ==== Example 2 ==== ^ Active ^ Host ^ DB user ^ App user ^ Application ^ Statement ^ Max running time (s) ^ Max memory (MB) ^ Max count ^ Aggregate ^ Severity ^ Auto clear ^ Alarm tag ^ Alarm ^ Metric ^ | true | * | !SYSTEM | * | * | * | 0 | 10240 | 1 | false | CRITICAL | true | MEM | true | false | **Effect**: Sends a CRITICAL alarm for each statement holding 10 GB of memory or more, except the statements of user SYSTEM. The running time criterion is disabled. ==== Example 3 ==== ^ Active ^ Host ^ DB user ^ App user ^ Application ^ Statement ^ Max running time (s) ^ Max memory (MB) ^ Max count ^ Aggregate ^ Severity ^ Auto clear ^ Alarm tag ^ Alarm ^ Metric ^ | true | * | * | * | ABAP | * | 120 | 0 | G2W:5 W2M:20 | true | MAJOR | true | | true | false | **Effect**: Sends one alarm when ABAP statements pile up: WARNING when 5 statements have been running for 2 minutes or more at the same time, MAJOR from 20. ==== Example 4 ==== ^ Active ^ Host ^ DB user ^ App user ^ Application ^ Statement ^ Max running time (s) ^ Max memory (MB) ^ Max count ^ Aggregate ^ Severity ^ Auto clear ^ Alarm tag ^ Alarm ^ Metric ^ | true | * | * | * | * | * | 60 | 0 | 1 | false | MAJOR | true | | false | true | | true | * | * | * | * | * | 600 | 0 | 1 | false | MAJOR | true | | true | false | **Effect**: The first line sends the running time and the memory of each statement running for 1 minute or more as metrics, without any alarm. The second line sends a MAJOR alarm for each statement running for 10 minutes or more. ===== Generated metrics ===== ^ metricId ^ metricUnit ^ metricTarget ^ metricDescription ^ | ACTIVE_STATEMENTS_COUNT | Statements | [HOST][USER][APP_NAME][STATUS] | Sends the number of running statements. | | ACTIVE_STATEMENTS_MAX_DURATION_SEC | Seconds | [HOST][USER][APP_NAME][STATUS] | Sends the longest running time. | | ACTIVE_STATEMENTS_TOTAL_MEMORY_MB | MB | [HOST][USER][APP_NAME][STATUS] | Sends the memory held by the running statements. | | ACTIVE_STATEMENTS_MAX_MEMORY_MB | MB | [HOST][USER][APP_NAME][STATUS] | Sends the memory of the biggest running statement. | | ACTIVE_STATEMENTS_TOP_DURATION_SEC | Seconds | [HOST][USER][APP_USER][APP_NAME][STATEMENT_HASH][STATEMENT] | Sends the running time of a statement selected by a line with Metric checked. | | ACTIVE_STATEMENTS_TOP_MEMORY_MB | MB | [HOST][USER][APP_USER][APP_NAME][STATEMENT_HASH][STATEMENT] | Sends the memory of a statement selected by a line with Metric checked. | **Note:** values in MB are rounded to whole megabytes. **Note:** the workload metrics are sent at every run for each host, DB user and application having a running statement. The application user is not a target of these metrics: it is only available on the per statement metrics. **Note:** a per statement series only exists while its statement is running and reaches a threshold of a line with **Metric** checked. Nothing is sent for it in normal times, so a monitor built on these metrics in an integration must treat missing data as normal. **Note:** the STATUS target is only present when **Include suspended** is enabled. It holds ''ACTIVE'' or ''SUSPENDED''. **Note:** a combination of host, users and application is only sent when at least one statement is running with it: a quiet database 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.