Database Comparisons with AWR Warehouse- Part II, Comparison Period Report
There are two ways to compare one database to another in the AWR Warehouse. I covered the ADDM Comparison Report here and now we’ll go through the second one, which is much more involved and has us empowering the AWR Warehouse taking two AWR Warehouse reports and comparing two databases to each other.
The AWR Warehouse, once setup and databases that are targets already monitored by your EM12c or EM13c environment, can then be added and upload all AWR snapshots to this central repository.
Comparison Period Report
The AWR Warehouse second comparison reporting option is accessible from the drop down menu in the AWR Warehouse dashboard:
Once you click on Compare Period Report, you’re offered to choose a baseline or snapshots from the list for the databases you wish to compare:
In my example, I simply chose the DNT database, with a one hour snapshot window to compare to an OMR, (Oracle Management Repository) database for another one hour snapshot interval. Clicking on Generate Report will then create an HTML formatted report.
The Actual Report
In the report summary, not only does the report show that I’m comparing two different databases from two different hosts, but any differences about the main configuration will be displayed. We can see that although I’m comparing the same amount of time, the average number of users is twice and the DB Time is extensively different for the two databases.
The report will then start comparing the high level information, including the host, the memory and I/O configuration-
The Top Ten Foreground events are displayed for each environment, ensuring there isn’t anything missed that could be confusing if a comparison was performed. In a more similar database, (let’s say test against production or old production vs. a newly consolidated environment) there’s going to be more similarities and you’d be able to see how the workload had changed between systems.
Each section contains values for the specific database and then the differences, saving the DBA considerable time manually calculating what has changed. Once you get to the Top SQL, the report updates it’s format again to display the SQL in order, over all, for time elapsed, CPU, etc. and then bread down between the times for each environment run or not and the difference.
After breaking down the SQL in every way possible, as commonly seen in an AWR report, but with the added benefit of comparisons between two different AWR reports and databases, the report digs into each of the Activity Stats and compares all of those:
The report then does comparisons for SGA, PGA, interconnects and even IO:
Once completed with these, it then digs into the objects and tablespaces to see if there are any outliers or odd differences in what objects are being called by both or either database.
As with all AWR reports, it also pulls up all Initialization Parameters and performs a clear comparison of what is set for each database so you can view if there is anything amiss that would cause performance impacts.
This is an incredibly valuable report for those that want to perform a deep analysis comparison between two databases for time periods around performance, workload, migration or consolidation. The comparison reports are one of the top features of the AWR Warehouse and is so infrequently considered a selling point of the product, (and if you already have the diagnostic and tuning pack, heck, it comes with it’s own limited EE license like the RMAN catalog and Enterprise Manager repository database) so what are you waiting for??
