| Craig S. Mullins
Database Performance Management
The DBA Corner
by Craig S. Mullins
Dissecting Database Performance
optimal performance is one of the biggest problems faced by DBAs on an
ongoing basis. The loudest complaints emanate from those users who
have to wait longer than they are used to waiting for their
applications to respond. This is to be expected because no one likes
to wait. Especially when they never had to wait in the past.
what causes those formerly fast applications to stall and deliver
sub-par performance? If there were an easy answer to that question
many DBAs would be out of work. Instead of attempting to answer that
question in-depth, letís examine the basics of performance
management and optimization that apply to all database applications.
database application, at its core, requires three components in order
to operate: the system, the database, and the application. To deliver
performance, the DBA must be able to monitor and tune each of these
components. This is easier said than done.
consists of the system software and hardware required for the
application to provide service. This includes the computer itself, its
disk subsystems, network connections, and all peripherals. From a
software perspective the system includes the operating system, the
file system, the DBMS itself, networking protocols, and any related
middleware such as transaction processors or message queues.
deliver system performance the DBA must have the resources to monitor,
manage and optimize the performance of these disparate pieces of
hardware and software. Some of the tasks required for system tuning
include the proper allocation and management of memory structures
(e.g. buffer pools, program cache area, etc.), storage management,
integration of the DBMS with other system software, proper usage of
database logs, and coordination of the operating system resources used
by the DBMS. Additionally, the DBA must control the installation,
configuration, and migration of the DBMS software. If the system is
not performing properly, everything that uses the system will perform
poorly. In other words, a poorly performing system impacts every
second component is the database. The database
stores the data that is used by the application. When the application
needs to access data, it does so through the DBMS to the database of
choice. If the database is not optimally organized or stored, the data
it contains will be difficult or slow to access. The performance of
every application that requires this data will be negatively impacted.
time, as data is modified and updated, the DBMS may have to move the
data around within the database. Such activity causes the data to
become fragmented and inefficiently ordered. The longer the database
remains online and the more changes made to the data, the more
inefficient database access can become. To overcome disorganized and
fragmented databases the DBA can run a reorganization utility to
refresh the data and make the database efficient once again. But the
key to successful reorganization is to reorganize only
when the database requires it; instead, some companies
over-reorganize by scheduling regular database reorganization jobs to
be run whether the database is fragmented, or not. This wastes
valuable CPU cycles.
reorganization is only one of many database performance tasks
performed by the DBA. Others
include data set placement, partitioning for parallel access, managing
free space, and assuring optimal compression.
third, and final, component of database performance is the application itself.
Indeed, as much as 80% of all database performance problems are caused
by inefficient application code. The application code consists of two
parts: the SQL code and the host language code in which the SQL is
is simple to learn and easy to start using. But SQL tuning and
optimization is an art that takes years to master. Every DBMS provides
a method of inspecting the actual access paths that will be used to
satisfy SQL requests. The DBA must be an expert at understanding the
different types of access paths, as well as which ones are best in
which situation. Furthermore, the DBA must be able to interpret the
output of the access path explanation produced by the DBMS, since it
is often encoded and cryptic.
language code refers to the application programs written in C, COBOL,
Java, Visual Basic or the programming language du jour. It is quite
possible to have finely tuned SQL embedded inside of inefficient host
language code. And, of course, that would cause a performance problem.
The Bottom Line
must understand all three aspects of database performance management.
Furthermore, the DBA must be able to identify problems when they
occur. Once identified, the problem must be analyzed to determine its
cause. And only then can a proper tuning strategy be deployed to
rectify the problem.
Trends and Applications, October 2001.
© 2001 Craig S. Mullins, All rights reserved.