Search

Top 60 Oracle Blogs

Recent comments

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.

possiblehttp://i1.wp.com/dbakevlar.com/wp-content/uploads/2016/05/possible.gif?r... 300w" sizes="(max-width: 480px) 100vw, 480px" data-recalc-dims="1" />

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:

addm_compare1http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/addm_compare1.... 300w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/addm_compare1.... 768w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/addm_compare1.... 1200w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/addm_compare1.... 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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:

compare1http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/compare1.png?r... 300w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/compare1.png?r... 768w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/compare1.png?w... 1455w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/compare1.png?w... 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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

awrc1http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc1.png?resi... 300w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc1.png?resi... 768w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc1.png?w=1462 1462w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc1.png?w=1200 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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-

awrc2http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc2.png?resi... 300w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc2.png?resi... 768w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc2.png?w=1232 1232w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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.

awrc3http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc3.png?resi... 300w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc3.png?resi... 768w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc3.png?w=1200 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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.

awrc4http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc4.png?resi... 300w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc4.png?resi... 768w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc4.png?w=1200 1200w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc4.png?w=1800 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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:

awrc5http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc5.png?resi... 300w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc5.png?resi... 768w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc5.png?w=1200 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

The report then does comparisons for SGA, PGA, interconnects and even IO:

awrc6http://i1.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc6.png?resi... 300w, http://i1.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc6.png?resi... 768w, http://i1.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc6.png?w=1200 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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.

awrc7http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc7.png?resi... 300w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc7.png?resi... 768w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc7.png?w=1200 1200w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc7.png?w=1800 1800w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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.

awrc8http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc8.png?resi... 300w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc8.png?resi... 768w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc8.png?w=1530 1530w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/05/awrc8.png?w=1200 1200w" sizes="(max-width: 600px) 100vw, 600px" data-recalc-dims="1" />

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??

 



Tags:  , , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Database Comparisons with AWR Warehouse- Part II, Comparison Period Report], All Right Reserved. 2016.