Oracle Performance Tuning - (Otherwise Known as Drowning in a Pool of Confusion)
July 15, 2004
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:
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:
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.
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:
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.