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