This is my third of four posts about people who have made a major impact on my Oracle database performance tuning journey. This post is about Cary Millsap. The previous two were about Craig Shallahamer and Don Burleson.
I am working through these four people in chronological order. The biggest impact Cary Millsap had on me was through the book Optimizing Oracle Performance which he co-authored with Jeff Holt. I have also heard Cary speak at conferences and we had him in for a product demo one time where I work.
I have delayed writing this post because I struggle to put into words why Cary’s book was so useful to me without repeating a long explanation of the book’s contents. Just before reading the book I had worked on a system with high CPU usage and queuing for the CPU. I had just read the paper “Microstate Response-time Performance Profiling” by Danisment Gazi Unal which talked about why CPU measurements in Oracle do not include time spent queued for the CPU. Then I read Cary Millsap’s book and it was very enlightening. For one thing, the book was very well written and written in a convincing way. But the key concept was Cary Millsap’s idea of looking at the waits and CPU time that Oracle reports at a session level and comparing that to the real elapsed time. This performance profile with waits, CPU, and elapsed time formed the basis of my first conference talk which I gave at Collaborate 06: PowerPoint, Word, Zip
Here is an example of a session profile from my presentation:
TIMESOURCE ELAPSED_SECONDS --------------------------- --------------- REALELAPSED 141 CPU 44.81 SQL*Net message from client 9.27 db file sequential read .16
This is a profile of a session that spent roughly two-thirds of its time queued for the CPU.
Since reading Optimizing Oracle Performance I have resolved many performance problems by creatively applying the concepts in the book. The book focuses on using traces to build profiles. I have made my own scripts against V$ views and I have also used Precise. I have used traces as the book suggests but only with TKPROF. I have not had a chance to use the tool that the book describes, the Method R Profiler.
However I do it the focus is on waits, CPU as reported by Oracle, and real elapsed time all for a single session. It is a powerful way to approach performance tuning and the main thing I learned from Cary Millsap. I highly recommend Cary Millsap and Jeff Holt’s book to anyone who wants to learn more about Oracle database performance tuning because it made such a profound impact on my career.
– Bobby
