Database Reference
In-Depth Information
decide whether the tuning effort can be stopped or not. In case tests in the previous phase were
run with instrumentation or SQL trace enabled, these should be switched off now. Measure-
ment intrusion is undesirable when trying to get reliable figures to base a forecast on. After all,
the original application usually runs without measurement intrusion through instrumentation
or tracing. On the other hand, if the original application runs with instrumentation enabled, so
should your test case.
Installation
Usually, changes are made in test and quality assurance systems first before they are allowed
to go into production. If the root cause was inefficient SQL coding or application coding in
general, it may take quite a while before the software manufacturer incorporates the necessary
changes and releases a new version of the software. In addition to approval, changes that require
reorganization of database objects need to wait for a sufficiently large maintenance window. If
there is no budget for an extra-cost feature that was used in the improvement phase (e.g., parti-
tioned tables), then it may be hard to get approval for the suggested changes. Furthermore it
may require additional downtime to install the feature.
MERITS Method Case Study
This section presents the application of the MERITS method to a real-world performance
problem. A digital imaging company accepts JPEG image files for printing and archival from
customers. A routine within the company's web application, which is implemented in Perl,
reads the EXIF image metadata in the digital still camera files and loads the image metadata
along with the images themselves into an ORACLE database. The EXIF data are used by search
functionality provided on the web site. The image data is stored as a
BLOB
, since this provides
full recoverability in case of disk failure. The average size of the JPEG files loaded is 1 MB. The
contact says that the application is capable of loading 68 files per minute. The goal is to at least
triple the number of files loaded per minute.
I have chosen this example, since it highlights some limitations of extended SQL trace. The
striking discrepancy between the response time calculated from the extended SQL trace file
and the actual elapsed time you will see shortly, should not lead you to believe that analysis
of extended SQL trace files is generally inaccurate. Such a high discrepancy is the exception,
rather than the rule. In this case it is due to incomplete instrumentation of LOB access with
OCI. The lack of accuracy observed in the response time profile for this case provides the
opportunity to endow the reader with additional tools and knowledge to overcome such situa-
tions. In this particular case, I show how instrumentation combined with statistics collection
at the action level offered by Oracle10
g
results in an accurate representation of response time
per action (
V$SERV_MOD_ACT_STATS
). In case you were still running Oracle9
i
, you would be able
to derive the elapsed time per action from the SQL trace file by looking at the timestamps that
are written with the module and action entry in Oracle9
i
(see page 249). This is not as good as
the data provided by the view
V$SERV_MOD_ACT_STATS
in Oracle10
g
, which include DB time, DB
CPU, and other statistics, but it's sufficient to find out where an application spends most of
the time.
