Preface
Optimizing Oracle response time is, for the most part, a solved problem. I hope that I have written effectively enough that, after reading this book, this idea will come to fruition in your own experience.
However, if you’re like most people, you probably don’t feel that way yet. For most people, Oracle performance improvement projects are long, frustrating battles against some invisible enemy that evades detection no matter how much time or extra computer hardware you throw at the situation. The root cause of the problem is that most education about tuning is broken. My aims in this book are to show you why, and to reveal to you what you should do instead.
Bad tuning methods have prospered among the Oracle community for a long time. For well over a decade, the Oracle community has been afflicted with lots of performance problems but a virtual absence of competent training programs for performance analysts. The result has been a lucrative seller’s market for the Oracle performance analyst. Throughout the 1990s in many parts of the world, a consultant could name his own price and bill by the hour for time spent attempting to improve performance. The tuning methods that evolved in this environment were geared more toward maximizing a consultant’s revenue intake than maximizing the success of your system.
Why I Wrote This Book
I began my Oracle career in 1989 as a new employee of Oracle Corporation itself. By 1992, I felt reasonably competent as a performance specialist. My performance optimization method was one that many people still teach today: fix the ten things I knew how to fix, and then pray that the cause of the performance problems had been some combination of those ten things. In late 1992, I was charged with leading a national group. Beginning promptly with that promotion to manager, my hands-on technical skills (such as they were) began their decay. By the end of 1993, I felt like I had logged more career hours in Excel and PowerPoint than in Oracle products.
In 1995, I proposed the construction of a new group in Oracle Corporation that would be called the System Performance Group (SPG). SPG became one of the largest and best collections of Oracle performance experts in the world. By 1996, it had become abundantly clear to me that my feelings of reasonable competence in 1992 had been false. Specifically, I was receiving engagement summaries from a few of my staff that depicted an absolutely stunning leap forward in project efficiency.
These analysts were wasting virtually no time whatsoever in their performance improvement projects. They were predicting the exact impact upon application response times that would result from the implementations of specific performance improvement recommendations. By the end of one of these analysts’ first day on site, he would have solved more performance problems more conclusively than I would have solved in a whole week back in 1992. It was as if these people were doing system performance surgery with CAT scans and laser scalpels in an environment where I had formerly known only of leeches and bone saws.
The informal name of the technology these analysts were
using was the “Oracle wait interface.” This “wait interface” was, to the
extent of my knowledge back then, a collection of V$ tables and some new trace data that could
tell an analyst how the Oracle kernel was spending the user’s response
time. Anjo Kolk’s internal Oracle paper, “Description of Oracle7 Wait
Events and Enqueues,” released in the mid-1990s, first made Oracle
insiders aware of the potential of this new instrumentation. As with any
emerging technology, however, remarkable successes were restricted
mostly to the few practitioners who possessed extraordinary talent to
begin with.
Repeatability was the problem. In the work of my most talented colleagues, I could smell the potential of a repeatable performance optimization method, but never more than about 10% of my 85 performance specialists could repeat the spectacular results of my few top consultants. The methods simply required more intuition and experience than we could count on people to summon.
In October 1999, I resigned from my position as vice president at Oracle Corporation. After taking the weekend off, I began work with Gary Goodman and Jeff Holt to build a company known now to several thousand performance analysts as hotsos.com. Since 1999, I have been able to dedicate my professional life to one goal:
To create a performance optimization method that works and that can be taught effectively to the typical Oracle database administrator.
In the more than three years since beginning this project, we have devoted over six man-years of full-time research to derive and test the results that you will see in this book. In the process, we have instructed students at the rate of about 250 per year in our Hotsos Clinic events. Our goal in the course is the same as the goal of this book, to transfer understanding of a reliable new method that will revolutionize your effectiveness as a performance optimizer. Using the same techniques presented in this book, students have returned home from class to improve the response time of critical business actions from hours to seconds on their first day back at work.
The “Oracle wait interface” is, by the time of this writing, prominently in the public attention among database administrators. Although it took nearly ten years since its introduction in Oracle release 7.0.12, messages about the “wait interface” are today being carried forward by hundreds of performance practitioners who are delivering wait-based tuning presentations at conferences and posting wait-based tuning information on public forums like Oracle-L (http://www.cybon.com/~jkstill/util/util_master.html).
However, at the time of this writing, Oracle’s extended SQL trace facility is still sorely underutilized in the general market, for several reasons:
Although the pseudo-error debugging event 10046 feature has been around for a long time, Oracle Corporation did not formally support its customers’ use of extended (i.e.,
LEVEL> 1) SQL trace data until the release of theDBMS_SUPPORT.START_TRACE_IN_SESSIONprocedure.Oracle Corporation’s own documentation and most of the books you buy have dedicated only minimal attention to the extended SQL trace facility.
There have been many misconceptions about extended SQL trace data that unfairly limit analysts’ perception of its trustworthiness. Even at the time of this writing, most analysts don’t realize that trace files do convey information about time that an Oracle session has spent paging, swapping, or waiting for CPU.
There have been no tools available that assist you in collecting properly time-scoped and program-scoped diagnostic data.
There have been few tools to help you interpret properly scoped trace data in a useful way. Oracle’s tkprof tool has performed adequately in unit testing environments since its release in Version 6. However, after retrofitting in Version 9, tkprof does a lackluster job of accounting for a session’s total response time. It does a poor job of helping you diagnose the events that occur between database calls. And it doesn’t help at all in determining the recursive relationships among cursor actions.
Oracle’s extended SQL trace facility has become the principal performance diagnostic feature of the Oracle kernel for our staff at hotsos.com. We have acquired this capability because since 1999 we’ve been able to do extensive research of the behavior of over a thousand real SQL trace files collected from real application systems running on a variety of platforms all over the world.
We have attacked both the education problem and the tools problem. In our Hotsos Clinic events, we have subjected our method to the rigorous scrutiny of several hundred students of performance analysis. With our free tool called Sparky, we have introduced the first tool in the world that helps you collect properly scoped SQL trace data. Because of our Hotsos Profiler software tool, we have helped solve hundreds of difficult real-life performance problems for our customers in hundreds of analyses that have averaged less than one hour each in duration. (You can obtain more information about Hotsos Clinic events and Hotsos software tools at http://www.hotsos.com.)
This book is the fruit of all three investments. Its intent is to eliminate the obstacles that have prevented the world from exploiting Oracle’s extraordinary “new” performance instrumentation to its fullest capacity.
Audience for This Book
Responding to an Oracle performance problem can be a complicated task involving people from several departments within your business (users, system managers, database managers, network managers, application developers, and so on) and possibly even from several of your hardware and software vendors as well. I have written this book for the person called the performance analyst. The performance analyst is one person, or perhaps a small team of people, who are responsible for the following activities in a performance improvement project:
- Targeting
The performance analyst is responsible for properly designing a performance improvement project to fix the right problem.
- Analysis
The performance analyst is responsible for ensuring that a performance improvement project will achieve the desired target with the least economic investment.
- Implementation
The performance analyst is responsible for ensuring that the performance improvement project results in real progress in the actual live system.
Because this is a book about a new performance improvement method that is more radical than you’re probably accustomed to, I have included text to help motivate the necessary changes in fundamental approach among project sponsors and project managers. Part I of this book is especially important reading for sponsors and managers who don’t understand the need for change in Oracle performance improvement methods.
Structure of This Book
This book is divided into four parts, with twelve chapters and five appendixes in total.
Part I, Method, is about targeting. It is written in an informal, narrative style that sponsors and managers of performance improvement projects can read from front to back without getting distracted by a lot of technical details. It includes the following chapters:
Chapter 1, explains why Oracle performance improvement is so difficult using conventional methods. It explains three important advances from other industries that Oracle performance analysts have ignored for decades. Finally, it describes the new performance improvement method to which the remainder of the book is dedicated.
Chapter 2, describes why many performance improvement projects are doomed from the beginning by poor project specifications. It explains how to construct a foolproof specification for your performance improvement project.
Chapter 3, describes how errors in diagnostic data collection are the root cause of many failed performance improvement projects. It describes why many projects cannot ever succeed without properly scoped diagnostic data, and it introduces three distinct sources of such information on Oracle systems.
Chapter 4, explains how performance improvement projects can be held to the same standard of informed consent that other scientific endeavors require. It describes how to forecast performance improvement project costs and benefits and how to find the economically optimal performance improvement activity from the universe of things you could do about system performance.
Part II, Reference, is about detail. It is written in a deeply technical style in which I try to provide what a performance analyst needs to implement the method. It contains the following chapters:
Chapter 5, describes the content of an Oracle extended SQL trace file. It describes the meaning of fields in a trace file, and it explains the relationships of time statistics throughout a trace file.
Chapter 6, explains how to collect the properly scoped extended SQL trace data that you’ll need to analyze a performance problem.
Chapter 7, explains how software like the Oracle kernel measures itself and how you can verify the self-diagnostic behavior of your own system. It goes on to explain several sources of unaccounted-for time in Oracle trace files and why these lapses in timing data often contain performance diagnostic data in and of themselves.
Chapter 8, explains some of the many deficiencies of Oracle’s dynamic performance views. It presents descriptions of several popular
V$fixed views and examples of their use. You might be surprised to find out that some of the things you thought you knew about Oracle’s dynamic performance views are untrue.Chapter 9, is one of my favorites. It explains the physical phenomenon of queueing and how to use the body of mathematical knowledge called queueing theory to understand and even predict the performance of systems including Oracle database applications.
Part III, Deployment, returns to the informal, narrative style that I hope will encourage project sponsors and managers to follow along. It covers the issues of how to complete the job for maximal positive impact in the following chapters:
Chapter 10, describes a step-by-step method for analyzing Oracle response time data that leads to maximized performance improvement at minimized cost. It describes the tremendous economic benefits of waste removal and explains how to think “outside the box” to achieve performance improvements you might never otherwise have considered. Finally, it explains how to tell when your performance optimization work is complete, a task that is astonishingly difficult in conventional performance improvement methods.
Chapter 11, describes how to improve application performance in response to various patterns you’ll find in your performance diagnostic data. It places particular emphasis upon how to eliminate wasteful work from your system, and it covers important response time components that are documented either poorly or not at all in other works.
Chapter 12, is the capstone chapter of the book. It documents four complete cases from problem identification, through the targeting, analysis, and deployment processes, to show you exactly how the method works in real life.
Part IV, Appendixes, contains the following:
Glossary, contains definitions of technical terms used throughout the book.
Appendix A, is a table of Greek letters and their English equivalents, intended to simplify your reading of Chapter 9.
Appendix B, inspired by Connor McDonald’s http://www.oracledba.co.uk, is the best proof I know that having a great database buffer cache hit ratio does not mean that you have a great system. The Perl program shown in this chapter can make your cache hit ratio anything you want it to be!
Appendix C, is a summary of formulas used in Chapter 9.
Appendix D, contains bibliographic information about the several dozen references used in the book.
Which Platform and Version?
This book contains examples from Oracle kernel releases 8 and 9 on operating systems including Linux, Sun Solaris, IBM AIX, HP-UX, OSF-1, VMS, MVS, and Microsoft Windows. Most of the features described within this book are virtually independent of operating system, and most work equally well in Oracle releases from 7.0.12 through 9.2.0.
As I write this book, Oracle Corporation has not yet unleashed Oracle release 10. I’m not officially endowed with any foreknowledge of the release 10 kernel, but I have my suspicions about what they’re doing with it. Where possible, I’ve identified areas in which the upcoming changes in release 10 will likely alter your world beyond what I’ve described in this book.
Some chapters are immune. The entirety of Part I and most of Part III will remain unchanged after
Oracle release 10 comes onto the market. Most of Part II, you might be surprised to
learn, is Oracle version independent. The core messages of Chapter 5, Chapter 7, Chapter 8, and Chapter 9 will remain unchanged by
release 10. For example, though Chapter
7 gives examples of how Oracle Release 7, 8, and 9 kernels do
their work, it also shows you how to find out whether release 10 behaves
any differently. Though Chapter
8 shows details of Oracle9i V$ views, which will change in release 10, the
basic problems of polling and summarization will remain relevant in
release 10.
Some features mentioned in this book are not available on every
version of Oracle from 7 through 9 (TRACEFILE_IDENTIFIER is one example). I’ve
made no effort to list the Oracle kernel version in which new features
appear. However, for features that may not be available in the release
of Oracle that you’re using, I generally provide two or more ways to
accomplish a task. Therefore, if the elegant way to accomplish a task
isn’t available in your environment, then I have probably described an
alternate way to get the job done.
What This Book Is and Is Not
This book is different from any other book about Oracle performance on the market. It is not a book of tips and techniques. It is a book dedicated to helping you remove performance pain faster and more completely than you’ve ever thought possible. I believe that doing this, however, requires a book that will change your whole mindset about performance.
This book describes a method for optimizing the performance of an Oracle system, but it goes an important step further. The method prescribed in this book optimizes the performance of the whole performance improvement project. The goal of this book is not to make one system faster; it is to make you faster and more efficient at optimizing any system.
This book focuses more completely upon performance problem diagnosis than upon repair tasks. In my experience, diagnosis is where people usually mess up. There are lots of experts out there responding sensibly to poorly collected diagnostic data in poorly specified projects. It is usually easy to solve a problem when it is presented to you correctly. It can be impossible to solve the right problem when you’re focused on the wrong problem. This book contains many examples of working on the wrong problem, and it explains how never to make that mistake again.
This is not a book about system management or capacity planning, although almost all the information in this book is relevant to the system manager’s and capacity planner’s job. It is not a compendium of all the Oracle “wait events.” I cover the events that we have encountered most frequently in the hundreds of trace files we’ve analyzed, but I leave detailed treatment of each event to other sources, such as you might find on the Internet. I use google.com as my primary source of information about wait event definitions. Authors are continually adding to the Internet repertory of detailed information about the wait events.
Finally, this is a book for the performance optimization practitioner. It’s not a book of theories. Every piece of information in this book is included because my students, my colleagues, and I need to have the information available to do our jobs of improving Oracle performance.
About the Tools, Examples, and Exercises
I’ve chosen Perl as the primary demonstration programming language for this book. It may seem odd that a book about Oracle doesn’t use SQL or PL/SQL as a demonstration language, but once you begin reading you’ll begin to understand. A lot of this book isn’t about Oracle, it’s about performance, and the issues of performance are much broader than the SQL and PL/SQL languages are capable of handling elegantly. Perl allows me to illustrate realistic experiments using a free, portable tool that is simple to install. I hope that this choice will maximize the probability that you will actually try some of the experiments that I describe.
I’ve chosen Microsoft Visual Basic as the demonstration programming language for some of the queueing theory material because Microsoft Excel is the de facto workbook software today. Again, I hope that this choice will maximize the probability that you will actually use the material I’m offering here.
You can download all the listings in this book at the following URL:
| http://www.oreilly.com/catalog/optoraclep/ |
This book contains exercises. This is unusual both for an O’Reilly book, and for a book about Oracle. I use exercises for the following reasons:
To encourage you to try exactly what you’ve just read.
To stimulate you to apply things that you’ve just read, but with different input values.
To stimulate you to apply things that you’ve just read, but on your own system.
To admit that I don’t know the answer to every interesting question out there, but at least I have made it as far as defining the problem. This way, others can better understand that a problem exists, and they can work on solving it only if the economics of their particular situation warrant the effort.
To encourage the use of this book as a textbook in formal education courses. I have been using draft versions of this book very successfully as the text material for my company’s Hotsos Clinic 101 (http://www.hotsos.com) since 2002.
Most of the exercises don’t have a single correct answer. For the ones that do, check http://www.oreilly.com/catalog/optoraclep for updated solutions.
Citations
This book cites several outside resources. For these citations, I use a standard academic format that is easy to decode. When you see the citation, “[Bach (1986) 148],” it is a reference to page 148 in the document identified in Appendix D as “[Bach (1986)].” The document in this case is a book written by Maurice Bach, published in 1986.
Conventions Used in This Book
The following typographical conventions are used in this book:
- Italic
Used for filenames, directory names, and example URLs. It is also used for emphasis and for the first use of a technical term.
Constant widthUsed for examples and event names and to show the contents of files and the output of commands.
Constant width boldIndicates user input in examples showing an interaction. It is also used to emphasize parts of
constant widthtext, such as output listings.- [RETURN]
Indicates a carriage return at the end of a line.
Comments and Questions
We have tested and verified the information in this book to the best of our ability, but you may find that features have changed or that we have made mistakes. If so, please notify us by writing to:
| O’Reilly & Associates, Inc. |
| 1005 Gravenstein Highway North |
| Sebastopol, CA 95472 |
| (800) 998-9938 (in the United States or Canada) |
| (707) 829-0515 (international/local) |
| (707) 829-0104 (fax) |
There is a web page for this book, which lists errata, examples, or any additional information. You can access this page at:
| http://www.oreilly.com/catalog/optoraclep/ |
To comment or ask technical questions about this book, send email to:
| [email protected] |
For more information about books, conferences, Resource Centers, and the O’Reilly Network, see the O’Reilly web site at:
| http://www.oreilly.com |
You can also visit the authors’ web site at:
| http://hotsos.com |
Acknowledgments
- Mindy, Alexander, and Nikolas Millsap
My wife and my boys are an inspiration to me each day, and I want to acknowledge the sacrifice they’ve made to make this project possible. I wrote this book on their time, and I hope that the results of this project in some way pay them back for all the time they have let me invest into it. Thank you, Mindy, Alex, and Nik.
- Van and Shirle Millsap
At the beginning of every school year, my parents would take me into school and introduce themselves to my teachers. On the way to school, they would always tell me the same thing:
There are two answers to every question your teachers will ask you while you’re in school. There’s the correct answer, and there’s the answer that the teacher wants. I expect you to know them both.
Thank you, Mom and Dad. You have no idea how much you’ve helped me in this lifetime.
- Jeff Holt
Of the many blessings bestowed upon hotsos.com in its three years, none is more important than to have secured the participation of one Mr. Jeffrey L. Holt. Jeff was one of the top performance analysts in that 85-person group that I left at Oracle. He is now our Chief Scientist at hotsos.com. In the past three years, Jeff’s principal job has been to teach me how to optimize an Oracle system. My job has been largely to deactivate the intuitive part of Jeff’s brain.
Jeff is one of those talented people who understands how to solve a problem long before he can explain how he did it. I’m an obsessive-compulsive pedant who spends more time trying to figure out why an answer is correct than he does trying to figure out the answer. I believe that if a method relies on its user’s intuition and experience, then the method is neither repeatable nor teachable. I believe that without rigorous elimination of experience, intuition, and luck from the performance improvement process, it is impossible to create an acceptably high-quality Oracle performance optimization method.
You can see, then, what Jeff has been dealing with. Jeff has been limitlessly intelligent and patient throughout the entire process of having the insides of his head taken out and put back in again lots of times. Thank you, Jeff.
- Gary Goodman
Gary Goodman is my friend and co-founder of hotsos.com. Without the long walk that occurred back in the summer of 1999, there’s no telling what I’d be doing today. Actually, without the long walk that occurred back in the summer of 1989, I might have never worked for Oracle Corporation. Without the job that Gary does every day, there would be no hotsos.com and no book that you’re now holding in your hands. Thank you, Gary.
- Mogens Nørgaard
Mogens Nørgaard is my award-winning friend from Denmark who first introduced me to the then-mysterious “Oracle wait interface.” Mogens is the first human on the planet to require his entire technical staff at Oracle Corporation to use the wait interface and only the wait interface in diagnosing Oracle performance problems. Mogens is also the founder of the world-famous Oak Table Network (http://www.oaktable.net), a collection of people who I consider to be the dominant minds in the Oracle performance space. Without Mogens’ friendship, support, and social arrangement-making, this book would probably have never happened. Thank you, Mogens.
- Anjo Kolk
Anjo Kolk is the father of Oracle response time optimization methods. Since I first met Anjo sometime in the early 1990s, he has never balked at investing time from his busy schedule to teach my groups and me how things really work. Thank you, Anjo.
- Virag Saksena
Virag Saksena is the first consultant in my System Performance Group at Oracle Corporation who showed me a glimpse of what the world of performance improvement projects could be like. Virag’s talent is in a sense the spark that lit the fire that became this book. Thank you, Virag.
- Jonathan Lewis, Connor McDonald, and Frank Hansen
For a multitude of reasons including their provision of feedback that improved the quality of this book. Thank you, gentlemen.
- Jonathan Gennick and the staff at O’Reilly & Associates
Rick Greenwald told me that there are three types of book publishers in the world: those who publish a book that’s worse than the author’s original manuscript, those who publish a book that’s as good as the manuscript, and those who publish a book that’s better. Jonathan’s leadership has made this book better than the one I would have written without O’Reilly.
I would like to express my sincerest gratitude to the customers of hotsos.com, who have provided sustenance to my family and stimulation to my mind. Your support is the reason that the material in this book can exist. Finally, thank you to the many people who have taught me many things, including:
Steve Adams | Nancy Dushkin | Jonathan Intner |
Micah Adler | Julian Dyke | Lynn Isabella |
Philip Almes | Morten Egan | Ken Jacobs |
Andy Bailey | Jean Emerson | Neil Jensen |
Karla Baisey | Bjørn Engsig | Phil Joel |
Vladimir Barriere | Dave Ensor | Guðmundur Jósepsson |
Ken Baumgardner | Barry Epstein | Derry Kabcenell |
Curtis Bennett | Henry Fahey | George Kadifa |
Darren Bock | Mark Farnham | Mike Kaul |
Kenneth Brady | Robert Feighner | Brian Kush |
Phillip Briggs | Peter Gram | Armand Sadat Kyaee |
Michael Brown | Donald Gross | Tom Kyte |
Tim Bunce | Kyle Hailey | Ray Lane |
Dr. Burt Burns | Stephan Haisley | Sang Chul Lee |
Lasse Christensen | Theresa Haisley | Jonathan Lewis |
Carol Colrain | Ray Hamlett | Margaret Lewis |
Rudy Corsi | Ahmer Hasan | Jim Littlefield |
Carol Dacko | Jim Herndon | Juan Loaiza |
Dominic Delmolino | Dave Herrington | Andrea Lopez |
David Dempsey | Carol Hipp | Scott Lovingfoss |
Kirti Deshpande | Dr. Myron Hlynka | Roderick Mañalac |
Johannes Djernæs | Torben Holm | Laura Mazzarella |
Greg Doherty | Mark Horstman | Connor McDonald |
Ellen Dudar | Mamdouh Ibrahim | Daniel Menascé |
Rick Minutella | Marcela Studnicka | |
Michael Möller | Torfi Ólafur Sverrisson | |
James Morle | Irfan Syed | |
Craig Newburger | Tony Taylor | |
Mark Pavkovic | Lawrence To | |
Charles Peterson | Dan Tow | |
Nagesh Pillarisetti | Joakim Treugut | |
Nick Popovic | Hank Tullis | |
Lyn Pratt | Peter Utzig | |
Darryl Presley | Gaja Krishna Vaidyanatha | |
Dr. Ray Quiett | Thierry Vergult | |
Willis Ranney | Michel Vetsuypens | |
Matt Raue | Dr. Anita Walker | |
Andy Rivenes | Dr. Bill Walker | |
Hasan Rizvi | Mike Wielonski | |
Jesse Ruder | Gerald Williamson | |
Bob Rudzki | Liz Wiseman | |
Sandy Sanderson | Brian Wolff | |
Matt Seaton | Graham Wood | |
Craig ShallahamerPete Sharman | Jimmy Harkey, for introducing me to the axiomatic approach to problem-solving | |
Robert ShawRoger Siemens | Rachel Rutti, for introducing me to Eli Goldratt’s The Goal | |
Dr. John Slocum | The members of the Oak Table Network | |
Jerry Snow | The many contributing members of Oracle-L | |
Bill Stangel | And Gram... I miss you more than I ever imagined. | |
Jared Still |