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.

3 comments:

OraCle said...

Hai Asif,Is there a chance that the awr report is run between the 2snapshots and whether they would've set high retention period for this snapshots.

Anonymous said...

Hai Asif,Is there a chance that the awr report is run between the 2snapshots (al though other snapshots are available)and whether they would've set high retention period for this snapshots.

Asif Momen said...

Seems they are living with the default 7 days retention and a snap period of 1 hour. But the guy generating AWR reports in not an Oracle guy and therefore generating these reports for a enormous duration.