Inside thе buffer cache – BGOUG Pravets 2010

 Oracle  Comments Off on Inside thе buffer cache – BGOUG Pravets 2010
Nov 222010

The slides from my session at BGOUG seminar at Pravets can be downloaded here: click

Great thanks from Milena an the other members, which again surpassed themselves and made ​​a great seminar. Dan Morgan’s session earn my personal “best of bgoug nov’10”. Julian once again won the prize for communication with my audience.

For me the most interesting was my personal AskTom session on Saturday evening, when the vodka (for me) and Shumensko (for Tom ), discussing about life, indexes, and many other things.

The whole gathering was a real feast for my Oracle soul:)

 Posted by at 13:37

A matter of statistics, part 3

 Oracle  Comments Off on A matter of statistics, part 3
Nov 012010

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 = '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)

 Posted by at 13:57