products:promonitor:latest:monitorsguide:hana:hanaexpensivestatements

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, section expensive_statement: enable = true.
  • threshold_duration of the same section is in microseconds. For example 1000000 records the statements running longer than 1 second.
  • The peak memory of a statement is only recorded when resource tracking is on in global.ini, section resource_tracking: enable_tracking = on and memory_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 READ system privilege, included in the MONITORING role, 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 under EXPENSIVE_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.

products/promonitor/latest/monitorsguide/hana/hanaexpensivestatements.txt · Last modified: by rbariou