Skip to Main Content
  • Questions

  • sql monitoring not working when plan is too long ?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sokrates.

Asked: November 07, 2011 - 1:12 pm UTC

Last updated: November 09, 2011 - 12:41 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

I have a quite complex view which is queried a small number of times (10 or so) per day
Execution time is up to some (5) minutes
Execution plan for the most common query against this view is quite complex and has 339 steps at the moment

I noticed, that the queries are never monitored (I mostly use OEM to follow SL Monitoring and never can see them there).
STATISTICS_LEVEL=typical

Setting STATISTICS_LEVEL to all does not help either.

Adding /*+monitor */ hint has also no effect.

What could be the cause for that ?

and Tom said...

I've searched around and did not find any outstanding issues regarding this. It might be a bug if the monitor hint didn't work for it.

For this one, I'll have to ask you to work with support to see if it is.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Sokrates, November 09, 2011 - 3:30 pm UTC

I just created
SR Number 3-4901192501
for that

finally I found the answer

Sokrates, January 29, 2015 - 3:04 pm UTC

... interestingly, support couldn't help me.

But the answer is very simple:
ALTER SYSTEM SET "_sqlmon_max_planlines"=1000 SCOPE=MEMORY;

fixed my issue.

The plan was just too long for the setting of this ( it was 300 on my system).

What I do not understand:
- why it is not documented that there is a limit of plan lines beyond which a plan cannot be monitored ?
- why does support not know about that ?

By the way:
is there a way to translate "old asktom URLs" into new ones ( I have loads of them in comments of my code , for example what is http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:42304816945767 now ? )

I bet we can do it in SQL :-)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.