AWR Warehouse in EM12c Rel. 4

I know a number of us performance tuning DBAs who’ve long wanted to retain AWR data, at least high-level AWR data for an extended period outside of the AWR repository on the local database. I implemented my own solution back in 2009-2010 at a previous employer as I was tired of hearing, “nothing’s changed”.  Nothing like having the high-level AWR data on CPU, IO, network, application and concurrency waits to rebuke this type of assumption at budget decision time…. 🙂

The AWR data is collected as snapshots from ASH, AWR and ADDM in the target databases and then loaded to a central AWR repository, (database version 11.2.0.4 or higher…) Due to the use of a central AWR repository, there is no overhead on the source database.  Each target is identified by the TARGET_GUID, just as with targets in the EM12c repository, making loads and reporting simplified.

The AWR Warehouse captures all data from the snapshots on the targets and then loads it to the central repository.  All AWR data is retained within the repository and an unlimited amount of AWR data can be stored unless a unique retention time is chosen.

AWR Architecture

How is EM12c Involved?

With the AWR Warehouse, the administrator is able to manage the AWR data with the seamlessly integrated UI and provide data to IT and management via dashboards in EM12c release 4.  The main UI is easy to navigate and has enough of a familiar feel for the administrator to come up to speed very quickly.

The administrator can view performance pages, run AWR, ADDM and ASH reports against data that was collected in snapshots a year, two years or more in the past.  A compare ADDM report can even be run to inspect performance differences between an hour of activity a year or more apart!

Many will recognize the value of the AWR Warehouse immediately, while others may need a bit to see how quickly they can answer the question of “what changed” and enhance it from what changed within the last week or month to what has changed in the last year on.  The benefit to capacity planning and the retirement of the “capacity planning spreadsheet” I’ve seen so often from IT is something I’m looking forward to, how about you?

So pretty cool, but Leighton, Brian Pardy and others wanted to know about licensing and any catch?  Yes, it will require the diagnostic and tuning management packs for all the features.  For the repository database, considering the scope of the data that you will be retaining, it will require an EE license and to scale and perform, you may want to seriously consider RAC.  Now, for you really serious data hoarders that never want to give up anything, consider partitioning, (I know, more licensing… :)) and there will be partitioning best practices in the documentation forthcoming from Oracle.

I promise to link to AWR Warehouse documentation soon, (not out till early July) but until then, enjoy this tidbit on this fantastic new opportunity to retain AWR data for the benefit of the IT environment and you!