This was first published on https://blog.dbi-services.com/sql-monitor-report-little-improvement-in-12c (2015-05-18)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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.
I always used the graphic interface. Those little SELECT are so nice, works perfectly !
Thanks for sharing ! Big data training in chennai | Informatica training in chennai | PHP Training in Chennai | Web Designing Training in Chennai
Thanks for this info, I didn’t pay attention on that until now.