Description
This alert will tell you when an Oracle monitored instance has a query that returned more rows than it normally averages. This example uses a 200 % threshold (the SQL returned twice as many rows as normal) for the Critical level and 150%-200% for the Warning. The SQL code in the alert definition uses the last 14 days for each SQL statement as the baseline. It also uses summary data so running it more frequently than once an hour will not have benefit.
Alert Definition
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: Abnormal Row Count per SQL
Execution Interval: 1 Hour
Notification Text: The following list of SQL statements had 200% (twice as many) rows returned as they normally do.
SQL Statement:
select today.sqlhash, 100.0 * today.row_count / hist.avg_daily_row_count
from (
select sqlhash, avg(daily_row_count) avg_daily_row_count
from (
select sqlhash, trunc(datehour) day, sum(row_count) daily_row_count
from con_stats_sum_#DBID#
where period = 'H'
and datehour >= trunc(sysdate-14)
and datehour < trunc(sysdate)
group by sqlhash, trunc(datehour)) daily
group by sqlhash) hist
inner join
(select sqlhash, sum(row_count) row_count
from con_stats_sum_#DBID#
where period = 'H'
and datehour >= trunc(sysdate)
group by sqlhash) today on today.sqlhash = hist.sqlhash
where hist.avg_daily_row_count > 0
and today.row_count / hist.avg_daily_row_count > 1
and today.row_count >= 5
Execute Against: Repository
Units: % Higher Row Count
High Threshold: Min 200, Max empty
Medium Threshold: Min 150, Max 200
