Tuning The Untunable: When Indexes and Optimizer Don’t Help

In 2004, I gave a tuning presentation in the Boston area at NOUG

الرئيسية

If you haven’t seen it, do take a look. It actually went viral and I received emails from around the world.

The PDF can be found here:

Oracle Performance Tuning

Recently, I gave a tuning presentation at the Manitoba Oracle User’s Group
http://mboug.wordpress.com/

This MBOUG presentation is a follow up. It covered a number of very mysterious tuning scenarios that I’ve encountered since NOUG, and the techniques I used to overcome them. In these cases, using optimizer and indexes usually did not help at all.

The PDF can be found here:  

HD Tune – Fabulous Windows Hard Drive Testing Software!

Recently I tested a number of my older Windows machines with HD Tune. It’s fabulous performance testing software!

HD Tune

HD Tune tests the slowest part of any computer. The hard drive.

If you have seen my database tuning presentation, you will know that it always amazes me why so many developers don’t realize that the hard drive is the slowest part of any system, and look to remove lines of code that run in memory. Sometimes only once! Duh!

What was really interesting was the the hard drives of three of my old machines ran at an average speed of 20, 50, and 70 megabytes per second. But one machine was running at less than 10 megabytes per second! It confirmed what I knew; that computer was really slow.

HD Tune is fast, easy and intuitive. Reading manuals is not required. Installs in one motion. Only works on Windows though.

The other really good testing software I’ve used is Passmark, that I reviewed previously.  Although, by just looking at the hard drive, HDTune seems to approximate the entire performance pretty closely.

Now, should I just get some solid state drives? Or just buy some new computers? Hmm.

Do check it out!

Oracle Performance Tuning

One of the first entries to start my notes should be the one thing I’ve already done a lot of writing on.

Since I started working with the Oracle database in 1995, I’ve always been very concerned about tuning, performance and response times. Even with the poor software and hardware that we had, I still had to make it work fast. Even though much of the conventional wisdom at the time was to just buy faster hardware, this was never an option at the companies where I worked. I made things go fast by doing what I could do; changing the code, not by buying new hardware.

Later, I was promoted from a developer, to a DBA, and a unix admin. A new job in year 2000 – 2001 gave me a daunting challenge.  The startup wanted to insert ONE BILLION rows of data into an Oracle 8i database, every day. Which is about 11,000 rows per second.  This is not difficult now, but with year 2000 hardware and Oracle 8i software, I was only getting about 6,000 row inserts per second to begin with on the hardware I had. I researched how to do this all day and evening. It took me very deep into Oracle internals and storage.

One of my “few” discoveries was that a number of the tuning paradigms at the time, after I tried them, really made absolutely no significant difference at all. Some of these concepts were whether we used RAID 0, 1, or 5, and hit ratios in Oracle’s buffers. My rigorous experiments where I modified only one variable at a time, such as the RAID setting, produced no difference in the data results at all. The data results were not supporting the paradigms.