Database Comparisons Using AWR Warehouse Part 1- ADDM Comparison

A lot of my ideas for blog posts come from questions emailed to me or asked via Twitter.  Today’s blog is no different, as I was asked by someone in the community what the best method of comparing databases using features within AWR when migrating from one host and OS to another.

There is a  lot of planning that must go into a project to migrate a database to another host or consolidate to another server, but when we introduce added changes, such as a different OS, new applications, workload or other demands, these need to be taken into consideration.  How do you plan for this and what kind of testing can you perform to eliminate risk to performance and the user experience once you migrate over?

AWR Warehouse

I won’t lie to any of you, this is where the AWR Warehouse just puts it all to shame.  The ability to compare AWR data is the cornerstone of this product and it’s about to shine here again.  For a project of this type, it may very well be a consideration to deploy one and load the AWR data into the warehouse, especially if you’re taking on a consolidation.

There are two main comparison reports, one focused on AWR, (Automatic Workload Repository) data and the other on ADDM, (Automatic Database Diagnostic Monitor).

From the AWR Warehouse, once you highlight a database from the main dashboard, you’ll have the option to run either report and the coolest part of these reports is that you don’t just get to compare time snapshots from the same database, but you can compare one snapshot from a database source in the AWR Warehouse to ANOTHER database source that resides in the warehouse!

ADDM Comparison Period

This report is incredibly valuable and offers the comparisons to pinpoint many of the issues that are going to create the pain-points of a migration.  The “just the facts” and crucial information about what is different, what has changed and what doesn’t match the “base” for the comparison will be displayed very effectively.

When you choose this report, the option to compare from any snapshot interval for the current database is offered, but you can then click on the magnifying glass icon for the Database to compare to and change to compare to any database that is loaded into the AWR Warehouse-

 

For our example, we’re going to use a day difference, same timeline to use as our Base Period.  Once we fill in these options, we can click Run to request the report.

The report is broken  down into three sections-

• A side by side comparison of activity by wait event.
• Details of differences via tabs and tables
• Resource usage graphs, separated by tabs.

We can clearly compare between the two comparisons of activity that there was more commit waits during the base period, along with user I/O in the comparison period.  During a crisis situation, these graphs can be very beneficial when needed to show waits to less technical team members.

The Configuration tab below the activity graphs will display quickly what differences in OS, initialization parameters, host and other external influences to the database.  The Findings tab will then go into the performance comparisons differences.  Did the SQL perform better or degrade?  In the below table, the SQL ID, along with detailed information about the performance change is displayed.

Resources are the last tab to display graphs about the important area of resource usage.  Was there an impact difference to CPU usage between one host and the other?

Was there swapping or other memory issues?

In our example, we can clearly see the extended data reads and for Exadata consolidations, the ever valuable single block read latency is shown-

Now for those in engineered systems and RAC environments, you’re going to want to know waits for interconnect.  Again, these are simply and clearly compared, then displayed in graph form.

This report will offer very quick answers to

“What Changed?”

“What’s different?”

“What Happened at XXpm?”

The value this report provides is easy to see, but when offered to compare one database to another, even when on different hosts, you can see how valuable the AWR Warehouse becomes that even the consolidation planner can’t offer.

Next post, I’ll go over the AWR Warehouse AWR Comparision Period Report.