If someone asked me what
learning Oracle performance tuning is like initially, I would have to say the
title of this article aptly describes the process. Moreover, to make matters
worse, it is not even one pool, but several pools in which the novice tuner can
drown. Knowing what defines poor performance, how to recognize it, and then
improve it reminds me of Supreme Court Justice Potter Stewart’s famous quotation:
"I can’t define
pornography, but I know it when I see it."
The purpose of
this article is not about how to optimize performance, but to offer
recommendations on two excellent books that plainly and effectively tell you
what to look for, and more importantly, how to fix a poorly performing system. There
are plenty of people who are far better at telling you what to do and have made
careers out of specializing in performance tuning. I am taking this opportunity
to stand on a soapbox and help point you in the right direction. What these two
books discuss, compared to the orthodox views presented in many other books,
poignantly highlights this dilemma: you can learn performance tuning the hard
way, or you can learn it the right way.
The Hard Way – Experience Can be a Harsh Teacher
Oracle
Corporation itself is largely to blame for the negative perception and
experience many DBAs have felt first hand when it comes to performance tuning. Performance
tuning (or, "performance and tuning," depending on which piece
of documentation you’re reading) is probably the weakest skill set of most DBAs
– new and experienced alike. For many PT survivors, the approach to performance
tuning follows this scenario:
Patient: Doctor, my head hurts when I bang it against the wall.
Doctor: Stop banging your head against the wall. You should notice an
immediate reduction in pain.
That solves the patient’s
pain problem, but we are left to wonder why the patient was banging his head
against the wall in the first place. Following the steps in Oracle’s tuning
methodology is one approach to investigating the "why" behind this behaviour.
At some point down the road, when you get to the SQL tuning "usual
suspect," The "A Ha" revelation takes place, followed
immediately by, "What do I do now?"
And this is what
you did: Well, if the pin-this get-that ratio in this pool is greater than 80%,
but the logical-reads versus physical reads in this cache is less than 99%, and
if today is Wednesday and Oracle is using more than 100MB of RAM, then set "_another_undocumented_init_ora_parameter"
to 42 to solve the problem.
Execute "alter
system stop scott from doing large joins;" to prevent the problem in the
future. I think you get the idea why this particular plan of attack fails to
win the hearts and minds of DBA’s.
Are the
almost-written-in-stone ratios important? Yes and no. Yes, in that they may be
indicators of problems, and yes in that you need to know some of them by rote
for certification exams. No, in that they are not the gospel and you will not
be fired from your job if you can demonstrate why conforming to these
benchmarks actually reduces your system’s performance.
The Right Way
The path can be
found in Oracle
Performance Tuning 101 (published by Oracle Press) and Optimizing Oracle
Performance (published by O’Reilly). If you know nothing about performance
tuning, other than you know you need to learn how to do it, start your education
with the Oracle 101 book. Learn the methodology presented here before you learn
or acquire "bad habits." What is that you say? How could a "101"
book teach me anything? Assuming you are not one of the top 20 or so
acknowledged performance tuning experts, read this book and you will see for
yourself.
The Oracle 101
book (authored by Vaidyanatha, Deshpande, and Kostelac), unfortunately, has
flown under the radar with respect to exposure and publicity. The Oracle 101
series from Oracle Press are the Reader’s Digest version of their full scale,
unabridged parent versions. In this case, Oracle Performance Tuning 101
is all you really need out of this topic’s family tree.
The O’Reilly book
and the 101 book have something in common: Cary Millsap, co-founder of Hotsos
(author of one, writer of a Foreword in the other). Go into your local
Starbucks infested bookstore and read two sections of Optimizing Oracle Performance:
the Foreword and Preface. If you can identify with anything written there, I
promise you will benefit by buying and reading this book. Optimizing Oracle
Performance is based on this idea:
Our groundbreaking research is making
trial-and-error "tuning" a thing of the past. The Hotsos response
time profiling method […] is the world’s first method that allows you to make
informed economic decisions throughout every step of an Oracle performance
improvement project. It renders traditional trial-and-error tuning methods
obsolete. (http://www.hotsos.com/company/index.html)
Disclaimer: I
have no personal or professional association or relationship with the authors
of these books, so this is not a shameless plug. My motivation for recommending
these books is altruistic in nature: they happen to be excellent books that
help unravel the performance tuning pool of confusion you may be drowning in.
In Closing
Many DBAs own a
collection of personal reference books. If you try to categorize them, you may
or may not have consciously grouped them by functional area. Looking at the
Oracle8i OCP exam breakdown, the five areas are:
- SQL & PL/SQL
- Architecture & Administration
- Backup and Recovery
- Performance and Tuning
- Network Administration
I have noticed
that my own collection mirrors this arrangement. Many reference books cover all
areas; a few cover them well. Your collection may include general reference
books and books limited to a single topic. Oracle8i DBA Bible is
my favorite all-around reference book. For network administration, my hands
down choice is Oracle Net8
Configuration and Troubleshooting. For performance tuning, Oracle Performance Tuning 101
and Optimizing Oracle
Performance take the top spot. However, that’s just my opinion. Take a
look at these books (the other ones too) and browse through them. You have to
find the right book (or two) that really makes an impression on you because of
its clarity and depth of coverage. If you are drowning in a pool of confusion
with respect to performance tuning, these two books will rescue you if you take
the time read and apply what the authors cover.
