If you want to alert on a specific query executing N% more than the avg, here is a start to get you going.
Go to Options -> DB Query Tool -> run SELECT ID, NAME from COND -- to get the id of the instance you want to create an alert for
In DPA, go to Alerts -> Manage Alerts -> Custom of Multiple Numeric Return (Create Alert)
Name the alert something appropriate, choose 1 hour as the interval (since we are going after summarized data to be efficient), then select the instance to watch for a specific query's executions.
Use this SQL to run against the repository since we have already collected the stats (no need to place any additional load on the monitored target):
select datehour, (execs-(select avg(execs) from con_stats_sum_2 where PERIOD = 'H' and SQLHASH = 3072687556))*100/(select avg(execs) from con_stats_sum_2 where PERIOD = 'H' and SQLHASH = 3072687556)
from con_stats_sum_2
where PERIOD = 'H'
and SQLHASH = 3072687556
and datehour = (select max(datehour) from con_stats_sum_2 where PERIOD = 'H' and SQLHASH = 3072687556)
NOTE: Replace the 2 in the table name con_stats_sum_2 with the ID selected from COND that you want to monitor. Also, replace the SQLHASH with the value of the query you want to alert on within that instance.
Set the units to Percent and choose a threshold to alert on (like 20 or 30). Test the alert out to make sure all looks good. (Generic query added as a file to this post.)
