Wild Tip #12, Oracle Performance Tuning Expert
Create an AWR Repository
This tip is one of the most powerful ideas I have ever used. Using the AWR repository is great if you only need to check back a month or two. But what if you want to see a long term trend over years? Suppose you want to see how a sql has degraded over time?
Obviously, the standard AWR report is not going to be very helpful; therefore, for some critical databases, we have created a repository that contains years of information. The beauty of this approach is, that you only need a small set of tables, which don’t require a lot of disk space.
Simple, Small, but Powerful!
Here's why this new repository will be pretty small: You don’t need the Active Session information—you just need the metrics for each sql. It is astonishing the information you can glean from just five tables.
Here are our five custom tables:
- DA_DBA_HIST_SERVICE_STAT
- DA_DBA_HIST_SNAPSHOT
- DA_DBA_HIST_SQLSTAT
- DA_DBA_HIST_SYSSTAT
- DA_DBA_HIST_SYSTEM_EVENT
Here's How it Works
A few times a day, we run a job that copies the new information from the original “Dba_Hist” tables into our repository. Then, to use these five tables, you simple query your repository tables instead, like this:
We have used the above system on our most critical database for many years. When users ask me to check the historical performance of some report or sql, it is easy to do. I bet you will find it easy as well.
