A most excellent Oracle tuning course
by Jonathan Gennick
Last week I attended a three-day Hotsos
Clinic on diagnosing performance problems in Oracle systems. It's a great
clinic, highly recommended. It stands out as one of the two best database courses
I've ever attended (the other was a DEC/Rdb Internals course that my boss at
the time accidentally sent me to in 1992).
The clinic is taught by Cary
Millsap and Jeff Holt. Cary and Jeff are among the growing number of Oracle
performance specialists who use Oracle level-8 trace data to methodically diagnose
performance problems. Some highlights from their methodology:
It's user-focused. The emphasis is on response-time as perceived by users.
It's business-focused. The emphasis is on maximizing the economic value
of a system.
Oracle level-8 trace data is used to profile poor performing processes,
enabling you to see exactly how much time is used by the CPU, how much time
is used for I/O, how much time is spent waiting for various resources, and
so forth. Using this information, you can focus your tuning efforts where
they'll have the most impact.
Hard numbers from level-8 trace data allow you to accurately forecast performance
gains that can be achieved by purchasing faster CPUs, faster disks, etc.
Knowing what to expect ahead of time means that a business can first determine
whether a given upgrade will even be helpful, and then decide whether the
resulting performance benefit will outweigh the cost.
There's a single entry-point. Gone are the days when you needed to depend
on guesswork and intuition to tune a system. Oracle tuning does not need
to be a black-art. Apply the methodology, follow the process, and you'll
come to the correct conclusion.
Cary and Jeff are very rigorous, and they approach their work in a very scientific
manner. They spent a good part of the first day debunking the cache-hit ratio
and all the other ratios that DBAs have been taught to rely on as performance-tuning
aids (by they way, if you happen to have the Oracle8i
DBA Bible, which I helped write, please rip out Chapter 20 and throw it
away). They also took time on the first day to emphasize the business aspects
of tuning. We heard the terms net profit, return on investment,
and cash flow repeatedly.
Days two and three found us plunged into the guts of the Hotsos process. We
learned how to generate level-8 trace data, how to read the raw trace files,
and how to use the Hotsos
Profiler to aggregate the raw trace data and present it in a more useful
form. In addition, we received a mini-lesson in a branch of mathematics known
as queueing theory.
Queueing theory is concerned with situations in which requests come into a system
asking for various units of work to be performed, the requests queue up for
resources, the units of work are eventually performed, and the results are sent
back. Queueing theory can be used to predict the workload that an Oracle system
(any system really) can handle while maintaining a reasonable response time.
I thoroughly enjoyed the Hotsos Clinic, and I've probably not done justice
to it in this short review. If you're an Oracle DBA with responsibility for
resolving performance problems, get yourself to this course. You won't regret
it. Also register at the Hotsos website and
read the many helpful papers that Cary and Jeff make available. A good paper
to start with is Why
99% Database Buffer Cache Hit Ratio is NOT Ok (you'll need to register in
order to read it).