I have a question on the CoordActLifetime histogram. We are calling WLM_COLLECT_STATS (actually OPM is dong it) every two minutes to collect/reset statistics from the activities. Looking at the histogram data collected in one of these 2 minute windows, we observed that in the upper bins, there are activities with lifetimes of 1715085 milliseconds (@28 minutes). How is this possible?
The lifetime data for an activity is only actually accounted for in the Coordinator Activity Lifetime (CoordActLifetime) histogram when the activity is complete and its lifetime is over.This means that while the activity is executing, it does not contribute to the bin counts in this histogram and its existence will not be reflected in any statistics collected during this time. When the activity ends, its lifetime is reflected in the population count for the appropriate bin in the CoordActLifetime histogram and the next statistics collection will reflect its existence.
So, when you see a 28 minute duration activity in the 9:28 to 9:30 colelction interval, it actually corresponds to an activity that started somewhere between 9:00 and 9:02.
Of course, to be completely (and pedantically :) accurate, you don't actually see a 28 minute activity in the bin, you see a bin count of greater than zero for the bin representing activities with lifetimes between 1124409 milliseconds (@18.7 minutes) and 1715085 milliseconds (@28.6 minutes) which means that those activities started somewhere between 8:59 and 9:10 :)
Also, as always, beconsious that liefetime does not always mean busy time; it simply means the time between the request to start executing and the end of execution. The query itself may not have been actively executing that whole time for any numebr of reasons such as it was slow due to runtime conditions (e.g. lock waits) or it was a cursor slowed due to waiting on client fetch requests, etc. But that's another topic entirely ;)