In Oracle Database SQL Monitor is an easy to use tool for getting details about SQL statement execution. We use it all the time for Database In-Memory queries. I even wrote a technical brief to explain how to create and use SQL Monitor active reports. Maria Colgan also did a nice post about Getting the most out of Oracle SQL Monitor, and I have done a couple of Ask TOM Office Hours sessions on SQL Monitor, the latest being about the new updates that have been added.
However, many people still don’t realize that there are different report types with SQL Monitor and they are not all equal. I will go so far as to say that the only SQL Monitor report you should ever use is the “active” report. The biggest reason for this is that only the “active” report will identify the “Activity” or where, and more importantly how, time was spent in the execution plan steps. There is also an “Activity” section in the other report types (i.e., TEXT and HTML), but it is not used. It is a long standing bug that does not appear will ever get fixed.
With Database In-Memory, a SQL Monitor active report will differentiate CPU time spent “in-memory” and other CPU time. How this is done will be detailed in a subsequent blog post, but suffice it to say that this and other information is captured in Active Session History (ASH) in a very lightweight manner. The bottom line is that only the active report will display this information.
Here is an example of a SQL Monitor active report with Database In-Memory CPU time shown. Notice that the color of the activity bar is different and by hovering the mouse pointer over the bar we see that this time represents “Cpu: in memory”, how many samples were observed and the overall percentage of total time.
SQL Monitor active reports do have their limitations. I pointed this out in the Ask TOM session. Since SQL Monitor is based on ASH data things like parse time and non-idle wait events are not directly attributable in a SQL Monitor active report. Session statistics are also not available. This doesn’t mean that it is not a useful tool. It just means it is one of several tools that you may need to use to properly diagnose where time is spent in a SQL execution.
Here is an example:
Notice in this snippet of a Time & Wait section that the query Duration, or elapsed time, is much longer that the Database Time. Unfortunately there is no easy way in a SQL Monitor active report to determine where the additional time was spent. In this example, the additional time was spent in an “idle” wait event. An extended SQL trace file for this execution shows us a more complete time picture:
Notice that most of the additional Duration time was spent in the SQL*Net message from client wait event.
I will admit we’ve taken a slight detour, but this was really just to show that a SQL Monitor active report does have limitations, mainly imposed by the source data used which is ASH data. The bottom line though, is that in most cases a SQL Monitor active report is easy to generate and easy to use to visualize the SQL execution plan and where execution time was spent. And in the case of Database In-Memory, the only easy way to differentiate CPU time spent in “in-memory” versus other CPU time.