DESCRIPTION
This alert will list SQL Statements that have recently executed outside their normal execution time. The previous 14 days are compared when calculating the average execution time for a SQL. The results are reported back in percentage above normal executions time, e.g. if 200 is returned that means the query executed twice as long on average withing the last hour than normal.
Warning: This alert is for Oracle repositories only.
Available Database Instances: All database platforms are supported as this alert executes against the Ignite repository.
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 SQL Execution Time
Execution Interval: 1 Hour
Notification Text: The following list of SQL statements have been running longer than normal.
SQL Statement:
select 'SQL '||sqlhashname||' recently averaged '||round(last_avg,5)||' which is more than twice the historical average of '||round(total_avg,5), 100* last_avg / total_avg
from (
select NVL(n.name, t.sqlhash) sqlhashname, t.total_execs, t.total_avg, h.last_avg, h.last_execs
from (
select sqlhash, total_execs, total_time, total_avg
from (
select sqlhash,
sum(execs) total_execs,
sum(timesecs) total_time,
avg(timesecs/execs) total_avg
from con_stats_sum_#DBID#
where execs > 0
and datehour > sysdate - 30
group by sqlhash
order by total_time desc)
where rownum <= 500) t,
(
select sw.izho sqlhash,
sum(ss.execs) last_execs,
sum(sw.qp)/100 / sum(ss.execs) last_avg
from consw_#DBID# sw, conss_#DBID# ss, (select max(iedx) iedx from contt_#DBID# where pub='Y') tt
where sw.d between (tt.iedx - #FREQUENCY#/1440) and tt.iedx
and tt.iedx = ss.d
and sw.izho = ss.h
and ss.execs > 0
group by sw.izho) h, con_sql_name n
where t.sqlhash = h.sqlhash
and t.sqlhash = n.hash
-- twice the historical average
and h.last_avg > t.total_avg * 2
-- and had at least 10 seconds of total wait time
and h.last_execs * h.last_avg >= 10)
Execute Against: Repository
Units: Percentage above Normal
High Threshold: MIN 500, MAX empty
Medium Threshold: MIN 200, MAX 500