Oracle Database 11g: Database Replay, Part 2 - Page 4

May 30, 2008

Phase 4: Regression Analysis

Oracle 11g gives me several tools for comparing the captured vs. replayed workloads. As shown in Figures 2.5.1 and 2.5.2 below, the most succinct comparison is the one that Database Replay provides immediately after a successful replay operation has completed:

As these summaries illustrate, it’s obvious that the changes I’ve made to the database’s objects have had an overall positive effect because the time it took to execute the captured workload is significantly longer than the time it took to replay the identical workload, and that immediately indicates that the system modifications have positively increased database throughput. It’s just as important to note, however, that there were no deleterious effects introduced. This is easiest to see in the second half of the regression analysis screen because there is absolutely no divergence between the generated data, and there were no unexpected errors generated.

Reporting Analyses

Finally, Database Replay offers several reports in HTML format that analyze the results of the completed database replay operation:

• DB Replay Report. This report compares the execution of the captured workload to that of the replayed workload, and it searches for the source of any possible data and/or error regressions.
• AWR Report. This report provides an Automatic Workload Repository (AWR) report that summarizes and analyzes the database’s overall performance between the database replay operation’s starting and ending time periods.
• ASH Report. At an even lower level of detail, this report shows exactly which SQL statements and wait events caused the largest impact on database performance by looking at the contents of the database’s Active Session History (ASH) buffer during the execution of the replay operation.

Next Steps

In the final article in this series, I’ll expand on the scenario that played out in this article by tackling one of the thornier concerns most Oracle DBAs have for systems that are about to migrate to Oracle 11g: How will my current database perform in a brand-new environment? And to make it even more relevant and interesting, I’ll target a two-node Oracle 11g Real Application Cluster (RAC) clustered database as the testing environment.

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11gR1 documentation for the deeper technical details of this article:

B28274-01 Oracle Database 11gR1 Performance Tuning Guide

B28279-02 Oracle Database 11gR1 New Features Guide

B28419-02 Oracle Database 11gR1 PL/SQL Packages and Types Reference

» See All Articles by Columnist Jim Czuprynski