Re: "Practicing" Oracle performance tuning ...

  • From: RameshGeecee <RameshGeecee@xxxxxxxxx>
  • To: Martin.Klier@xxxxxxxxxx
  • Date: Thu, 9 Apr 2009 15:44:59 +0530

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?

Regards,
Ramesh.

On Thu, Apr 9, 2009 at 3:05 PM, Martin Klier <Martin.Klier@xxxxxxxxxx>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:
>
> http://www.usn-it.de/index.php/2009/02/26/oracle-explain-or-execution-plans-guess-or-reality/
> )
>
> 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: martin.klier@xxxxxxxxxx
> www.klug-is.de
>
> ------------------------------------------------------------------------------
>
> Geschäftsführer: Johann Klug, Roman Sorgenfrei
> Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
> HRB Nr. 2037, Amtsgericht Amberg
>
> --
> //www.freelists.org/webpage/oracle-l
>
>

Other related posts: