Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 30, 2008

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

By Jim Czuprynski

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:

Click for larger image

Figure 2.5.1: Replay Regression Analysis, Part 1

Click for larger image

Figure 2.5.2: Replay Regression Analysis, Part 2

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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM