SQLs greater than x secs
Andi or anyone,
i would like to alert on all the SQLs if they are greater than x secs. So i have the below config of the BT and we have SQL aggregation is on.
When i drill down into one of the alerts, i see one sql executed 4 times and total time is greater than 2 secs (which is the threshold i have put in the BT). But when i checked the individual sqls for that purepath none of them are greater than 2 secs. So i am wondering how can i achieve this?
Hi
you need to specify "sum" as the aggregation option in your BT. Why? Because for a single PurePath your measure will provide a value for each individual SQL node in your PurePath. In your case that is 3. If you select "Last" then the value 5.96 will be used as result measure. Try "Sum" and you should get 5707.33 which will then trigger your threshold
Andi
Thanks Andi for the reply. Just want to make sure i have explained it clearly what i want to achieve. in the screenshot i provided i chopped off other queries as the only query that has high time is the first one which is executed 4 times and total is 5707.33. When i drill down, max time for any query was less than 2 secs. So it should not have alerted in the first place? I don't want to alert if the sum for a query is more than 2sec but i would like to alert when an individual query itself takes 2 secs.
To give you another example -
Here first query shows it has 6102 secs (for 4 executions) and may be that is why it is alerting as it is > 2 secs. But when i checked the individual queries nothing is more than 2 secs. So how can i achieve that?
Set the aggregation to 'max' if you want to achieve this
Thank you. i actually have max in the alert. i will try max in the BT itself
Richard/Andi - i tried with Max, but i still see sqls less than 2secs when i drill down into DB...
Do the results include SQL queries over 2s? This is what I would expect, as the Business Transaction evaluates per-PurePath, so there may be other queries less than 2 seconds executing. If you need PurePaths where all queries were over 2 seconds (which may never happen if there are multiple executions in the PP) then you would set the aggregation to 'min'.
Hope that helps,
Rick B
Rick,
i did not see a single sql with > 2 secs.
I can reproduce what you are describing. It seems to have to do with whether database statement aggregation is enabled.
If enabled, min/max/count are aggregated per statement
If disabled, aggregations are measured per execution
In my opinion this setting should not change the meaning of the metrics so I am opening a ticket. I will update this post when I have a response from the lab.
Thanks,
Rick B
