A matter of statistics, part 3

This post is also available in: Bulgarian

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:

select p.value
  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)