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.