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 the DBMS_SUPPORT.START_TRACE_IN_SESSION procedure.

  • 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.


   
Top


Optimizing Oracle Performance
Optimizing Oracle Performance
ISBN: 059600527X
EAN: 2147483647
Year: 2002
Pages: 102

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net