Real-Time SQL Monitoring - Retention

Doug's Oracle Blog

  • Home
  • Papers
  • Books
  • C.V.
  • Fun
  • Oracle Blog
  • Personal Blog

Aug 15: 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 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
Posted by Doug Burns Comments: (8) Trackbacks: (3)

Trackbacks
Trackback specific URI for this entry

PingBack
Weblog: www.pythian.com
Tracked: Aug 19, 13:54
Real-Time SQL Monitoring - Retention (part 2)
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
Weblog: Doug's Oracle Blog
Tracked: Sep 22, 07:15
PingBack
Weblog: oraclecommunity.in.ua
Tracked: Sep 23, 08:12

Comments
Display comments as (Linear | Threaded)

#1 - Lothar 2013-11-07 10:02 - (Reply)

Hi Doug,

do you know if it is possible to set a higher treashold for a query to be monitored?
5 Seconds is a bit low for a DWH.

Thanks

Lothar

#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.1 - Lothar 2013-12-30 07:14 - (Reply)

Hi Maxym,

we had to restart the db for that parameter to take effect.

Thanks

Lothar

#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.1 - Doug Burns said:
2014-01-03 08:48 - (Reply)

Happy New Year to you, too.

My first thought was maybe V$SQLSTATS, but that's per statement rather than execution.

#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!


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
 
 

Statistics on Partitioned Tables

Contents

Part 1 - Default options - GLOBAL AND PARTITION
Part 2 - Estimated Global Stats
Part 3 - Stats Aggregation Problems I
Part 4 - Stats Aggregation Problems II
Part 5 - Minimal Stats Aggregation
Part 6a - COPY_TABLE_STATS - Intro
Part 6b - COPY_TABLE_STATS - Mistakes
Part 6c - COPY_TABLE_STATS - Bugs and Patches
Part 6d - COPY_TABLE_STATS - A Light-bulb Moment
Part 6e - COPY_TABLE_STATS - Bug 10268597

A couple of posts about Incremental Stats confusion

Part 1
Part 2

Comments

personal blog about Moving Sideways
Wed, 01.06.2016 17:34
That is a good tip particularl y to those fresh to the blogos phere. Short [...]
odziezprestige.pl about Moving Sideways
Wed, 01.06.2016 16:07
Please let me know if you're l ooking for a article writer fo r your site. [...]
Doug Burns about Moving Sideways
Tue, 10.05.2016 21:43
Oh, I won't give it that long unless I enjoy it ;-)

Bookmark

Open All | Close All

Syndicate This Blog

  • XML RSS 2.0 feed
  • ATOM/XML ATOM 1.0 feed
  • XML RSS 2.0 Comments
  • Feedburner Feed

Powered by

Serendipity PHP Weblog

Show tagged entries

xml 11g
xml ACE
xml adaptive thresholds
xml ASH
xml Audit Vault
xml AWR
xml Blogging
xml conferences
xml Cuddly Toys
xml Database Refresh
xml dbms_stats
xml Direct Path Reads
xml Fun
xml Grid Control
xml hotsos 2010
xml listener
xml locking
xml oow
xml oow2009
xml optimiser
xml OTN
xml Parallel
xml partitions
xml Patching
xml Swingbench
xml The Reality Gap
xml Time Matters
xml ukoug
xml ukoug2009
xml Unix/Shell
xml Useful Links

Disclaimer

For the avoidance of any doubt, all views expressed here are my own and not those of past or current employers, clients, friends, Oracle Corporation, my Mum or, indeed, Flatcat. If you want to sue someone, I suggest you pick on Tigger, but I hope you have a good lawyer. Frankly, I doubt any of the former agree with my views or would want to be associated with them in any way.

Design by Andreas Viklund | Conversion to s9y by Carl