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 ADMINSetup1.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_sequencing1.spc, and the initial version of its package body is
shown in pkg_sequencing_v1a.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 APSetup1.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_generator1.spc
and pkg_load_generator1.bdy,
respectively.
- Finally, the code in APInitialization1.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:
Figure 2.1.1: Workload Capture Setup: Initial Screen
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.
Figure 2.1.2: Workload Capture Setup: Plan Environment Checklist
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.
Figure 2.1.3: Workload Capture Setup: Options
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.
Figure 2.1.4: Workload Capture Setup: Setting Parameters
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.
Figure 2.1.5: Workload Capture Setup: Assigning EM Task Name
Figure 2.1.6: Workload Capture Setup: Final Task Review
Finally,
Oracle 11g requests one last confirmation ...
Figure 2.1.7: Workload Capture Setup: Task Submission
... 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.
Previous
Next
Back to DBAsupport.com
