Re: AWR consolidation or mining Grid Control?
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.
On Sun, Oct 3, 2010 at 6:29 AM, LS Cheng <exriscer_at_gmail.com> wrote:
> 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?