Oracle Database 11g: Database Replay, Part 2
May 30, 2008
Synopsis. Oracle Database 11gR1 offers the ability to capture applications workloads in a production environment and then replay that workload against a test environment to determine the impact of proposed system, database or application modifications on database performance. This article the second in this series demonstrates how Oracle 11g Database Replay can be used to capture and prepare a workload from a current Oracle 11g production database environment (P+0) and then replay that identical workload in an Oracle 11g testing environment that represents the next iteration (P+1) of the database system. This offers an Oracle DBA the chance to analyze and isolate the vectors that might cause detrimental performance or divergence between current and future environments well before any actual changes are rolled out.
A friend of mine who teaches future cardiologists describes the time-honored tradition under which surgeons learn their trade watch one, assist one, do one. The prior article in this series provided a primer into the theory and best practices behind Oracle Database 11gs new Database Replay features, and while understanding the theory behind a complex set of features is certainly a noble goal, its time to move on to the assist one / do one phases. This article will focus on how to:
Simulated Application Environment
A word about my testing environment for this article: For simplicitys sake, Im going to use the same database for both capture and playback operations. Im using the basic Oracle 11g seed database with all sample schemas installed. The database will be run in ARCHIVELOG mode with Flashback Logging activated so that I can use the FLASHBACK DATABASE command to quickly rewind the database to an appropriate starting point for replay.
Phase 1: Recording a Workload
To set up a valid capture and reply scenario, Ive constructed several new users, tables, indexes, and related PL/SQL objects:
Setting Up for Workload Capture
Now that my source databases environment is initialized, Ill initiate the capture of an actual workload. Figure 2.1.1 below shows the initial screen for Database Replay, which is accessed from the Database Replay link under the Real Application Testing section on the Software and Support tab of the EM Database Control facility:
Once Ive selected the first task, Im presented with a checklist that lists all prerequisite steps that Ill need to acknowledge before my capture session is allowed to proceed.
Oracle 11g next presents me the ability to either restart or not restart the database before officially capturing the workload, and it lets me filter out unnecessary session activity (e.g. from EM itself!). Note that Ill follow Oracle 11gs recommended best practice for clean captures: Ill accept EMs suggestion to stop and restart the test database to establish an effective start time for the capture.
Next, Im presented with the chance to name the capture session and specify the directory object into which all replay scripts will be captured.
Oracle 11g next needs me to specify the name of a scheduled EM Database Control task that will handle the workloads capture. Figures 2.1.5 and 2.1.6 show the final confirmation screens for that tasks setup.
Finally, Oracle 11g requests one last confirmation ...
... and the capture is initiated. Once Oracle 11g displays this screen, its essentially waiting for me to start the representative workload against the source database.