Aug 10: Real-Time SQL Monitoring - Statement Not Appearing

Like Greg Rahn, I've looked at many SQL Monitoring reports over the past year or two. Possibly not as many as Greg, but it's become my default method of communicating* SQL performance issues to colleagues to the point that some might be finding it irritating by now, whilst others are hooked from the start. (Personally, I can't understand those who aren't hooked from the start!)

One of those who have been hooked came to me with a problem last week. He simply couldn't see his report in the OEM SQL Monitoring screen and after asking him if it was really running right now (it was) and attempting a re-run with a /*+ MONITOR */ hint, I was almost stumped. Then I suggested we fall back on using DBMS_XPLAN.DISPLAY_CURSOR to get the plan and when I saw the results, I suddenly understood what the problem was. This was a massive plan! It wasn't a particularly complex query but it was referencing a Data Dictionary view (I can't remember which one now) which expanded out into what looked like hundreds of lines. Which was the problem.

There's a hidden parameter - _sqlmon_max_planlines with a default value of 300 - which limits the number of lines beyond which a statement will not be monitored. This statement exceeded that limit.

A small post, but hopefully useful should you ever wonder why a statement isn't appearing. Some day soon there'll be another post about why you can't find statements that you know executed fairly recently, which is a much more common problem in my day-to-day work. (As a preview, that parameter is _sqlmon_max_plan.) Then another one about the mysterious disappearing tabs!

* Communication is where SQL Mon excels, with the ability to send ACTIVE reports to allow others to dig around in the detail.
Posted by Doug Burns Comments: (11) Trackbacks: (2)

Trackbacks
Trackback specific URI for this entry

Real-Time SQL Monitoring - Retention
As I suggested in my last post, there's at least one more reason that your long-running SQL statements might not appear in SQL Monitoring views (e.g. V$SQL_MONITOR) or the related OEM screens. When the developers at my current site started to use SQL Moni
Weblog: Doug's Oracle Blog
Tracked: Aug 15, 10:34
PingBack
Weblog: oraclecommunity.in.ua
Tracked: Aug 16, 07:26

Comments
Display comments as (Linear | Threaded)

#1 - Dominic Brooks said:
2011-08-11 11:11 - (Reply)

Useful titbit, thanks.

#2 - Kerry Osborne said:
2011-08-12 05:06 - (Reply)

Hey Doug,

I've been using SQL Monitoring extensively for a while as well. This is an awesome bit of info. Thanks for sharing it.

Kerry

#3 - Damir Vadas said:
2011-09-08 08:12 - (Reply)

Which SQL are you using in this example?
Rgm
Damir

#4 - Doug Burns said:
2011-09-09 00:22 - (Reply)

I don't really understand your question

#5 - Damir Vadas said:
2011-09-09 12:35 - (Reply)

Doug,

what SQL statement is underlying this report/graph shown in the start of blog.
hope now I'm more clear ...
Rg,
Damir Vadas

#6 - Doug Burns said:
2011-09-11 23:51 - (Reply)

I'm still not sure. I can't see a report/graph at the start of this post.

Do you mean this bit?

This was a massive plan! It wasn't a particularly complex query but it was referencing a Data Dictionary view (I can't remember which one now)

If so, I said I can't recall which view the query was against, which means I can't remember the query either.

#7 - Damir Vadas 2011-09-12 06:52 - (Reply)

Hi!

On page
"http://oracledoug.com/serendipity/index.php?/archives/1646-Real-Time-SQL-Monitoring-Retention.html"
there is graph showing data.

This is the SQL I'm talking about.

Or did I miss something?
THX

#8 - Doug Burns said:
2011-09-13 21:43 - (Reply)

Oh, right. It might have been a bit clearer if you'd left the comment on that post, rather than on this post with the comment ...

"what SQL statement is underlying this report/graph shown in the start of blog."

I was re-reading this post, trying to work out what you were asking about.

Now that you point out the correct post, that is just the usual SQL Monitoring main page in OEM Grid or Database Control.

Does that answer your question?

Cheers,

Doug

#9 - Damir Vadas said:
2011-09-14 07:08 - (Reply)

Doug,
Yap
Rg,
Damir

#10 - Dominic Brooks said:
2011-11-02 14:44 - (Reply)

I knew this info would come in handy.

It just rang a big bell when I tuned a sql statement but trouble is the plan's gone from less than 200 lines to more than 400 so it's not in sql monitoring.

Now I could change that parameter or take up the challenge to keep all tuned plans to less than 300 lines....

#11 - Doug Burns said:
2011-11-02 15:24 - (Reply)

Strangely, we had the same example crop up yesterday too. On the one hand, I think they should get the plan below 300 lines, on the other, those are *just* the queries which might need SQL Mon the most!

Coming along this evening?


Add Comment

Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
BBCode format allowed