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:  

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.