Interestingly, my tests show Oracle's wait interface places significantly more load on the database server than ASH. For example, simply operating system tracing an active server process indicated that 2,938 gettimeofday calls were issued over a 3-minute period, and each call took 0.017 ms. Another 3-minute sample of an active server process made 89,555 gettimeofday calls, with each taking around 0.077 ms. As you can see, the impact is highly variable. My first example would consume 999 ms (2938 _ 20 _ 0.017) of CPU per hour, and the second example would consume 138 seconds (89555 _ 20 _ 0.077) of CPU per hour. Therefore, wait interface overhead is highly variable. Since every system call is instrumented, many short waits would create more overhead than fewer longer waits. Keep in mind that this is just for a single server process, not the total for ten, hundreds, or even thousands of server processes. So if you're concerned about ASH overhead, you should be very concerned about the wait interface overhead.

ASH is fascinating from both an architectural perspective and a usefulness perspective. Many Oracle internals topics are interesting, but few have the potential to be useful in our firefighting efforts. If you have the license to query from the ASH view, I think you'll find using ASH in your OraPub 3-circle analysis extremely beneficial.

This brings us to the end of a wide variety of performance diagnosis topics. Most people in IT think Oracle performance optimization is centered on superior Oracle internals knowledge. I disagree. I believe those who use a solid method, perform a spot-on diagnosis, and then appropriately apply Oracle internals are less likely to guess and much more likely to be successful performance firefighters.