How do you use your AWR data or Statspack data?

Do you retain it longer than the default retention?

Do you save it somewhere else?

Do you review it often?

Imagine that you get pulled into a performance problem, and the business users are telling you, with panic on their faces, that Report XYZ is running very slow.

You look them in the eye, and all you can see is hope, hope that you can solve their problem, and make the report run faster, like it used to.

You start asking questions, to shed more light on the problem.

Questions like:
How long does the report take?
How long did the report take when it ran fast?
And, this is my all time favorite: When was the time the report ran fast?

At the last question, you pray inside your head, that the AWR (Automatic Workload Repository) data is still available for the “good” performance times.

Why?

Because if you have some statistics for the time the report was running fast, you have things to compare to, and all of a sudden solving the problem becomes much easier.

In a perfect world, we would always have all the AWR data available to us to query!

Unfortunately, most of the time that is not the case. The AWR data that we need, is aged out from the repository and no longer available. This make solving the problem just a bit harder and longer.

It always amazes me how long it takes for a performance problem to reach the ears of the DBA!

There are critical performance problems, you hear about right away, and get involved in right away.

However, many times you get engaged in a performance problem, after a few months that the problem is present.

When you ask the user, they tell you, “A few months ago the performance was good”. Good luck with that!

So what can you do to take your DBA world closer to perfect?

You need to prep!
You need to think how can you preserve the AWR data, so it is readily available to you?

Here are some ideas you could implement to have AWR data at your fingertips!

• Increase AWR retention from the default 8 days to anywhere between 45-90 days. The longer the retention, the better.
• Backup the AWR data once a month (or as often as your retention goes), and have it readily available on disk. Since this data is not taking up much space, you could keep this for a long time, maybe indefinitely.
• Generate AWR reports for each snapshots you have. If you take a snapshot every hour, then you would have 24 AWR reports a day. You could zip these reports up once a week, or once a month for each database and keep these files for a long time, maybe indefinitely. These are really small html files, and do not take up much space.

Similar things could be implemented with Statspack reports, in case you have no AWR.

Now imagine having all this data at your fingertips. You no longer need to pray or hope to have the “good” performance data available in AWR, because you will always have it.

I am curious, what do you do with the AWR Data? Do you just let is age out from the repository without saving it? Hit reply, and let me know! I read every email!

If you want to know how to export the AWR repository data, make sure you tune in next week!