Re: "Practicing" Oracle performance tuning ...

From: RameshGeecee <>
Date: Thu, 9 Apr 2009 15:44:59 +0530
Message-ID: <>

Hi Martin,
Thank you so very much for the detailed response. And WOW!!!, I guess I need to spend at-least about a few months reading through basic stuff, before I get to understand / troubleshoot tuning problems. Generally how many months / years do people spend learning these kinds of things? And is it possible to learn these things on NON-on-the-job training at all?


On Thu, Apr 9, 2009 at 3:05 PM, Martin Klier <>wrote:

> Hi Ramesh,
> good proactive performance optimization (efficient modeling and SQL) is 95%
> of the speed-up potential in a slow DB. The more you know about it, the
> better your results will be.
> > Some of the questions that I have are:
> > 1. Where does one get started with performance tuning?
> - Understand basic IT performance. Why is HDD access slower than RAM
> access? What's a random seek on a disk, why is it slower than a bulk
> access? Why does RAM access cost CPU? Bandwidth, IPC issues, serialization-
> and parallelization problems...
> - Understand how the RDBMS does work. What are Blocks, Buffers, all the
> lots of various caches, latching mechanisms, how, why and when are they
> used? How are tablespaces and segments organized, what are conceptional
> bottlenecks?
> - Understand, what Indexes are, how they work and why they are useful in
> some situations, and why not.
> - Understand the Optimizer as good as possible :), and what it needs for
> optimization decisions (Statistics, Histograms), and why! Make yourself
> familiar with execution/explain plans (as described here:
> )
> This are the basics. It's lots of theory, but without it you will loose.
> Knowing that, you can help yourself and collect the necessary experience.
> Most of them are in the Performance Tuning Guides provided by Oracle, you
> just have to read them and look up the parts you don't understand.
> > 2. Various tuning exercises need to be tried on boxes other than
> > Production first - so how does one make the test box similar to what
> > the production has?
> Make them identical, by machine, options, version, metadata and data.
> Export the production including everything (including stats and so on), and
> run the slow queries from there manually in the test environment. Make sure
> that execution plans have the same hashes on both boxes, don't try to catch
> spectres!
> > 3. What sets of built-in tools are generally useful in real life
> situations?
> Use the built-in Oracle Wait Interface to see what REALLY causes trouble
> NOW (v$session_wait and its dependents). It's usually useless to fight
> against things that do not cause waits in production operation.
> Furthermore, have a look at statspack (AWR is newer and a bit better, but
> it has to be licensed seperately).
> The OSM-Tools of Craig Shallahamer are great as well, but you need some
> practice with it to understand what it does.
> > 4. What about any commercial utilities?
> Oracle diagnostics pack is nice, especially in combination with the Oracle
> EM included with the basic product.
> I usually don't use third party tools, so I don't know a name, but some of
> them are said to be really nice to compare statspack reports or something
> similar.
> A general advice:
> Try to find out, what problem harms most, and fight it first!
> Sounds simple, but lots of people waste time with fighting problems that
> are cause 10% of the impact, and ignoring the 60% things just because they
> don't know how to handle them immediately. Often, the large problems are
> causing minor ones. In my example, it's high chances that the 10% problem
> disappears when the 60% one is solved.
> Good luck!
> --
> Mit freundlichem Gruß
> Martin Klier
> Senior Oracle Database Administrator
> ------------------------------------------------------------------------------
> Klug GmbH integrierte Systeme
> Lindenweg 13, D-92552 Teunz
> Tel.: +49 9671/9216-245
> Fax.: +49 9671/9216-112
> mailto:
> ------------------------------------------------------------------------------
> Geschäftsführer: Johann Klug, Roman Sorgenfrei
> Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
> HRB Nr. 2037, Amtsgericht Amberg
> --

Received on Thu Apr 09 2009 - 05:14:59 CDT

Original text of this message