Integrating Workload Replays into Database Change Management
There is one universal database constant
that no matter what, your database will evolve. That is it will
change over time. Yet often the Service Level Agreement (SLA) and user expectations will be that things
that they experience
and care most about
remain mostly the same (i.e.
not degrade significantly).
Yet there are so many factors that can affect ones’ database
Hardware upgrades, virtual resource re
allocation, or virtual machine relocation
es (e.g. new operating system or
update to virtual machine software)
Operating system and/or virtual machine configuration and/or initialization parameters
Database patches and/or upgrades (e.g. 10.2.0.3
>10.2.0.4 or Oracle 10gR2
ation and/or initialization parameters (e.g. SGA/PGA memory allocations)
Database structural changes (e.g. add/drop
indexes and views)
Storage nature changes (e.g. start using partitions, sub
partitions, index organized tables
New database feature usage (e.g. start using 11G optional OEM “
plan stability” features)
Statistics and/or histogram collection frequency, granularity, parameters and/or method
Relative size of the database (e.g. most databases grow over time
So how can a DBA reasonably ensure that the before and after user experience remains the same? You
cannot simply say that the above conditions were unavoidable and hence we’ll simply have to live with
Nor can you postpone, prev
ent or otherwise significantly affect many of the above issues.
he answer is to
database application “
into your databas
read on to see why.
Let’s say you’re using TOAD with
the optional DB
Module to perform database compare and sync
operations to effect basic
change management. Let’s further assume that you’re using either
TOAD’s “.DEF” files (offline copies of data dictionary) or standard source code control software with th
resulting CREATE and/or ALTER SQL scripts.
Either way, that would only
over the very basics for
That leaves quite a bit of room for error
and surely more than most
SLA’s or users are going to accept.
missing here is what is both “scientific method” and “scientific control”
specifically the ability
to measure the “before” and “after” results to formulate a conclusion (i.e. better or worse). Wikipedia
defines scientific method as follows:
thod refers to a body of techniques for investigating phenomena, acquiring new
knowledge, or correcting and integrating previous knowledge. To be termed scientific, a method
of inquiry must be based on gathering observable, empirical and measurable evidenc
e subject to
specific principles of reasoning. A scientific method consists of the collection of data through
observation and experimentation, and the formulation and testing of hypotheses.
The key phrase is “…gathering observable, empirical and measureabl
e evidence…” What that means is
way to repeat something and quantify the variances.
So in the case of a database
The DBA is looking for the ability to run a sample subset of a realistic workload before and
after the chang
es are made
with the goal to see if things are better (you keep) or worse (you rollback).
Now you might be thinking “Hey that sounds just like Oracle 11g’s Real Application Testing or RAT!” But
RAT is an optional and fairly expensive add
on that you ma
y not have budget for. Plus RAT’s granularity
is really at the entire database level. In some cases that might be exactly what you really need or desire.
But for many it’s overkill or simply not an option due to financial reasons. So the question becomes “
there some way to capture and replay Oracle database application workloads without additional cost,
that’s not overly complex, and yet that’s reliable enough for reasonable before and after comparisons?”
The answer is
and it’s a
many DBA’s already are quite familiar with
The Oracle database provides a wonderful instrumentation capability known as tracing session. This can
be turned on at the database or specific session level. And when the “bind variables” are incl
uded in the
trace, you have a perfect log of the database activity or workload. All that’s needed is a simple way to
replay those captured trace files on a target database
whether the same database after some change
or another database (e.g. dev vs. test
As before let’s say you’re using
Toad DBA Suite for Oracle
, then guess what
you have the basic tools to
perform basic workload replay. The Quest Software product that can replay an Oracle trace file or files is
called Benchmark Factory (abbreviated an
d often referred to as “
”). Benchmark Factory comes as
Toad DBA Suite for Oracle
or it can be purchased by itself. Either way the cost is negligible. Plus
it’s a simple client based tool that one can easily experiment with and learn on their PC.
In fact I’ve seen
some developers and DBA’s perform the initial, rudimentary “before” and “after” tests on their local
database under the assumption that it better work on a minimal machine before attempting anywhere
Let’s detail an example of how o
ne can use Benchmark Factory to accomplish workload reply.
Capture the Workload
If you simply want to capture the workload for anywhere from a single session to a small group of
you can very simply use TO
as shown below to both
initiate and terminate Oracle collecting trace files for them.
Oracle collects all those trace
files on the database server and not
your local PC (unless you’re using a local PC database that is).
ere trace files are collected is both Oracle version and parameter setting dependent.
However the trace files are merely ASCII text files, so there is nothing special about them really
that they contain detailed internal database “engine execution”
instrumentation data. And I say data
because you’ll really need some kind of tool to assist interpreting the massive amounts of seemingly
But as the
prospector said “
There’s gold in them thar hills
When you identify a session or sessions that you want to initiate the trace for, TOAD will prompt you for
. the maximum allowable size for the trace file
You must include
as shown here
Now you simply let that session run and collect its trace file. When it’s done
will close the
completed trace file, and of course you can choose to terminate the session’s
trace collection anytime
if you wanted to collect
trace files for
all the session
within the database? That’s actually
as a privileged
DBA type user
and you’ll start collecting trace files for ever
y new session within your database
DBMS_MONITOR.DATABASE_TRACE_ENABLE (instance_name =>
Finally, what if you had instead wanted to collected trace files for specific sessions as defined by
user defined rules. Then you would need to add a database level event trigger something like this:
/* ONLY TRACE FOR THE THREE STOOGES */
'ALTER SESSION SET tracefile_identifier = ''XXXXX'''
'ALTER SESSION SET max_dump_file_size = ''10M'''
Of course the more sessions you trace the more trace files you’ll have to work with. Also remember that
setting on trace scope too wide (such as for the entire database) could
slow your database down and/or
fill your trace/log file directory. So use caution and prudence.
Copy the TRACE file to your PC
Once you’ve identified all the sessions you want to trace and performed the actual tracing of their
workload, then yo
u’ll need to transfer those trace files to your PC. There are ways for you to force the
trace files to follow a user defined naming pattern
(as was done in the trigger above)
but very few
people use that Oracle feature it seems. So you’ll need to examine
the trace file sizes and dates to
the correct trace files.
Here’s an example of Oracle trace files on my local database, where
trace file 2908 is the one I want to work with (i.e. that I did the capture for the purpose of replay).
had been on a remote server then I would simply have used TOAD’s FTP utility to copy it down.
Inspect TRACE File using TOAD
Strictly speaking this step is purely optional. But sometimes it helps to inspect and verify the trace file
ontents before proceeding. What if you turned on the capture too early or too late? What if you had
forgot to include the bind values? While performing an Oracle trace is not really too tough, there are
nonetheless steps where one could make a mistake. Thu
s better to double check. But wait, I said the
trace files were packed full of raw data
not too user friendly.
Once again TOAD comes to the rescue
via the TOAD Trace File Browser
simply open the local copy of the trace
file and TOAD
mines and presents that data as information
pretty much like the Session Browser screen. With this facility, you can very quickly review and accept or
reject your trace files as having been captured properly. In fact if you’re a believ
er in the
approach to database performance tuning
then you can use TOAD’s Trace File Browser as
a key weapon in your tuning arsenal.
In fact the TOAD combination of the Session Browser for managing
trace and this browser makes f
or a formidable one
Create BMF Project for Replay
once you have the trace files captured, copied locally and inspected for appropriateness, you now
are prepared to use Benchmark Factory to create a BMF project for replaying
those trace files. And it’s
actually much easier than you might imagine.
For those like me who prefer the absolute easiest way with the least number of
steps, if you’ve
performed step 3
using the TOAD Trace File Browser utility, then you simply nee
d to press that screen’s
last toolbar icon (a pair of gears) to automatically package up the trace file
as a BMF project
and send it
off to BMF
as shown here.
This is by far the easiest and most direct method for setting up
But what if you need to create a BMF project for
trace file workload replay scenarios that contain more
than one trace file (i.e. a collection of trace files from multiple sessions over a period of time), then you
need to do just a
little more work
although not too much
You simply launch Benchmark Factory and push the first toolbar button (a gear with label of New) which
Load Scenario Wizard
shown here below. Note that the fifth choice, “Replay load from an
Oracle Trace file”, creates a
BMF project that replays the captured workload from a
set of user
Oracle trace file
. That’s how amazingly simple it is
there’s a specific featu
re for performing
this exact request.
next step of the wizard permits you to identify the local Oracle trace file or files that you desire to
execute as part of this BMF project, as shown here:
Benchmark Factory parses those trace files (which
might take some time) and then offers you some
options as to how to process them
In most cases you’ll simply choose to import the whole file
excluding the ‘SYS’ user. That’s pretty much
it. Remember since trace file collection has to
include the bind variable values (as was pointed out back
in step 1), the resulting trace files contain everything necessary to replay the workload
including all the
time dependency information for keeping the transaction timing and order the same.
Replay the Captured Workload
ve completed running this load scenario creation wizard,
end up with a completed BMF
project like the one shown here, and to replay it you simply push the
”Submit Job to
Jobs Queue”. Tha
t will then replay the workload at whatever database you decide to point to via your
database connection (BMF calls this the database profile
which is simply where you identify user id,
password and database instance).
BMF will then replay your captur
ed database workload exactly the same way as it ran before as shown
here. Remember that if your capture took an hour your replay will take approximately the same time. If
you’ve tuned the application and/or database BMF might process the workload replay fa
success) or slower (meaning worse). So be patient.
Also look into the Benchmark Factory feature called
“Run Reports” (also shown below). When you double click on this entry you can get detailed reports and
graphs about all your prior executio
ns (called iterations).
How to Measure “Before & After”
Now comes the hardest part
how do you monitor and measure relative results? Benchmark Factory
simply replays the workload (although to call the ton of work BMF does to re
process the trace file and
replay your transaction with proper ordering and timing
is a misnomer). To monitor and compare
the database performance between runs you need a true diagnostic and/or monitoring tool
like Quest’s Spotlight for Oracle
(also included in the Toad DBA Suite for Oracle)
or Performance Analysis
Oracle. Here’s an example of Spotlight’s wonderful dashboard showing all aspects of your database
architecture or technology stack
and their performance (using the simplistic traffic light style green,
yellow and red type coloration scheme).
se not everyone may have such user friendly tools available for doing their before and after
relative comparisons. In that case you could always use other tools like Oracle’s Stats Pack. You can take
a Stats Pack snapshot before and after each run to estab
lish a performance characteristic and score for
that iteration. Then you can compare the results between iterations to decide whether the results are
better or worse. Of course this technique will require more time and effort
but it’s doable.
if You Need
For many people the example shown here will
exactly and perfectly
is they simply need to
capture and playback where nothing changes.
(and just that)
BMF and Oracle RAT are
alike. However what if you needed to capture a workload and then
replay it at 150%
, or 300%
In other words you want to capture a workload pattern and then see
how it scales. Or what if you were happy with the capture but wanted to change
the bind values for the
SQL statements processed
such that you can perform “
if you also wanted to manually add something new to the mix
maybe some logic that says when doing
operation X also include this
new operation Y. An example might be to see what would happen if you
made a coding change or added a trigger where this new operation gets added to replay
having to make the database change to see the effect. In all these examples (and many
neither BMF as shown so far nor RAT fit the bill. This is where BMF offers some things that RAT does not.
The BMF project constructed from importing your Oracle trace files is totally user customizable
the only limits are your ima
gination and ability or skills to modify that BMF project. For example you ca
easily modify the captured workload
replay by adding new user scenarios, adding new transactions,
the transaction latencies as shown here below.
For example by
just reducing or minimizing
the latencies you can increase the relative workload against the server (i.e. same transactions but played
back with less delay in between equals more load).
Furthermore BMF offers a complete scripting language, global vari
ables at the project level, and
contextually smart or sensitive
substitution variables or macros
the example shown below
where I’ve modified the SELECT statement’s WHERE to use
a valid state randomly selected state
time rather than t
he value that was captured. It’s that easy to
and the results can be amazing.
and change for a myriad of reasons. Popular DBA tools for doing database and/or
schema compare and sync operations typically only
cover a s
ver of the reasons why a database might
change. So it’s
t service level agreements and user expectations when these
changes occur because something is
missing from the equation. That something is the ability to
pture and replay
workloads before and after the
such that a
permits one to judge the results as
being positive or negative.
Toad DBA Suite fo
you get all the tools necessary to perform
namely Benchmark Factory, a tool that can replay Oracle trace files with minimal input.
About the Author
Bert Scalzo is a domain expert for Quest Software and a member of the Toad developme
nt team. He has
worked with Oracle databases for well over two decades. Prior to joining Quest, Bert worked for both
Oracle Education and Oracle Consulting. He holds several Oracle Masters certifications and an extensive
helor’s and master’s degrees, and Ph.D., in computer science, as
well as an MBA and insurance industry designations. He is also an Oracle ACE.
Bert is also accomplished speaker who has presented at numerous Oracle conferences and user groups,
OW, ODTUG, IOUG, OAUG, and RMOUG. His key areas of DBA interest are data modeling,
database benchmarking, database tuning and optimization, "star schema" data warehouses, Linux, and