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 11g’s new Database Replay features, and while understanding the theory behind a complex set of features is certainly a noble goal, it’s time to move on to the assist one / do one phases. This article will focus on how to:

• Capture an actual workload from an Oracle 11g database
• Capture the corresponding Automatic Workload Repository (AWR) data for that recorded workload
• Prepare the test database environment for eventual workload replay
• Transfer the production workload configuration to the testing environment
• Preprocess the production workload for replay
• Replay the workload on the test system
• Analyze the replayed workload for any performance issues or divergence

Simulated Application Environment

A word about my testing environment for this article: For simplicity’s sake, I’m going to use the same database for both capture and playback operations. I’m 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, I’ve constructed several new users, tables, indexes, and related PL/SQL objects:

• A new user, ADMIN, will be used as a repository for global administrative objects within my Oracle 11g database. I also created a new table for storage of primary key values. The code to reproduce these objects is shown in ADMINSetup.sql.
• PL/SQL package ADMIN.PKG_SEQUENCING controls assignment of new primary key values. The source code for its package specification is shown in pkg_sequencing.spc, and the initial version of its package body is shown in pkg_sequencing_v1.bdy.
• Another user, AP, will encapsulate the schema for a simulated Accounts Payable system, including new tables AP.VENDORS, AP.INVOICES, and AP.INVOICE_ITEMS. The code to create this new schema and its related objects is found in APSetup.sql.
• To populate AP schema objects, I’ve set up package AP.PKG_LOAD_GENERATOR. The source code for its specification and body can be found in pkg_load_generator.spc and pkg_load_generator.bdy, respectively.
• Finally, the code in APInitialization.sql loads table AP.VENDORS with a few hundred rows of simulated data, creates 25 new Invoices in table AP.INVOICES and corresponding Invoice line item detail in table AP.INVOICE_ITEMS, and gathers initial optimizer statistics for all objects in the ADMIN and AP schemas. It also creates a directory object, DBRControl, that Database Replay will use to store the resulting scripts and XML files generated during workload capture.

Setting Up for Workload Capture

Now that my source database’s environment is initialized, I’ll 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 I’ve selected the first task, I’m presented with a checklist that lists all prerequisite steps that I’ll 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 I’ll follow Oracle 11g’s recommended best practice for “clean captures”: I’ll accept EM’s suggestion to stop and restart the test database to establish an effective start time for the capture.

Next, I’m 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 workload’s capture. Figures 2.1.5 and 2.1.6 show the final confirmation screens for that task’s setup.

Finally, Oracle 11g requests one last confirmation ...

... and the capture is initiated. Once Oracle 11g displays this screen, it’s essentially waiting for me to start the representative workload against the source database.