Database Reference
In-Depth Information
CHAPTER 28
■ ■ ■
The MERITS Performance
Optimization Method
T
he MERITS performance optimization method is built around a sophisticated assessment of
extended SQL trace files. The extended SQL trace profiler ESQLTRCPROF, which is capable of
parsing the undocumented trace file format, is used in the assessment phase of the method.
The MERITS method uses undocumented features predominantly in the assessment, repro-
duction, and extrapolation phases.
Essentially, the MERITS method is a framework for solving performance problems. The
goal of the method is to identify the root cause of slow response time and to subsequently
modify parameters, database objects, or application code until the performance goal is met.
Introduction to the MERITS Method
The MERITS performance optimization method is an approach to performance optimization
that consists of six phases and relies on undocumented features in several phases. MERITS is a
designed acronym derived from the following six phases of the method:
1.
M
easurement
2.
Ass
e
ssment
3.
R
eproduction
4.
I
mprovement
5.
Ex
t
rapolation
6.
In
s
tallation
The first step in any performance optimization project should consist of measuring the
application or code path that is too slow (phase 1). Measurement data are assessed in the second
phase. In some cases this assessment may already reveal the cause of the performance problem.
More intricate cases need to be reproduced by a test case, potentially on a test system (phase 3). If
a SQL statement takes excessive time to execute, then the test case consists of reproducing the
response time of the SQL statement. The fourth phase is concerned with improving the response
time of the application, code path, or test case. This may involve creating a new index, changing
371
