If you know of Douglas Adams you will know that the answer to life, the universe and everything is 42**.
In the case of Statspack, and AWR, 42 is my preferred answer to the question: “How long should I keep the snapshot data?”
One of the strengths of collecting snapshot data is that it makes it easy to compare reports from good periods and bad periods. To this end, it wouldn’t be unusual to compare a report from today with:
The same day last week
The same day four weeks (ISO month) ago
The same day of the month last month (e.g. 1st of month)
Same “logical day” of the last month (e.g. 2nd Friday)
If you’re going to do all these, you need five weeks of data in order to make sure that you can cover all the possibilities. And since it’s not always possible to do the analysis the moment the problem appears, it makes sense to leave one week in hand – i.e. six weeks, hence 42 days.
The keep time for Statspack data is unlimited, the default keep time for AWR is seven days – so you may want to take steps to increase the AWR default (using procedure dbms_workload_repository.modify_snapshot_settings) and put in place a schedule for purging old Statspack data (using the sppurge.sql script).
Of course, you may want to do comparisons with the same periods a year ago (compare this Christmas with last Christmas, for example) so there is an obvious argument for even longer retention periods.
If you are running your own reports against the data remember that you should (as with all SQL you develop) understand the data content and structure so that you can optimise the queries before putting them into production. A query that works well on the first few days of snapshots may perform very badly when you have a couple of months of data recorded.
** If you know Douglas Adams’ work well, you will also know that the question is “What do you get when you multiply six by nine?”. If you are a real nerd, you will realise that the answer to this question is 42 … but only if your arithmetic operates in base 13 rather than the more traditional base 10. (Check the sum: 4 * thirteen + 2).