We have a mixed environment all 188.8.131.52 ...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 ...one 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 Exadata..is there anyone out there using more than this..and how is it working out for you ?
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.
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
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.
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...