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 > >