One of the best new features of 11g from a diagnostic standpoint is the Real Time SQL Monitoring capabilities. I did a post about it a couple of years ago here: Oracle 11g Real Time SQL Monitoring In that post I talked about a procedure (DBMS_SQLTUNE.REPORT_SQL_MONITOR) that provides a very nicely formatted explain plan type output which contains quite a bit of useful information. Well, it has recently come to my attention that the report contains a column that shows cell offloading. That’s pretty cool. Here’s a script to call that procedure, report_sql_monitor.sql, and an example (note the format is really wide so be sure and use the scroll bar at the bottom to see the columns on the right of the output):

So as you can see, this is a parallel query. For parallel queries there is a separate section that shows what each slave process did. There are a couple of columns that are particularly interesting in this section. The Cell Offload column shows the percentage reduction in data transferred to the database tier that resulted from offloading. The Wait Events column shows what the slaves waited on. In the Plan Details section you can see that the plan included a full table scan and that it was offloaded. This section has columns that show the same basic information (although the column names are slightly different), but it’s organized by plan step. This is extremely useful on complex statements that have multiple steps which may be offloaded. This section also clearly shows which steps are taking the most time (Activity %).

Here’s a more complicated example. The statement in this example doesn’t have a lot of steps, but the output is very useful in understanding what is happening. Again, be sure to scroll over to the right to see the interesting bits.

In this example, we have an update statement that took a while, even though it was using Smart Scans. So what gives? When we looked at the output from SQL Monitor it was clear that it wasn’t spending all it’s time looking for the records to update, but rather on the update itself. As you might have guessed from the name of the table, this statement was attempting to update every row in a table that had been compressed using HCC (not something you would normally want to do). This caused a lot of extra work, migrating the rows to OLTP compressed blocks. So that’s what’s going on under the covers. The point of the example is that the report quickly focuses our attention on the area that needs investigation.

By the way, SQL Monitoring is supposed to kick in automatically for long running statements (over 5 seconds by default) and for parallel statements. Occasionally though we would like to see the SQL Monitoring Report on a statement that is not being monitored for some reason. Fortunately, there is a hint (MONITOR) that can be used to force SQL Monitoring. In situations where we don’t have access to the code, like a packaged app for example, we can apply the hint via a SQL Profile. I created a little script called fm.sql to do just that. And no fm doesn’t stand a radio frequency range, and no it doesn’t stand for some kind of magic, and no it’s not the name of a Steely Dan song (well it is, but that’s not why I called it that). It stands for “force monitoring”. Here’s an example:

So there you have it. A quick and dirty way to enable Real Time SQL Monitoring on a production statement that wasn’t being monitored by default.

I have really been using this capability a lot lately. Being able to see which steps in a complex plan are offloaded and what wait events are affecting which steps is very useful. The “Activity %” column which shows which steps the statement spent all it’s time on is also pretty helpful. It’s a tool that has moved very close to the top of my bag.

I have not run into that. The script expects a SQL_ID to be entered or for all values to be left blank (which runs the report for the last long running query issued by this session). Try entering a value for the SQL_ID or leaving all prompts blank.

Hello Kerry,
A question on sql execution times when it is run parallel? Have you tried calculating it via “A-Time” in execution plan or through v$sql? I have several queries running with a parallel degree 2 on some of the big partitioned tables. If I execute one, typically I find two rows in v$sql, one belonging to the QC (possibly) and the other row for the total number of slaves used. In my case, though the line that belongs to the QC (EXECUTIONS 1, PX_SERVER_EXECUTIONS 0) gives the elapsed time more closer to the reality, the one for slaves (in this case I see PX_SERVER_EXECUTIONS 4 and EXECUTIONS 0) is larger than 4 times the response time of the QC.
I would normally expect the time taken by all the slaves would be closer to the sum of my real response time (or closer to that).
I may be missing something here. If it is evident to you, please share the information. Appreciate your help through this wonderful blog.

I’m not sure I am understanding your question on this post. In recent versions of Oracle V$SQL should have only one entry for a parallel query. It should have a correct count for the number of executions (1 per actual execution of the entire query) and should have the correct number of slaves assigned to it in px_servers_executions (i.e. the total number of slaves to work on however many executions were done). The elapsed_time field should have the total elapsed time of all the slaves combined. But I didn’t really talk about any of that in this post. The report_sql_monitor procedure provides an elapsed time in the header of the report which seems to be pretty accurate (at least I haven’t noticed any big discrepancies).

Thanks for the response Kerry. Since I could not find an appropriate post I just shot this question here. Yes, I do use OEM’s SQL montioring feature that tells me the time. But I had one case where there was a discrepancy in terms of time taken by one of the slaves was more than the elapsed time (end-user’s view of). That SQL somehow aged out of monitoring ( at least the execution times). I wish I could have cross-verified this before it was flushed.

Hi Kerry,
If I want to ask a question, which page would be more appropriate? I mean I don’t want write in a post that you rarely visit.
This time I have a question on SQL MONITORING page on OEM. If I move my mouse on the time bars, it gives me two types of information, First Active and Duration.
Have you noticed it? If yes, how do I interpret it? Thanks for the help.