DBA Call to Action: Zeroing in on Performance Problems

Have you ever been asked to
help with the evaluation of a database that is not performing optimally? Here
is a quick guide to help you if you just do not know where to start.

Thrown Into the Fire

In my early days of being a database administrator, I was
thrown straight into the fire of database performance issues. Now, I did not
have any experience, except for the education of database systems from my local
college, and was at the mercies of other professionals around me. The problem
was that many of them did not have much experience either. I would often asked
myself why in the world someone would ask me to work on database systems that
were the lifeline of their company and would trust someone with such little
experience. The reason they let me ‘play’ with their systems was that they were
at my mercy combined with a few things happening with their database systems in
general. Users were beginning to ask more and more of their databases, they
were storing more and more data, the amount of people with real credentials to
tune a database was low and computer hardware was still not quite robust enough
for the solutions in which they were asked to take part. Today, all of these
issues are still true for the most part except for computer hardware. The
hardware of today can mask the performance problems of the typical database
environment for quite some time. The simplistic nature of buying hardware,
throwing a database on it, creating objects to store the information, and users
creating applications against the database does create a false sense of
stability that typically will only break down over time when true scalability
of the system comes into question.

What does this all mean? Well, if you stick in this field of
database administration long enough, you will in due time be faced with the
issue of having to investigate a performance problem. What makes matters worse
is that no one will typically know where the problem arose from or where to
start. It is your duty as a database administrator of the system to track down
the problem so that you can effectively come up with a solution. So where do
you start? This article will give you an approach to use where you can begin to
investigate the problem and hopefully come up with your own feelings and
convictions about what must be done with this highly stressful and possibly
confrontational issue.

User Input

Do not underestimate the information the users of database
systems have when first determining the problem with a system that is not
performing well. It is true they cannot tell you anything about memory
consumption, how the data is laid out across your storage subsystem or how an
application is written, however, what they can do is verbally explain to you
the pain they are experiencing when they use the system. They have a unique and
distinct experience that can help you zero in on the use cases that are
experiencing problems. Zeroing in on the events or tasks that cause the problem
will help you later down the path when you want to re-create the problem. These
problems are what you should concentrate on, and since they are the pain points
experienced by users, should be your first line of attack to begin easing the
perceived performance problems. You may still have many issues that are
occurring that you will need to fix but if some of your users are happy you
will have a better, and less stressful, chance of sleep at night.

When talking to users, it is very important for you to have
a set of questions that you can ask to extract all possible information on the
system from these users. In Listing 1, I have given you a few questions that
you can incorporate when you talk to the users of the system. The idea you
should come away with is that you need to get as much information as possible
about how users are feeling about the system while instilling in them the importance
of the problem to you and your commitment to getting it resolved. In addition,
you will notice that these questions are geared to extract information about
how the system is behaving now and relating it to how the system has behaved in
the past. By doing this you will begin to get an idea for the change of events
that might have happened between a system performing properly and poorly from
the users’ perspective.

Listing 1
Questions you can ask your users

In you own words, explain the problem.

When did you first experience the problem?

Does this problem always occur?

Is anyone else experiencing the problem?

Are you still able to do your work?

Compare and contrast the time it takes for you to complete the task when
you experience a problem with when you would not experience the problem.

Did the problem immediately manifest itself or was it gradual?

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles