Monitoring alerts

This topic provides an overview of alerts in OTCAD.

Pre-configured alerts

The system provides pre-configured alerts to provide system monitoring capabilities without manual setup. Each alert has a pre-configured threshold that defines the acceptable performance limit. The system then sends a message notification when the database exceeds that threshold.

You can monitor the system using preconfigured alerts without setting them up manually.

Queries

Query performance alerts notify you when queries exceed execution time thresholds. The system provides the following pre-configured alerts to monitor queries:

Failed query number - The total number of queries that failed during execution due to errors.
Query running time - A query that is neither in the queue nor completed is a running query. The Query Running Time reflects the elapsed time since the query started executing.
Queued query number - The number of queries currently waiting in the queue for resources before execution begins.
Retried query number - The number of queries that are placed on hold for different reasons, such as limited resources.
Spilled query number - The number of queries that were written to disk when the system did not have enough memory.

Resource pool

The system sends alerts when an individual resource pool reaches a specified state or usage level. For details about resource pool configuration parameters, see Built-in resource pools configuration. You can configure the following resource pool alerts:

Ended query with queue time exceeding limit - The number of queries that were not executed because their queue time exceeded the allowed limit.
Ended query with runtime exceeding limit - The number of queries that were stopped because they ran longer than the time limit set by the resource pool.
Maximum allowed resource value - The maximum resource limits (such as memory, CPU) allowed for a query defined in the resource pool.
Minimum starting resource value - The minimum resources required for a query to start running.
Queries with resource rejections - The number of queries that were rejected due to insufficient resources.
Queries reaching the max allowed execution time - Set a time threshold to see how many queries reached that limit during execution.

Custom alerts

Create custom alerts to measure system performance metrics that are not monitored by the pre-configured alerts. Create a dynamic SQL query that triggers an alert when it returns any results. You can configure how often an alert is generated, the alert criticality level, and who receives email alerts. For example, you can create custom alerts that monitor the following:

Failed logins within a configurable time period
Idle database user sessions using a configurable time limit

Note
You must have administrator privilege to create, edit, clone, and delete custom alerts.

Create a custom alert

Create custom alerts to track abnormalities and performance fluctuations for queries and resource pools using your own database schemas and tables. When the database triggers an active alert, you receive notifications according to the alert priority.

Note

Creating custom alerts on custom tables is not recommended because it can increase query execution time and affect performance. Custom alerts are designed to work optimally with system tables.

In the Settings Overview page, select Alerts. The Alerts page appears.
Choose Custom alert.
In the Alert name field, enter a unqiue name for the custom alert.
In the Description field, enter an optional description for the custom alert.
In the Trigger condition query field, enter the following SQL query and choose Run:
```
SELECT
  login_timestamp,
  user_name,
  node_name,
  client_hostname,
  reason
FROM
  login_failures
WHERE
  reason in ('INVALID USER', 'FAILED', 'INVALID DATABASE')
  AND login_timestamp > sysdate - INTERVAL '{{Time_Interval}}'
```
The AND clause of the preceding query encloses the variable of type String named Time_Interval in double curly braces to represent a valid SQL syntax element.

A variable is a dynamic value that is evaluated at runtime that you can configure after you save the alert. You can add up to 6 variables per custom alert. Variable names may consist of a maximum of 14 alpha-numeric characters, including underscores. Verify that the variable uses the correct data type and format. Variables of type String require single quotes around the curly braces.

A SQL query triggers an alert if it returns one or more rows.
From the Interval list, choose the time interval at which the SQL query is executed.
In the Criticality level area, choose the alerty priority. By default, the alert criticality is Low.
Choose the option Send an email copy. In the Add new user field, enter the email address of the recipient. For multiple recipients, enter the email addresses separated by a comma.
Choose Create.
Turn the alert on or off using the switch at the right of the alert row.

Edit a custom alert

After creating a custom alert, you can edit the details to suit your requirements.

In the Alerts page, select a custom alert.
Hover over ⋮ and select Edit. All the details of the custom alert are populated. You cannot edit the name of the custom alert. For more information about editing a custom alert, see Create a custom alert

Clone a custom alert

You can create a clone or replica of an existing custom alert. The settings of the existing custom alert are copied to the cloned custom alert. You can edit these settings in the cloned custom alert.

In the Alerts page, select a custom alert.
Hover over ⋮ and select Clone. Information in all fields is pre-populated. You can edit this information. For more information about editing a custom alert, see Create a custom alert

Delete a custom alert

You can delete a custom alert that is no longer in use or required.

In the Alerts page, select a custom alert.
Hover over ⋮ and select Delete.
In the Confirmation dialog, select Delete.

The custom alert is deleted.