Description
This alert will tell you when an Oracle monitored instance has a SQL that executed more often than it normally averages. This example uses a 200% threshold, meaning the SQL executed twice as many times as normal, as the critical level and 150%-200% as 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: SQLs with Abnormal Number of Executions
Execution Interval: 1 Hour
Notification Text: The following list of SQL statements had 200% (twice as many) executions as they normally do.
SQL Statement:
select today.sqlhash, 100.0 * today.execs / hist.avg_daily_execs pct_increase
from (
select sqlhash, avg(daily_execs) avg_daily_execs
from (
select sqlhash, trunc(datehour) day, sum(execs) daily_execs
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(execs) execs
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_execs > 0
and today.execs / hist.avg_daily_execs > 1
and today.execs >= 5
Execute Against: Repository
Units: % Higher Executions
High Threshold: Min 200, Max empty
Medium Threshold: Min 150, Max 200
