This discussion is archived
4 Replies Latest reply: Apr 20, 2013 9:34 PM by marksmithusa RSS

AWR retention with Exadata

user5431290 Newbie
Currently Being Moderated
We have a mixed environment all ...Exadata and non-Exadata

On the non-Exadata, we've been keeping 90 days worth of AWR data and it's been working quite well for us of the big benefits is being able to answer the question " It used to work fine two months ago...what happened ? "

We'd like to be able to do the same with Exadata...currrently we're using 30 days on there anyone out there using more than this..and how is it working out for you ?

  • 1. Re: AWR retention with Exadata
    Marc Fielding Journeyer
    Currently Being Moderated
    Hello user5431290,

    I'm personally a big proponent of long AWR/ASH retention periods, and have put 90-day retentions into place in several Exadata environments with good results.

    I also find Exadata to be a great environment to do large-scale ASH analysis, with a combination of a HCC-compressed version of dba_his_active_sess_history, and offloaded queries on the resulting table.

    The tradeoff naturally is space in the SYSAUX schema. At large sites, it's even possible to set up a central AWR environment, using the AWR export/import scripts to load AWR data from all databases and then removing from the source location.

  • 2. Re: AWR retention with Exadata
    user5431290 Newbie
    Currently Being Moderated
    I was able to get one of the high end Oracle employees at the current site where I'm at..and put the question directly to him
    There's no 'magic' value for retention and large AWR retention intervals ( as long as your disk space can handle it) are ok...and you've installed the required patches for AWR to prevent sysaux from growing to infinity

  • 3. Re: AWR retention with Exadata
    Kerry.Osborne Oracle ACE Director
    Currently Being Moderated
    I know of one customer that retains 7 years of AWR data (although they haven't had the system 7 years yet). The longer the retention, the better, as far as I'm concerned. We always try to get 14 months to cover any unusual year end processing. The main tables are partitioned by default by the way, so it's set up to handle large amounts of data. There is also a sql script to estimate space usage based on current workload (I blogged about it some where along the way - $ORACLE_HOME/rdbms/admin/utlsyxsz.sql - catchy name huh?). So you can check before increasing retention to get an estimate of how much space you'll need.
  • 4. Re: AWR retention with Exadata
    marksmithusa Journeyer
    Currently Being Moderated
    Wow. That makes my 14-day retention (including retained snapshot periods for certain points over the last two years) look puny. Thanks, gents :)

    Seriously, though, didn't realise the objects were partitioned. And that script'll be handy...


  • Correct Answers - 10 points
  • Helpful Answers - 5 points