Integrating Workload Replays into Database Change Management

stickyraffleΛογισμικό & κατασκευή λογ/κού

4 Νοε 2013 (πριν από 3 χρόνια και 5 μήνες)

52 εμφανίσεις

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
will

remain mostly the same (i.e.
application
performance will
not degrade significantly).

Yet there are so many factors that can affect ones’ database


including:

1.

Hardware upgrades, virtual resource re
-
allocation, or virtual machine relocation

2.

Software upgra
d
es (e.g. new operating system or

update to virtual machine software)

3.

Operating system and/or virtual machine configuration and/or initialization parameters

4.

Database patches and/or upgrades (e.g. 10.2.0.3
-
>10.2.0.4 or Oracle 10gR2
-
>11gR1)

5.

Database configur
ation and/or initialization parameters (e.g. SGA/PGA memory allocations)

6.

Database structural changes (e.g. add/drop
/alter

tables,
columns,
indexes and views)

7.

Storage nature changes (e.g. start using partitions, sub
-
partitions, index organized tables
, etc
)

8.

New database feature usage (e.g. start using 11G optional OEM “
auto
plan stability” features)

9.

Statistics and/or histogram collection frequency, granularity, parameters and/or method

10.

Relative size of the database (e.g. most databases grow over time


some
exponentially
!
)

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
the results.

Nor can you postpone, prev
ent or otherwise significantly affect many of the above issues.

T
he answer is to
tightly
integrate
database application “
workload replay


into your databas
e change
management procedures



read on to see why.

Let’s say you’re using TOAD with
the optional DB

Admin

Module to perform database compare and sync
operations to effect basic
database
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
e
resulting CREATE and/or ALTER SQL scripts.

Either way, that would only
really
c
over the very basics for
scenarios #6

and #
7

from above.

That leaves quite a bit of room for error


and surely more than most
SLA’s or users are going to accept.

What’s
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:

Scientific me
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
having some
reliable
way to repeat something and quantify the variances.

So in the case of a database
application,

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 “
Is
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
yes

and it’s a
n Oracle

feature that

many DBA’s already are quite familiar with


trace files
.

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 “
BMF
”). Benchmark Factory comes as
part of
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
real.

Let’s detail an example of how o
ne can use Benchmark Factory to accomplish workload reply.


STEP 1


Capture the Workload


If you simply want to capture the workload for anywhere from a single session to a small group of
sessions, the
n

you can very simply use TO
A
D”s

Session Browser


screen
as shown below to both
initiate and terminate Oracle collecting trace files for them.

Remember


Oracle collects all those trace
files on the database server and not
on
your local PC (unless you’re using a local PC database that is).

Furthermore, wh
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


just
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
unstructured data.

But as the
old
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
some trace
file

parameters


(e.g
. the maximum allowable size for the trace file
).

You must include
both

the ”T
imed
S
tatistics


and
“Include B
ind
s” ch
eck boxes

as shown here
.


Now you simply let that session run and collect its trace file. When it’s done
the database

will close the
completed trace file, and of course you can choose to terminate the session’s
trace collection anytime
as well.

But what

if you wanted to collect
trace files for
all the session
s

within the database? That’s actually
quite easy

too
.

Simply
run
one
the following
appropriate
command
s

while
connected
as a privileged
DBA type user

and you’ll start collecting trace files for ever
y new session within your database
:

For non
-
RAC:

DBMS_MONITOR.DATABASE_TRACE_ENABLE

For RAC:

DBMS_MONITOR.DATABASE_TRACE_ENABLE (instance_name =>
‘$
SID

)

Finally, what if you had instead wanted to collected trace files for specific sessions as defined by
some
user defined rules. Then you would need to add a database level event trigger something like this:

CREATE

OR

REPLACE

TRIGGER

AUTO_SET_TRACE

AFTER

LOGON

ON

DATABASE

BEGIN


IF

(USER

IN

(
'MOE'
,
'LARRY'
,
'CURLY'
))

/* ONLY TRACE FOR THE THREE STOOGES */



THEN



EXECUTE

IMMEDIATE

'ALTER SESSION SET tracefile_identifier = ''XXXXX'''
;


EXECUTE

IMMEDIATE

'ALTER SESSION SET max_dump_file_size = ''10M'''
;


DBMS_SESSION.SESSION_TRACE_ENABLE

(
waits
=>

FALSE,

binds
=>

TRUE);


END

IF;

EXCEPTION


WHEN

OTHERS

THEN


RAISE_APPLICATION_ERROR
(
-
10001
,
'AUTO_SET_TRACE ERROR'
);

END;


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.


STEP 2


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
gather
all
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).

If it
had been on a remote server then I would simply have used TOAD’s FTP utility to copy it down.




STEP 3


Inspect TRACE File using TOAD


Strictly speaking this step is purely optional. But sometimes it helps to inspect and verify the trace file
c
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


and
not too user friendly.

Once again TOAD comes to the rescue


via the TOAD Trace File Browser
utility shown
here
below. You

simply open the local copy of the trace

file and TOAD
mines and presents that data as information


and
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

Cary Millsap



Method
-
R


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
-
two punch.



STEP 4


Create BMF Project for Replay


OK


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
manual
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
for execution
as shown here.

This is by far the easiest and most direct method for setting up
trace file
workloa
d
replay scenarios.



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
launches the

Load Scenario Wizard


shown here below. Note that the fifth choice, “Replay load from an
Oracle Trace file”, creates a
new
BMF project that replays the captured workload from a

set of user
defined
Oracle trace file
s
. That’s how amazingly simple it is


there’s a specific featu
re for performing
this exact request.


The

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


shown here:




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.


STE
P 5


Replay the Captured Workload


Once you’
ve completed running this load scenario creation wizard,
you
’ll

end up with a completed BMF
project like the one shown here, and to replay it you simply push the
toolbar
button to

”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
ster (meaning
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).




STEP 6


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
simple
is a misnomer). To monitor and compare
the database performance between runs you need a true diagnostic and/or monitoring tool


something
like Quest’s Spotlight for Oracle

(also included in the Toad DBA Suite for Oracle)

or Performance Analysis
for

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).




Of cour
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.


And
There’s More

if You Need


For many people the example shown here will
exactly and perfectly
suffice


that
is they simply need to
perform

a
database workload
capture and playback where nothing changes.

For that
(and just that)
both
BMF and Oracle RAT are
p
retty much
alike. However what if you needed to capture a workload and then
replay it at 150%
,

200%
, 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 “
what if
” type
or scenario
analysis.

Finally what
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


but without
having to make the database change to see the effect. In all these examples (and many
more variations),
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


where
the only limits are your ima
gination and ability or skills to modify that BMF project. For example you ca
n
easily modify the captured workload
for
replay by adding new user scenarios, adding new transactions,
or changing
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
even
contextually smart or sensitive
substitution variables or macros


such as

the example shown below
,
where I’ve modified the SELECT statement’s WHERE to use
a valid state randomly selected state
at run
time rather than t
he value that was captured. It’s that easy to

change


and the results can be amazing.




Conclusion


Databases change


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
li
ver of the reasons why a database might
change. So it’s
next to

impossible to
me
e
t service level agreements and user expectations when these
changes occur because something is
sorely
missing from the equation. That something is the ability to
ca
pture and replay

live database

workloads before and after the
database
changes
are made
such that a
scientifically sound
method to
observe and
quantify
any
variances

permits one to judge the results as
being positive or negative.

With the
Toad DBA Suite fo
r Oracle

you get all the tools necessary to perform
such testing


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
academic background
-

including bac
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,
including O
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
VMware.