1. INTRODUCTIONDatabase administrat

1. INTRODUCTION
Database administrators can call on a variety of tools to help with
physical configuration ([7]-[14]), system monitoring and
maintenance ([20]-[23]).
Current automatic physical tuning tools have become
sophisticated. Given a representative workload of SQL
statements; they find the best physical design for the workload.
They do this based on tight interaction (what-if analysis [11] or
instrumentation [10]) with the cost-based query optimizer.
Beyond that effort in automatic physical design, Oracle’s SQL
Tuning advisor [18] can collect statistics, correct system
parameters, and recommend changes to SQL statements. (In the
running example of this paper, the Tuning Advisor found high
load SQL statements and identified bad query features like
Cartesian products.) Such tools work at the SQL statement level,
aiming to find beneficial physical structures to the SQL workloads
or to spot the problematic SQL statements. AppSleuth, as a
database tuning tool at the application level, can incorporate and
work with such tools to offer better performance tuning
suggestions to users.
Self-tuning memory management in database systems has also
gained much attention. Reference [1] proposes adaptive memory
allocation in DB2 based on monitoring the characteristics of the
workload during run time. Other commercial products also have
implemented self-tuning memory management facilities to
improve the performance of the database systems ([3], [4]).
DBMS designers have worked hard to make the internals selftuning
and self-managing. Since how the internals work
adaptively is beyond the control at the application level,
techniques in this category are orthogonal to that of AppSleuth.
AppSleuth works at the application code level. In our tuning
consulting experience, changing application code can lead to 2 to
100 times performance improvements. Thus, tuning efforts at
different levels can combine to obtain the best performance for
database applications.
Oracle 11g has a helpful feature called Hierarchical Profiler [18]
which can profile PL/SQL program executions with the number of
calls and the elapsed time of subprograms and SQL statements.,
Hierarchical Profiler differentiates self time from descendant time
within caller-callee relationships. The package offers analysis of
the raw profile data and generation of a group of reports in HTML
format. That achieves the same functionality as a subset of what
we have in our analysis of SQL trace below.
Database applications’ code run in two different contexts: the
programming context, which deals with programming logic in
languages like Java or Python, and the database context, which
entails database accesses, such as SQL statement processing,
stored procedure calls etc. Frequent switches between the two
contexts will hurt performance seriously. Reference [37] proposes
a way to partition database applications into two parts: one part
runs on the application server, the other part runs on the database
server. The goal is to minimize the roundtrips between the two
servers while retaining the semantics of the original application.
The proposal does an elegant job of allocating proper burdens
onto eligible servers. But such optimized separation at the server
level deals with only one of the factors that affect performance.
One needs to look at the application level and its many
“delinquent” design patterns.
* Work done while the author visited New York University under the support of
China Scholarship Council’s Graduate Education Program, and later partially
supported by Natural Science Foundation of China (No. 61202331, 60170013,
60833005,61070055, 91024032, 91124001), and the National 863 High-tech
Program (No. 2012AA010701, 2013AA013204).
† Work supported by U.S. National Science Foundation grants 0922738, 0929338,
1158273
Permission to make digital or hard copies of all or part of this work for
personal or classroom use is granted without fee provided that copies are
not made or distributed for profit or commercial advantage and that copies
bear this notice and the full citation on the first page. To copy otherwise,
or republish, to post on servers or to redistribute to lists, requires prior
specific permission and/or a fee.
EDBT/ICDT’13, March 18–22, 2013, Genoa, Italy.
Copyright 2013 ACM 978-1-4503-1597-5/13/03…$15.00.