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
» 
Comment and Contribute
