This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - Commit Latency

DESCRIPTION

This alert will review the Disk Write Time - Commits metric collected by Ignite and provide a warning when the metric goes above 15 ms and a critical alert when it goes above 20 ms. The metric is averaged over the timeframe for the alert, i.e. 5 minutes for this example. This raw Commit metric is calculated by getting the average time for a log file sync wait and included as a canned resource metric in Ignite.

Note: The example below uses the "Disk Read Time - Commits" metric, but a very similar query can be used for other metrics as well. For example, if you wanted to monitor O/S Memory Utilization, you could modify the "where mn.NAME = 'CPU Utilization'" line to "where mn.NAME = 'Memory Utilization'".  For a list of all metric names, you can run these queries:

select id, name from cond; -- use the ID for the instance in the query below for <DBID>

select name from con_metrics_names_<DBID> order by name; -- substitute the value of name into the alert query

ALERT DEFINITION

To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:

Alert Name: Commit Latency

Execution Interval: 5 Minutes

Notification Text: The average commit latency for the last 5 minutes has exceeded thresholds

SQL Statement:

SELECT NVL(AVG(md.V*1.0),-1) avg_5min

FROM con_metrics_names_#DBID# mn

INNER JOIN con_metrics_#DBID# m ON m.metric_name_id = mn.id

INNER JOIN con_metrics_detail_#DBID# md ON m.id = md.metrics_id

WHERE mn.name = 'Disk Read Time - Commits'

AND md.d >= SYSDATE - (5/1440)

Execute Against: Repository

Units: ms

High Threshold: Min 20, Max empty

Medium Threshold: Min 15, Max 20