ABSTRACT
Excellent work ([1]-[6]) has shown that memory management and
transaction concurrency levels can often be tuned automatically
by the database management systems. Other excellent work ([7]]-
[14]) has shown how to use the optimizer to do automatic
physical design or to make the optimizer itself more self-adaptive
([15]-[17]). Our performance tuning experience across various
industries (finance, gaming, data warehouses, and travel) has
shown that enormous additional tuning benefits (sometimes
amounting to orders of magnitude) can come from reengineering
application code and table design. The question is: can a tool help
in this effort? We believe so. We present a tool called AppSleuth
that parses application code and the tracing log for two popular
database management systems in order to lead a competent tuner
to the hot spots in an application. This paper discusses (i)
representative application "delinquent design patterns", (ii) an
application code parser to find them, (iii) a log parser to identify
the patterns that are critical, and (iv) a display to give a global
view of the issue. We present an extended sanitized case study
from a real travel application to show the results of the tool at
different stages of a tuning engagement, yielding a 300 fold
improvement. This is the first tool of its kind that we know of.