AWR Warehouse

AWR Warehouse is a central repository configured for long term AWR data retention. It stores AWR snapshots from multiple database sources. Increasing AWR retention in the production systems would typically increase overhead and cost of mission critical databases. Hence, offloading the AWR snapshots to a central repository is a better idea. Unlike AWR retention period of default 8 days, the AWR Warehouse default retention period is "forever". However, it is configurable for weeks, months, or years. 

For more information on AWR Warehouse click on the following link for a video tutorial. 

http://www.youtube.com/watch?v=StydMitHtuI&feature=youtu.be

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.