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 - Abnormal SQL Execution Time - Oracle

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 emoticons_plus.png

-- 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