Tuesday, June 21, 2005

Oracle Wait Interface

The conventional wisdom of tuning Oracle databases has undergone a shift in the last five or so years. Tuning an Oracle database used to mean measuring various ratios and tweaking init.ora parameters so the ratios were "right". Maybe the users' response got better, maybe it didn't. But the database was running better. Some people built businesses around this black magic of tweaking init.ora parameters so the numbers looked "right" without ever really solving the root cause of the slowness. Tuning today means resolving the root cause of the problem and making the users response time acceptable. A large part of measuring performance is the Oracle Wait interface.

I first learned about the Oracle Wait Interface (OWI) was in an informal conversation with Roman Kab from Optimum Computing after a users group meeting. Roman talked about some new 8i features and he mentioned the OWI and the higher degree of instrumentation. One of the most helpful parts of the new "Statspack" report was the top wait events. I thought it was interesting that my instance waited on "db file sequential read" the most, but what statement caused the majority of these waits? I had a good buffer cache hit ratio, so it couldn't be that relavent, could it?

For me, tkprof in version 9.2 brought the OWI to the forefront of wait event tuning. The ability to see which waits were affecting the longest running statements was a major step in diagnosing performance problems. I experience several "Ah-ha" moments when I looked the new 9.2 traces.

Two new books on the OWI are Optimizing Oracle Performance by Cary Milsap and Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (OWI:Practical) by Richmond Shee, Kirtikumar Deshpande and K. Gopalakrishnan. Optiming Oracle Performance is a roadmap of how to use information garnered from OWI and use it in a reproducable scientific method to solve performance problems. In this book, the author explains queueing theory in terms most DBAs can understand. In Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning by Richmond Shee, Kirtikumar Deshpande and K. Gopalakrishnan, the authors delve into each of the most populate wait events and explain in detail how to diagnose and resolve the different conditions which might cause excessive waits. Where Optimizing Oracle Performance was heavy in the theories behind the OWI, OWI:Practical concentrates on how to use the wait events in everyday situations.

I suggest reading OWI: Practical first to get a handle on how the wait interface works and how it can help you in day-do-day performance tuning. Once you get some practice with OWI and tkprof, you will be able to better comprehend Optimizing Oracle Performance and appreciate the performance tuning methodlogy outlined in it.


Amar said...

Hey i just bought Carry Milsap's book. Now you say read Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning before reading Optimizing Oracle Performance.

Can't you be quicker...

Jokes apart, I read the first chapter of Milsap's book, and got a blink on Method R and Method C. Let me see when i can finish it, then will have something to discuss about :)

Jeff Hunter said...

Hey, that's the same way I did it. I'm not saying you won't get a lot out of it, it just makes more sense after reading OWI:Practical.

Robert Vollman said...

I have both books, and enjoyed both. I even have a review of Millsap's book:


If you go there, you will also find links to the 1st chapter, the last chapter, and 3 of Millsap's best articles. Including one related to his book.

On this topic, I also recommend reading the YAPP paper. Actually, Tim Hall has a link to many articles, including Anjo Kolk's OraPerf:


fellow Oraclite said...

Thanks Jeff. I have both the books but I havent read them yet. When I start reading I will start with the OWI. :)


Hi Jeff,
I have almost finished reading the core parts of Cary's book.His approach of accounting for User Response time is highly appreciated and the clarity in walking thro a raw trace file generated by an extended sql trace is too good. Having said that i feel that the most difficult practical problem in taking the approach is in collecting the proper user&time scoped diagnostic data. May be as Appl.servers advance this might become more easier.Still one needs patience but no doubt a good book explaining kernel diagnostic data in simpler terms.