Antwort: "Practicing" Oracle performance tuning ...
- From: "Martin Klier" <Martin.Klier@xxxxxxxxxx>
- To: rameshgeecee@xxxxxxxxx
- Date: Thu, 9 Apr 2009 11:35:49 +0200
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:
- » Antwort: "Practicing" Oracle performance tuning ... - Martin Klier
