Wednesday, January 20, 2010

Oracle Performance Tuning

I guess the first entry 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.

For those of you who are firm believers that RAID 0 or 1 is faster than RAID 5, that may be true at the hard drive level. However, the slowest part of doing those 11,000 inserts on average per second was in fact Oracle's Log Writer (LGWR). The LGWR was slower than the hard drives that wrote the log to disk. Even those disks set at RAID 0 or 1. While Oracle can have many parallel server processes reading the data, and many database writer (DBWR) processes writing the data, there is only one log process. And, it MUST be sequential. And it must work without fail, otherwise the Oracle database stops. When we used Spotlight on Oracle during these heavy insert processes, the indicator for the log writer turned red, red, red. Eventually, I was able to get 13,000 inserts per second. But it wasn’t easy. And it was not achieved by the paradigms that I had had been advised to do.

Fortunately, since then, a number of Oracle researchers such as Tom Kyte, and Cary Millsap did experiments that exposed the flaws of some of the original paradigms of Oracle tuning. They made perfect sense to me. Google for: “Why a 99% buffer cache is NOT Ok”

A few years later, I presented at the Oracle User's Group on PLSQL and SQL tuning. There was not much theory. It was pretty much all case studies on how I dramatically sped queries up from hours, to minutes. And sometimes, hours to seconds. I've received compliments in emails from Germany, Italy, and India about my ideas. Many websites are referencing it around the world. You can google my name, or find the presentation here:


I resonate a lot with the experts Tom Kyte and Cary Millsap. Don’t speculate. Do the experiment, show me the data, and analyze it. Draw conclusions based on the data, not the other way around. If you have not read any of Tom Kyte’s or Cary Millsap’s work, it’s highly recommended. Do look them up.



No comments:

Post a Comment