Alerts

• 1: Monitoring alerts
• 2: Alert configuration

1 - Monitoring alerts

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.

1. In the Settings Overview page, select Alerts. The Alerts page appears.

2. Choose Custom alert.

3. In the Alert name field, enter a unqiue name for the custom alert.

4. In the Description field, enter an optional description for the custom alert.

5. 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.

6. From the Interval list, choose the time interval at which the SQL query is executed.

7. In the Criticality level area, choose the alerty priority. By default, the alert criticality is Low.

8. 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.

9. Choose Create.

10. 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.

1. In the Alerts page, select a custom alert.
2. 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.

1. In the Alerts page, select a custom alert.
2. 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.

1. In the Alerts page, select a custom alert.

2. Hover over ⋮ and select Delete.

3. In the Confirmation dialog, select Delete.

   The custom alert is deleted.

2 - Alert configuration

Alert configuration

Enable and customize settings for pre-configured or custom alerts for each database. Pre-configured and custom alerts generate messages when the component they measure exceeds the specified threshold.

Configurable settings

Select Alerts in the Settings Overview page. To configure an alert, you must turn the switch on the right of the row to the on position. By default, custom alerts are turned off. Pre-configued and custom alerts have the following settings:

• Trigger condition - Add lower and upper limits on acceptable operating values for the component. Move the arrow up or down to either increase or decrease the query count.
• Interval - Choose how often the system executes the underlying SQL for the alert.
• Resource pool - Choose the resource pool to which you need to add this alert.
• Criticality level - Choose the alerty priority. By default, the alert criticality is Low.
• Send an email copy - Choose this option to enter the email address of the recipient. For multiple recipients, enter the email addresses separated by a comma.

Configuring resource pool alerts

Resource pool alerts use the same configurable settings as the other alerts, but you can set alerts per resource pool.

1. Login to OTCAD.
2. In the Settings Overview page, select Alerts. The Alerts page appears.
3. In the Resource pools tab, select the resource pool to which you need to add this alert.
4. Edit the trigger condition, interval, resource pool, criticality level, and recipient user email address. For more information, see Configurable settings.

Configuring query alerts

You can configure alerts that monitor queries.

1. Login to OTCAD.
2. In the Settings Overview page, select Alerts. The Alerts page appears.
3. In the Queries tab, select the query to which you need to add this alert.
4. Edit the trigger condition, interval, criticality level, and recipient user email address. For more information, see Configurable settings.

View alerts

The Alerts page organizes alerts to help you effectively monitor the state of your database. Pre-configured and custom alerts generate messages when the component they measure exceeds the specified threshold.

1. Log in to OTCAD.
2. Click the Bell icon in the upper‑right corner of the application. The Alerts page appears.
3. From the Date range list, choose the period to view the alert. Do one of the following:
   • Choose Last 15 min to view only those alerts that were created in the last 15 minutes.
   • Choose Last 1 hour to view only those alerts that were created in the last one hour.
   • Choose Last 7 days to view only those alerts that were created in the last 7 days.
   • Choose Last 14 days to view only those alerts that were created in the last 14 days.
4. From the Notification type list, choose the type of alert. Do one of the following:
   • Choose All to view both resource pool and query alerts.
   • Choose Queries to view only query alerts.
   • Choose Resource pool to view only resource pool alerts.
5. In the Severity area, choose the option to view the alerts by severity. Do one of the following:
   • Choose Low to view alerts with low priority.
   • Choose High to view alerts with high priority.
6. Select Mark as read after you view an alert and do not wish to view the same alert again.

   Note

   The system does not display notifications that are marked as read.