I’m back at the system load topic. I was thinking about this quite some time, and now I have it. I will post it here – to share and to remember.
The task is: find the values of some system statistic based on the AWR reports (in this example – PGA usage) – for the last, say, 60 days. It wouldn’t be right to compare the load at 3 PM to the one at 3 AM. So I want to check only reports, standing +/- 2.1 hours from the current time of day. In other words, st 3 AM I want the values from 1 to 5 AM during the last 60 nights.
And here is the query:
from dba_hist_pgastat p, dba_hist_snapshot s
where s.snap_id = p.snap_id
and p.name = 'total PGA allocated'
and s.end_interval_time > sysdate - 60
and abs(remainder(cast(s.end_interval_time as date)-sysdate, 1)) < 2.1/24;
It works like this
- take the difference between sysdate and the date of the awr snapshot. The result is in days. Works for all dates - older and newer then the current
- get only the time (in other words, repove the "day" part) using
remainder. The beauty of this function is, it works in "limit" cases. E.g. in 1 AM you can take 11 PM from the day before (and vise versa). I did not expect to have such an easy and elegant solution! 🙂
- compare with the given limit, in our case - 2.1 hours (2.1/24)
The you can run the resulting number through the query, which I posted last year, to check if the current value is "normal" or suspicious.
The key here is to keep enough AWR reports history. The statistics I use will work only for 30 or more "observations" (as someone noted last year)