By Franck Pachot
.
This is a very short post about something I though I had already blogged about.
I like SQL Monitoring active reports. The are graphical, and still very light. There is only one thing I don’t like about it in 11g and it has been improved in 12c
Look at the following. The query has run for 1.1 minute. And from the ‘Database Time’ line we can see that it used mostly CPU. If you’re not familiar with the color, just move the mouse above it and you will see the wait event and the number of samples.
Then I want to check which line in my execution plan is responsible for most of that time:
Look: there is no ‘Database Time column’. There is one for CPU and one for wait events. But that is not very helpful and misleading. Above, it looks like a large percentage of samples concerns wait events. But that’s only the percentage relative to wait events.
Because I’ve seen before that most of the time is spent on CPU, I have to look at the CPU column only.
12c
This has changed in 12c. there is only one column called ‘Activity %’ so the lines with a high percentage are those where the most of time is spend:
Remember, this is sampling, not profiling. But if you apply the percentage to the total time, then you have a good estimation of the time spend on each part of the plan.
(the screenshot above has no sample about the I/O that occured, but the will show in the same column)
This is very useful when you have a long query with a long execution plan.
Of course, this requires Tuning Pack. I’ll share in a future post how I approach that when having only Diagnostic Pack.