Re: AWR consolidation or mining Grid Control?

  • From: Darren Darnell <darren@xxxxxxxxxxxxxxx>
  • To: exriscer@xxxxxxxxx, Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 3 Oct 2010 08:11:21 -0500

I use a set of stored procedures for an ETL process that transfers the
AWR data to a central reporting database.  The stored procedures are
based off of the DBMS_SWR_INTERNAL calls from the awrextr.sql &
awrload.sql scripts.

Since the DBMS_SWR_INTERNAL package is used, the data is kept in the
AWR schema.  There are several advantages to keeping the data in the
AWR tables/views:

   +  The Oracle provided ASH & AWR reports can still be used.
   +  The DBA_HIST views are well documented making custom reports
much easier.
   +  At some point Oracle will hopefully add a historical component
to Grid Control.  If/when that happens, then the historical data can
be moved into Oracle's Grid Control "archive".

I started out down the path of creating a schema with a subset of the
data from the AWR.  But the more I worked on the design, the more it
looked like Oracle's AWR schema.  Then it became an question of
effort.  Oracle has provided all of the pieces to export and load the
AWR into a central repository.  Why re-invent the wheel?  It was much
easier to keep the data in Oracle's schema and create materialized
views for the information I needed for reports.

Darren

On Sun, Oct 3, 2010 at 6:29 AM, LS Cheng <exriscer@xxxxxxxxx> wrote:
>
> Hi
>
> I have seen in the past in the list some discussions regarding using AWR data 
> to perform capacity planning. However I think I only saw once someone 
> mentioned the possibility of storing several database's AWR data in a sort of 
> Data Warehouse (database for the DBAs).
>
> I might try to consolidate 20 database (all of them 10.2.0.5) AWR data in a 
> Warehouse, not all AWR data, just a dozen of matrics I am interested. I 
> wonder if anyone's got experience with this? I can think of two approaches
>
> Design a Star Schema from scratch
> Design a 3NF schema following AWR data model
>
> The other option is all these 20 databases are monitoried by Grid Control 
> which stores 13 months data. But I have been looking Grid Control's data 
> model and doesnt seem very intuitive. Anyone's got experience with mining 
> Grid Control data?
>
> TIA
>
> --
> LSC
>
--
//www.freelists.org/webpage/oracle-l


Other related posts: