Phase 4: Regression Analysis
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:
these summaries illustrate, its obvious that the changes Ive made to the
databases 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. Its 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.
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
This report provides an Automatic Workload
Repository (AWR) report that summarizes and analyzes the databases
overall performance between the database replay operations starting and ending
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 databases Active Session History
(ASH) buffer during the execution of the replay operation.
the final article in this series, Ill 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, Ill target a two-node
Oracle 11g Real Application Cluster (RAC) clustered database as the testing
References and Additional Reading
Im hopeful that Ive given you a thorough grounding in the technical aspects
of the features Ive discussed in this article, Im also sure that there may be
better documentation available since its 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 Ive 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
See All Articles by Columnist Jim Czuprynski