Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 15, 2004

Oracle Performance Tuning - (Otherwise Known as Drowning in a Pool of Confusion)

By Steve Callan

If someone asked me what learning Oracle performance tuning is like initially, I would have to say the title of this article aptly describes the process. Moreover, to make matters worse, it is not even one pool, but several pools in which the novice tuner can drown. Knowing what defines poor performance, how to recognize it, and then improve it reminds me of Supreme Court Justice Potter Stewart's famous quotation: "I can't define pornography, but I know it when I see it."

The purpose of this article is not about how to optimize performance, but to offer recommendations on two excellent books that plainly and effectively tell you what to look for, and more importantly, how to fix a poorly performing system. There are plenty of people who are far better at telling you what to do and have made careers out of specializing in performance tuning. I am taking this opportunity to stand on a soapbox and help point you in the right direction. What these two books discuss, compared to the orthodox views presented in many other books, poignantly highlights this dilemma: you can learn performance tuning the hard way, or you can learn it the right way.

The Hard Way - Experience Can be a Harsh Teacher

Oracle Corporation itself is largely to blame for the negative perception and experience many DBAs have felt first hand when it comes to performance tuning. Performance tuning (or, "performance and tuning," depending on which piece of documentation you're reading) is probably the weakest skill set of most DBAs - new and experienced alike. For many PT survivors, the approach to performance tuning follows this scenario:

Patient: Doctor, my head hurts when I bang it against the wall.

Doctor: Stop banging your head against the wall. You should notice an immediate reduction in pain.

That solves the patient's pain problem, but we are left to wonder why the patient was banging his head against the wall in the first place. Following the steps in Oracle's tuning methodology is one approach to investigating the "why" behind this behaviour. At some point down the road, when you get to the SQL tuning "usual suspect," The "A Ha" revelation takes place, followed immediately by, "What do I do now?"

And this is what you did: Well, if the pin-this get-that ratio in this pool is greater than 80%, but the logical-reads versus physical reads in this cache is less than 99%, and if today is Wednesday and Oracle is using more than 100MB of RAM, then set "_another_undocumented_init_ora_parameter" to 42 to solve the problem.

Execute "alter system stop scott from doing large joins;" to prevent the problem in the future. I think you get the idea why this particular plan of attack fails to win the hearts and minds of DBA's.

Are the almost-written-in-stone ratios important? Yes and no. Yes, in that they may be indicators of problems, and yes in that you need to know some of them by rote for certification exams. No, in that they are not the gospel and you will not be fired from your job if you can demonstrate why conforming to these benchmarks actually reduces your system's performance.

The Right Way

The path can be found in Oracle Performance Tuning 101 (published by Oracle Press) and Optimizing Oracle Performance (published by O'Reilly). If you know nothing about performance tuning, other than you know you need to learn how to do it, start your education with the Oracle 101 book. Learn the methodology presented here before you learn or acquire "bad habits." What is that you say? How could a "101" book teach me anything? Assuming you are not one of the top 20 or so acknowledged performance tuning experts, read this book and you will see for yourself.

The Oracle 101 book (authored by Vaidyanatha, Deshpande, and Kostelac), unfortunately, has flown under the radar with respect to exposure and publicity. The Oracle 101 series from Oracle Press are the Reader's Digest version of their full scale, unabridged parent versions. In this case, Oracle Performance Tuning 101 is all you really need out of this topic's family tree.

The O'Reilly book and the 101 book have something in common: Cary Millsap, co-founder of Hotsos (author of one, writer of a Foreword in the other). Go into your local Starbucks infested bookstore and read two sections of Optimizing Oracle Performance: the Foreword and Preface. If you can identify with anything written there, I promise you will benefit by buying and reading this book. Optimizing Oracle Performance is based on this idea:

Our groundbreaking research is making trial-and-error "tuning" a thing of the past. The Hotsos response time profiling method [...] is the world's first method that allows you to make informed economic decisions throughout every step of an Oracle performance improvement project. It renders traditional trial-and-error tuning methods obsolete. (http://www.hotsos.com/company/index.html)

Disclaimer: I have no personal or professional association or relationship with the authors of these books, so this is not a shameless plug. My motivation for recommending these books is altruistic in nature: they happen to be excellent books that help unravel the performance tuning pool of confusion you may be drowning in.

In Closing

Many DBAs own a collection of personal reference books. If you try to categorize them, you may or may not have consciously grouped them by functional area. Looking at the Oracle8i OCP exam breakdown, the five areas are:

  • SQL & PL/SQL
  • Architecture & Administration
  • Backup and Recovery
  • Performance and Tuning
  • Network Administration

I have noticed that my own collection mirrors this arrangement. Many reference books cover all areas; a few cover them well. Your collection may include general reference books and books limited to a single topic. Oracle8i DBA Bible is my favorite all-around reference book. For network administration, my hands down choice is Oracle Net8 Configuration and Troubleshooting. For performance tuning, Oracle Performance Tuning 101 and Optimizing Oracle Performance take the top spot. However, that's just my opinion. Take a look at these books (the other ones too) and browse through them. You have to find the right book (or two) that really makes an impression on you because of its clarity and depth of coverage. If you are drowning in a pool of confusion with respect to performance tuning, these two books will rescue you if you take the time read and apply what the authors cover.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM