Thursday, December 02, 2010

I bet you haven't seen 62 hours of AWR Report

One of my customers is having extreme performance issues during batch load. The batch is consuing around 6 hours to complete which is supposed to complete in less than 90 minutes.

The guys shared their AWR report with us and here is the snapshot information for you guys:

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:      4621 27-Nov-10 00:00:09        30       1.3
  End Snap:      4683 29-Nov-10 14:00:51        30       2.0
   Elapsed:            3,720.70 (mins)
   DB Time:            3,021.65 (mins)

This report is for whopping 62 hours. This long report results in a meaningless data as everything is averaged out and one doesn't see the real issues.

With this information at hand I can say:

1) Mostly likely they haven't changed the default AWR snapshot duration (i.e. 1 hour)

2) I would then request the guys to generate hourly AWR reports during the time they were experiencing extreme performance issues.

3) Lastly, suggest them to change the snapshot duration from (default) 1 hour to 15 mintues.