The second step in the ETL process is with Oracle Enterprise Manager 12c. Now we are onto the Enterprise Manager Job Service and it submits an Enterprise Manager Job on the host to transfer the data from the target to the warehouse host for the third part of the ETL process. This is an agent-to-agent host process to transfer the dump files directly from target host to AWR warehouse host, so at no time do these dump files end up on the Oracle Enterprise Manager 12c host unless you were housing your warehouse on the same server as your Oracle Enterprise Manager 12c environment.
Figure 3
The last step for the ETL process is to then complete the load to the AWR warehouse. This is another DBMS job that takes the dump files and imports them into the AWR Warehouse schema. DBIDs are mapped and any duplicates are handled, (not loaded to the final warehouse objects…). The ETL is able to handle multitenant data and at no time is there a concern then if more than one database has the same name.
The Warehouse Console
Once set up, the AWR home can be accessed from the Targets menu. Click on Databases, then once you’ve entered the Databases home, (databases should be listed or shown in a load map for this screen, click on Performance and AWR Warehouse.
The following dashboard with all the pertinent data about AWR Warehouse will appear:
Figure 4
The console, including the sizing information, amount of daily snapshot uploads, database information and snapshot retention for each source database, also includes management options to add/remove more source databases and assign privileges to manage snapshots in AWR Warehouse.
Reporting
AWR Warehouse adds some exceptional features in the reporting department. Along with the AWR Viewer, which gives easy console access to historical views of all AWR reports for any snapshot combination for the source target in question, the following reports are included:
- AWR Report
- ASH Analytics
- ADDM Compare Period
The ability to run AWR reports for six month, a year or more historically are impressive. The next generation of Top Activity, ASH Analytics is something I’ve written and blogged about for quite some time. The ability for the product to adapt and answer performance questions is what we all need, but then we get into the ADDM Comparison Report, (Compare Period).
How often do we ask what’s changed or what’s different? The ADDM Comparison Report takes it one step further with AWR Warehouse and allows you to ask this question not just of a one snapshot to another, but compare one database to another!
When choosing to run the Compare Period report in AWR Warehouse, you have an additional field with a drop down that will show you all databases name and DBIDs. You can then choose the database you want to compare your database to, along with the time period you want to compare to. As this is an ADDM comparison, it will choose the snapshot closest to the time period and use this for the comparison, so keep that in mind:
Figure 5
The report will then run and compare all SQL, parameters, resource usage and other pertinent differences identify and recommend ways of addressing the issues.
Next Generation
AWR Warehouse and console features are still in their infancy and I’m excited about where the product is heading. The opportunity for the DBA and developer to set it up and start using it should be an easy choice. The Oracle Database Enterprise Edition license means that you can install the AWR Warehouse repository on a server, configure it, use it and if you don’t introduce features that require added licensing, (RAC, data guard, etc.), you now have the product for the IT community to answer all those challenging technical questions right at your fingertips!