Antwort: "Practicing" Oracle performance tuning ...

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, serializationand  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_at_klug-is.de
www.klug-is.de
------------------------------------------------------------------------------

Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg

--
http://www.freelists.org/webpage/oracle-l