This content has been marked as final. Show 4 replies
Hello user5431290,1 person found this helpful
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...