Aug 15: Real-Time SQL Monitoring - Retention
When the developers at my current site started to use SQL Monitoring more often, they would occasionally contact me to ask why a statement didn't appear in this screen, even though they knew for certain that they had run it 3 or 4 hours ago and had selected 'All' or '24 Hours' from the 'Active in last' drop-down list.
I noticed when I investigated that some of our busiest test systems only displayed statements from the past hour or two, even when selecting 'All' from the drop-down. I asked some friends at Oracle about this and they informed me that there is a configurable limit on how many SQL plans will be monitored that is controlled by the
_sqlmon_max_plan hidden parameter. It has a default value of the number of CPUs * 20 and controls the size of a memory area dedicated to SQL Monitoring information. This is probably a sensible approach in retrospect because who knows how many long-running SQL statements might be executed over a period of time on your particular system?I included this small snippet of information in my SQL Monitoring presentations earlier this year because it's become a fairly regular annoyance and planned to blog about it months ago but first I wanted to check what memory area would be increased and whether there would be any significant implications.
Now that I've suggested to my client that we increase it across our systems I had a dig around in various V$ views to try to identify the memory implications but didn't notice anything obvious. My educated guess is that the additional memory requirement is unlikely to be onerous on modern systems but would still like to know for sure and so I'll keep digging but, if anyone knows already, I'd be very interested ...
Updated later - thanks to Nick Affleck for pointing out the additional 's' I introduced on the parameter name. Fixed now to read _sqlmon_max_plan
#1.1 - Doug Burns said:
2013-11-11 15:12 - (Reply)
Hi Lothar,
Sorry - I almost missed this comment because Serendipitys spam detection is so good it catches so much spam that I assumed everything is these days 
There is a hidden parameter that can be used to change this ...
_sqlmon_threshold (default is 5)
CPU/IO time threshold before a statement is monitored. 0 is disabled
Hope that helps.
Cheers,
Doug
#2 - Maxym Kharchenko said:
2013-12-27 22:07 - (Reply)
Hello Doug,
Hopefully you are still into sql monitoring 
Have a question for you - Do you know if there is anything that needs to happen *in addition* to changing: _sqlmon_max_plan to capture *more* executions ? I.e. bounce db, re-parse statements, "wait" ?
I am trying to use v$sql_monitor to capture *all* executions of a certain statement over (say 1 minute) time interval, by adding MONITOR hint with sql_profile. My expectation is to capture: 1000-2000 individual executions.
The problem that I am facing is that while I can change the value of _sqlmon_max_plan (both ALTER SESSION and ALTER SYSTEM), it seemingly has no effect as the # of "captured runs" is still limited by the default ("# of threads"*20 = 480 in my case), albeit all of the "runs" are replaced by the sql_id that I monitor.
I tried to play with other _sqlmon parameters but so far to no avail.
Any idea how to workaround that ?
11.2.0.2 RH Linux x64
Thank you,
Maxym Kharchenko
#2.2 - Doug Burns said:
2013-12-30 10:40 - (Reply)
Hopefully you are still into sql monitoring
Always!
As Lothar pointed out, you would need to bounce the database instance after the parameter change.
However, even if you try that, more experience of SQL Mon has taught me that sometimes it doesn't behave quite as I expect when it comes to the number of executions that would be captured.
I do want to ask, though ...
I am trying to use v$sql_monitor to capture *all* executions of a certain statement over (say 1 minute) time interval, by adding MONITOR hint with sql_profile. My expectation is to capture: 1000-2000 individual executions.
Why would you want to do that in particular? It's not the most obvious use-case for SQL Mon so I'm wondering what you're hoping to achieve?
Cheers,
Doug
P.S. Sorry for the slow reply - I am receiving so many spam comments these days that it's becoming too easy to miss the geuine ones occasionally!
#2.2.1 - Maxym Kharchenko said:
2014-01-02 19:02 - (Reply)
Hello Doug,
No problem, thanks for the reply
That's kind of what I thought (too bad too).
To answer your question - my goal is to capture 90 (99, 99.5 etc) percentile of "elapsed time" of a particular sql statement, in other words, find out the timing for "the worst 10% (1% 0.5% etc)" of all the runs.
I realize v$sql_monitor was not exactly designed for that, but, hey - it has the ability to capture individual executions and you use all the tools at your disposal, eh?
10046 tracing is a bit cumbersome for what I am trying to achieve (I need sql access to results), but v$sql_monitor seemed intriguing (and, alas, I can have, at least, *some* use of it w/o the bounce - as long as my result set is smaller than default).
There is also looping over v$sql/v$session (kind of like what Tanel Poder does in http://blog.tanelpoder.com/files/scripts/waitprof.sql), but it is (I think) not as precise as v$sql_monitor.
Anyway, thanks for the reply and Happy New Year!
Maxym Kharchenko
#2.2.1.2 - Doug Burns said:
2014-02-07 08:20 - (Reply)
Ah, your comment makes even more sense now
http://intermediatesql.com/performance/how-to-track-sql-performance-part-3-getting-down-to-business/
Nice post!
Tracked: Aug 19, 13:54
As I mentioned in my last post, I've been looking at increasing the SQL Monitoring Retention at my current site using _sqlmon_max_plans but, as well as confirming with Oracle Support that they're happy for us to do so, it would be nice to know what the re
Tracked: Sep 22, 07:15
Tracked: Sep 23, 08:12