A DBA's role often revolves around tuning the database. This in itself can be a very daunting task. A checklist of DBA tasks can be quite handy. James Koopmann shares some performance tuning activities that a DBA needs to know and that will often be part of a solution to remedy a database performance problem.
Ok, so you landed yourself a DBA job, maybe you've been promoted, or you
just had the role pushed into your lap because money is tight and your company
can't hire a "real" DBA. Whatever the reason we all know, aside from
creating tables, a DBA's role often revolves around tuning the database. This
in itself can be a very daunting task. Where do you start, where do you go and
how do you know you are doing it right? All these questions are quite
reasonable and are surely one reason why Oracle has put out a series of 2-day
type guides that can help guide you through your newly found set of tasks. This
article and some to follow, we will being looking at one of those guides, the
2-Day + Performance Tuning Guide, and attempting to help guide you through the
process of tuning your database. Granted, this 2-Day + Performance Tuning Guide
does not give an exhaustive methodology of tuning an Oracle Database but it is
a good place to start.
Probably one of the more common questions from new DBAs has to be wrapped
around what tasks are involved with keeping an Oracle Database running
smoothly. After all you won't always have someone pounding on your door telling
you what problems they are having and it is up to you to somewhat sniff out
those performance type problems. To do this, checklists or a set of DBA tasks
can become quite handy. While many of the following items in the following
checklist are not actual performance-tuning activities they are tasks that a
DBA needs to know and they will often be part of a solution to remedy the
performance problem.
Verify that an instance is up and running - clearly this
can lead to performance issues (lack of any performance) and a DBA must
know how to start and stop an instance as well as check to see if it is
available.
Look for alert log entries - the alert log is often your
keys to the engine, as it will report errors / problems that are being
encountered. Being able to quickly find alert log messages and relate them
to performance problems is paramount.
Verify backups are running and successful and be able to
perform various recoveries - backups are your lifeline to ensuring data is
protected. Verifying a backup has completed successfully enables you to
validate that you can recover and that a currently running or stuck backup
is not causing performance problems.
Verify disk usage - I've seen many databases come to a
complete halt because disk storage was limited for audit, trace, alert,
database, and archive log files. Make sure you know how to check this
quickly. Clearing up or allocating space is even more important.
Check for memory and CPU resource issues - lack of memory
or CPU resources over time can develop and we as DBAs need to be able to
not only view these resources but we need to relate them to current trends
in workloads.
Identify growth patterns within the database - this is very
similar to verifying disk usage but the growth patterns within a database
are sometimes different from externally on disk. Make sure you are able to
grasp how various database structures grow so that you can plan, if
needed, for the future.
Be able to install or patch the Oracle software - some
performance issues are rooted in the Oracle software itself. Be able to
install and patch the Oracle software for the purposes of testing
persistence of tuning issues across versions.
Be able to create and upgrade Oracle databases - same
issue as the Oracle software.
Be able to extract and manage user accounts and security -
finding out who is doing what, what authorizations they have, and trimming
access can often reduce rambunctious users from doing too much within the
database and driving up resource usage.
Be able to manage objects like tables, indexes, view,
triggers, etc. - improperly built objects such as views, indexes,
triggers, procedures, etc. can have dramatic impact on query performance.
Solve these problems and many performance issues will go away as well.
Monitor object usage - determining if objects are being
used, such as indexes, can shed light on a multitude of transaction type
issues. All too often indexes are ill-placed or go out-dated. Keeping
index structures clean, as well as other objects, makes for a tight
application environment.
Monitor various performance ratios - while these ratios
have gotten bad press in the past they are still quite valid in helping
find areas that could provide potential tuning opportunities. Take a look
at the big picture and then dive down into the details.
Monitor I/O contention - too many systems are developed
without taking into consideration the ability of disks to keep up with
workload requirements. All too often DBAs are called in to solve
performance problems that end up being disk I/O contention/speed and they
can do nothing about it. Understand your disk limitations and when you are
approaching them.
Compare past results from checklists - it is nearly
impossible to understand if performance is good today unless you have
something from the past that tells you, today's performance is worse.
Surely there are times when systems grind to a halt but these are
typically, at least should be, special instances. Understand past
performance trends, current application workloads, and take appropriate
action if performance is an issue.
Clearly, DBAs have a lot to think about when taking on the task of tuning an
Oracle Database. Getting ready is all about having a plan set that you can
follow. In this article, I took a quick look at a checklist of items you should
be thinking about on a regular basis and some tasks that you should know how to
do since they will most definitely be called upon within some of your attempts
to tune an Oracle Database. This checklist/list of items in no way covers how
to solve any performance issues, that will come later as we progress in this
series and as we go through the 2-Day + Performance Tuning Guide. Stick around,
it is bound to be an eye-opener if you have never tuned an Oracle Database before.
