Statspack Data Warehouse

I was just working on something that involved AWR (or Statspack ) retention. I was given about 75 statspack reports and asked to work up some Excel graphs of anything I found significant. At first I thought that I would have to copy all that data off the reports and into Excel to do the analysis and graphng. Groan. Lucky for me, in this case, the statapack data was still on the database so I was able to run some queries to extract what I needed into Excel.

But what if I had been given this task for data that had aged out of the statspack tables? Then I would have been stuck. At the same time, I can see the value in the types of analysis that could be done with a few years of AWR/statspack data. It could be useful to track parameter changes over time, it could useful to compare performance this month with the same month last year, etc, etc. But a lot of us don’t want to store all that statspack data in our production systems.

Which leads me to wonder about using some data warehousing techniques to extract the data to a data warehouse server. The data ought to be easy to extract as it is all keyed by snap_id. By data warehouse standards it is also not large so even a modest DW server could hold a few years of this data. I believe all the AWR/statspack data is actually keyed by db_id/snap_id so it should be possible to store this data from multiple databases in one DW and so some cross-system analysis, which might also be interesting.

It also seems to me that someone else has already done this but so far I haven’t found any references to it. If anyone out there has done something like this, please let me know