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

Advertisements

2 Responses to Statspack Data Warehouse

  1. Parijat Shah says:

    What is more intriguing for managers is to be able to look at a graph and get a feel of the health of the database. This is even more so as, though managers love to pose as geeks and burp out jargons and buzz words at every possible change to shine, in the deep of their hearts they are confident that all they need is a graph.

    I would suggest it would be of a great value to add cool tools to convert your DW analysis of the statistics cubes into graphs, to the arsenal of tools you are looking forward to build.

    • Gord Irish says:

      The statspack data lends itself to be graphed with Excel. The data is all time based so it can be graphed as a statistic or set of statistics changing over time quite simply. Time based graphs are very common so Excel has tools to support creating them and it is easy for just about anyone to get some idea from them.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: