Oracle Performance Firefighting by Craig Shallahamer

Today I am going to write about an amazing book Oracle Performance Firefighting by Craig Shallahamer, which was released in July 2009. After all the papers I read from Craig, my fingers were crossed for the release date and I ordered the book for our company immediately after release.  The reason I was very excited about the book is that  I always feel uncomfortable with overall system performance tuning and this is the area where Craig looks  very good at.

During the review, I will try to cover what this book promises and gives us by going through  all the chapters one by one. For overall, this book is not for sql tuning  It is about how to find the problem and giving possible other solutions for common problems ( in addition to tuning your code as a first option.) by giving the information about how Oracle works in most problematic areas like buffer cache, shared pool, redo, undo and especially  latches and mutexes.

Before starting to go through chapters, I want to say that the book is very well organized  and the way Craig teaches avoids boredom about numbers and math.   I could not  leave the book even on holiday.  The way he covers the very hard to understand topics brings curiosity about the next topic.  One more thing is the number of usage of the word OraPub (his company) kept minimum.  While reading  Optimizing Oracle Performance by Cary Millsap and Jeffrey Holt, I really hated the word Hotsos because  it was nearly on every page and that was very annoying. If you are curious about this problem it is not happening that much in this book.

Lets start with going through chapters (Chapters with * are the ones that really needs attention)

1- Methods and madness: This is the introduction to how to be a firefighter from both methodic and holistic perspective. Craig gives information  What you need during Oracle firefighting and how you can build a methodology . He covers the basics of analysing the system as a whole under the name of  OraPub 3 Circle Analysis.  When I first read about the OraPub 3 Circle Analysis I did not expect much but after a few chapters I admit that it works quite fine.  Most interesting topic to me was how to write the summary of your firefighting story.  That is something I need to work on.

2- Listening to Oracle’s Pain: This chapter tries to explain how to understand what Oracle is crying for by using Oracle Wait Interface and ORTA (oracle response time analysis). It covers how Oracle kernel developers instrumented their code for creating Oracle Wait Interface. It wasnt something I did not know but still nice to remember most them.

3- Serialization Control (*): This chapter is where this book starts to rock about explaining what is going on in Oracle by telling how Latches Mutexes and Locks work and differ from each other.  After finishing this chapter I believed that the book already worth the money I paid and there were 6 more chapters to go. Craig definitely knows how Oracle works and better than just knowing he knows how to explain it like latches for dummies.  It is really hard not to remember or not understand what he is explaining.

4- Idenfying and Understanding Operating System Contention: This chapter tries to cover how to use unix/linux tool to gather information from operating system to do the operating system circle of OraPub 3 Circle Analysis. Apart from using tool it is very good about the way Craig teaches about how to talk and convince Network, OS and Storage guys in case of a problem without making their nerves. I personally liked very much the word Craig uses like “there is a memory pressure” instead of “paging” 🙂

5- Oracle Performance Diagnosis: This chapter is going deep into how to gather overall performance related data from Oracle and how to interpret the data you gathered without going to compulsive tuning disorder. He covers the wait event myths section which was very interesting and he tries explain why profiling is not “always” the best approach. Making the most out of DBMS_MONITOR  tool is also covered well. Craig also gives internals about ASH (Active Session History). How it works why it is good and how to use it. Craig also gives the simple trick about how to learn Oracle internals which is reading Oracle patents also search oracle in (http://www.faqs.org/patents/asnl/op).

6- Oracle Buffer Cache Internals(*): Till I read this book, I never dreamed  about that, one day I will really fully understand and be able to explain to others, how  Oracle buffer cache, shared pool and latches works, despite all official documentation and the books I read so far. This and upcoming 2 chapters  gave me this opportunity and I want to thank Craig very much for explaining these topics this well. Diagrams and way he explains are totally amazing.  Chapter is not just explaining buffer cache it is also how to tune it.  Knowing the internals of Buffer Cache can be the only reason to have the book because it is nearly the main thing for optimal performance. I really like too much about latch and enqueue subtopics.

7- Oracle Shared Pool Internals(*): This is another reason that makes this book unique.  I already wrote my own compilation about shared pool in a blog post but this chapter taught me many more things like lathes and mutexes in shared pool , In memory undo   and how to tune them. This chapter is also a must to learn to do proper performance firefighting.

8- Oracle Redo Management Internals(*): In this chapter Craig gives how redo management works in Oracle and how to tune redo related issues which is again very important performance related topic.  He goes over  every redo related problem and gives possible solutions to them. Learning the dangerous commit_write parameter usage was  completely new to me.

9- Oracle Performance Analysis(*): In this chapter every topic so far is integrated with each other in  a possible problem scenario. Before going into the scenario Craig cover response service and queue time calculations and prepares us to scenario. In this chapter Craig starts an Overall system performance tuning in OraPub three circle analysis by analysing Oracle OS and Application together. He goes into the analysis three times and this gives reader the clues when and where  to or not to stop tuning.  This chapter totally depends on math and to be honest it is really easy to understands

Now it is time to talk about what I don’t like about the book. Craig introduces and explains too many internal parameters and he gives them as an option in case there is a problem. I loved to learn them because it is very helpful when you talk with Oracle support and also they are part of understanding Oracle but I prefer he mentioned more about not to use them before asking Oracle . He says not to use them couple of times but still not enough to me.   Second thing is that we don’t  have option to download the test case codes Craig mentions apart from OraPub monitoring kit.  Third there is no index at the end of the book.Although I don’t use indexes that much but still nice to have.  One last thing because it is only available from OraPub you might wait a bit if you are ordering outside of US like me, I wish there was an e-book option which I always find it easy but probably it is because of avoiding pricy.

After negative part lets come to the conclusion. This book is very informative very well written and in my humble opinion, a must on every DBA’s desk and I suggest it to every DBA. If you are experienced and read too many performance tuning books and feel comfortable with performance tuning but still struggling to understand latches mutexes buffer cache shared pool  or overall performance tuning and if you want to fully understand them Oracle Performance Firefighting is totally for you. If you are a new for Oracle Performance Tuning,  this book is a must to understand how Oracle works but please be careful about hidden/unsupported parameters.

My only regret is not to order 2 copies one for company and one for me. 🙂 I hope you will like the book as much as I liked.

Many thank to Craig Shallahamer for bringing  deep knowledge and experience about Oracle Performance tuning and sharing in this very special book.