Optimizing Oracle Performance Authors: Cary Milsap with Jeff Holt Pages: 388 Publisher: O'Reilly ISBN: 0-596-00527-x Summary: Reducing Oracle response time for fun and for profit! Review Date: 12 December, 2003

A few months back while troubleshooting an Oracle problem, I stumbled across hotsos.com, a website describing a very different way of improving Oracle performance than that offered by the 1000-page behemoth Oracle performance tuning book sitting on my shelf. Like any good DBA I had tweaked my buffer-cache hit ratios and worked to defrag my tablespaces, but hotsos.com had a different way of looking at things. First, the focus was on reducing the response time for end-users, rather than increasing cache-hit ratios or tweaking other parameters. Another main premise was that you couldn't do accurate diagnosis using only aggregate data (ratios). A key tool in diagnosing response time delay was Oracle 10046 event tracing.

A couple of pdfs from the website, and a Metalink document later, I was building my own trace analysis tool (I'm much too cheap to shell out the hundreds Hotsos was asking for their profiler, when I could just write my own). Unfortunately I didn't have the full picture of how Oracle 10046 trace files were put together. Although I was getting somewhere, I still had some lingering questions about the data I was analyzing.

So when I saw that O'Reilly was publishing a book on Oracle performance by Cary Millsap and Jeff Holt (the Hotsos guys), I was very eager to get my hands on a copy. I can gladly report that the book both met and exceeded my expectations. I can easily say (and I don't say this about many books) that this book is a must read for anybody who wants to get better response time out of their Oracle database.

The first section of the book addresses the overall methodology of tuning recommended, and addresses a lot of politics as well, with fun, painful, but all too familiar examples of tuning projects gone wrong. Ever made a major investment in a hardware upgrade only to find your Oracle performance problem has worsened? This book explains just how that could happen. The first section is written without a lot of technical detail and outlines the various methods to target the appropriate user actions, gather the right diagnostic data, and choosing the right corrective measures to resolve the problem. A key point I took home from this section is the idea that you want to target reducing the response time of whichever user action holds the greatest financial impact on the business. This would be a good section to give your boss, to convince him or her that these methods you want to try out have merit.

The next section is several chapters of very technical reference material. It was in these chapters that I was able to fill in the missing pieces to interpreting 10046 trace data. The authors cover in great detail the contents of 10046 event trace data, and how to collect it. The methods here reinforce the principles taught in the first section of ensuring that you are collecting properly scoped data. Properly scoped meaning that you collect data corresponding only to the targeted user action, and corresponding to the appropriate time interval for that action.

Also covered in this section is exacting detail about Oracle kernel timings, and how to account for unaccounted-for time in your trace data. Also found here is information about the Oracle fixed views, and how best to use them within the framework of this tuning methodology. Filling out this section is a chapter covering queueing theory and how you can apply it to predicting response time, and more particularly, predicting the response-time impact of a particular tuning measure.

The final section of the book is the practical application section, and ties together the first and second sections of the book. It describes how to analyze your trace data, and how best to respond to that information. The section ends with several actual case studies of the methodology in action.

One of the major problems I had with the gargantuan Oracle tuning book I had was there was so much detail, and so many 'recipes' one could apply, one hardly knew where to begin. And of course the book heavily emphasized tuning ratios, which method Millsap and Holt have thoroughly debunked in this work. Rather than endless recipes, the book provides a very clear methodology based on scientfic analysis, measurement, and application. Their methodology not only allows you to locate and respond to key performance problems, but to actually predict the impact any corrective measures will make on the problem.

So don't expect to find the reams of defragmenting tips, rollback segment sizing, disk I/O layout and ratio tuning so prevalant in other tuning guides. Certainly these topics are mentioned, and there are some very good tips you can find in the book, but it's all within the context of the methodology.

Readers of the book should be fairly well-experienced with Oracle, but the first section is very readable (and recommended) for those without technical backgrounds. This book is for thinkers, and takes a good deal of effort to fully grasp some of what's covered in the technical sections. But don't let that be a deterrent to you, you'll be much better off for having put forth the effort to internalize the topics covered.

The book has numerous examples throughout, and code examples given are usually either in Perl or Visual Basic. One of the examples also utilizes an Excel spreadsheet available on the book's website.

In summary, this book and the ideas it promotes are bound to change the face of Oracle tuning efforts in a major way. Already on at least one online forum in which I participate, the book and its methods have been readily accepted and heartily recommended by many experts in the field. "You need to read Cary's book" is a common sentiment expressed there.

Overall rating: 9.5/10 - The content is excellent, and any Oracle performance analyst will benefit immensely by reading and applying these techniques in their shop. Perhaps my only complaint with the book (aside from a few spelling errors) is that sometimes the book turns advertistic (is that a word?) in promoting the hotsos.com products, which I'm sure are very good, but it is a little bit distracting. For those with the programming prowess to build their own trace analysis profilers based on the info in the book it might not be so bad, but for those without, they might feel a bit like they're left hanging.