{"id":1669,"date":"2012-01-10T12:00:00","date_gmt":"2012-01-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1669"},"modified":"2012-01-10T12:00:00","modified_gmt":"2012-01-10T12:00:00","slug":"ukoug-2011-ash-outliers","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2012\/01\/10\/ukoug-2011-ash-outliers\/","title":{"rendered":"UKOUG 2011 – Ash Outliers"},"content":{"rendered":"

My final UKOUG 2011 post is about another of my favourite presentations –\u00a0 “ASH Outliers: Detecting Unusual Events in Active Session History<\/em>” by John Beresniewicz. (JB for short, but Marco Gralike<\/a> made a fairly good stab at pronouncing his surname correctly during the introduction.)<\/p>\n

I’d been looking forward to this presentation because I’d already been aware of his work in this area for a while and was supposed to be helping out (but I’ll come back to that later …). I’d also expected to see it in 2010 but JB withdrew the abstract later. Although I knew a lot of the content already I enjoyed it because of the subject and because JB’s always likely to make me see something old from a new angle. Anyway, what was it all about?<\/p>\n

One of the most important limitations of ASH data is that, because the data is sampled, it doesn’t include every individual timed event that sessions waited for and is inherently biased towards longer duration (and more common) events. As long as you understand this design decision then you can understand sensible ways to use the data and also less sensible ways!<\/p>\n

For example, it is not<\/em><\/strong> sensible to write queries against ASH data that SUM(time_waited) or AVG(time_waited) because the results look sensible but are fairly meaningless. If you sum all the wait times in ASH but ASH doesn’t include all activity then what does the total value represent? Likewise, if you calculate the average ‘log file parallel write’ time in ASH for a specific period, it’s virtually guaranteed that the result will be higher than the true average because ASH will tend to capture more long duration events than short ones.<\/p>\n

Generally it’s more sensible to use simple COUNT functions and make the assumption that a sample represents a second of time. It’s obviously an approximation, but one that works surprisingly well as reasonably long experience of the OEM performance pages and ASH queries has shown me.<\/p>\n

However, although the Oracle employees I’ve discussed this with encourage people to use the TIME_WAITED column in ASH with caution, I’ve personally found it useful on many occasions when ASH is the only session-level data available because I’m trying to diagnose a problem after the event. In fact, ASH can be extremely useful in that situation because, as well as having session-specific information, it contains it for all the sessions. So I confess that when querying ASH data using SQL, I’ve often found the TIME_WAITED of individual rows to be a useful indication of what’s been going on if unusual or particularly long waits appear immediately prior to a serious problem. By looking at the series of events that led up to a problem, I can see the interactions between those sessions via blocking session information and at least build up an approximate view of what happened.<\/p>\n

JB’s presentation was essentially about a single query that he’s been working on that is designed to look at the TIME_WAITED values in ASH data to identify outliers – events with unusually long wait times relative to the usual wait times for that event. The thinking being that the appearance of long wait times for certain events could indicate the root cause of serious system-wide performance problems. (Note that AWR data would be next to useless for this because the problems might only occur for a few seconds in the lead-up to the problem and that level of detail would be aggregated to insignificance in the wider AWR time scope.)<\/p>\n

Having hopefully said enough technical things in this post now to keep grumpy people happy, allow me a humorous interlude. From the start of the presentation, JB kept alluding to a couple of things<\/p>\n

a) The singular lack of response he’d had from people he’d shared the query with. Fortunately no names were mentioned but he was clearly griping about the limited response he’d had from me after sending me the query a while back and how long it had taken. What can I say? I’m *busy*! 😉<\/p>\n

b) Why the feedback is so important to him – because people in Oracle Development appear very interested in what customers are doing on their real Production systems. Even though Oracle do have their own internal Production systems, they’re only a small fraction of what customers are doing so it’s difficult to know what will work. In any case, I guess Oracle don’t let developers play around on their production systems, like most companies.<\/p>\n

So here’s the deal. As JB mentioned, he is probably the least on-line guy there is. He might do DBA 2.0, but he doesn’t do Web 2.0! So he suggested that I could get the script out there to other people to try on their own interesting systems and see whether they found it useful.<\/p>\n

Here it is<\/a><\/p>\n

Note that this version references the V$ rather than GV$ version of ASH data so only uses officially supported features as JB alluded to during the presentation. i.e. a RAC version is possible but this is single-instance for now, just to let people give it a try.<\/p>\n

To save a veritable deluge of mail (yeah, JB, you *wish*! LOL) it might be best to post any feedback on the script as comments here or I could forward it on.<\/p>\n

Oh, and something I heard later that a few people had struggled with during the presentation was the concept of Significance Levels that are central to the script and the presentation. Fortunately, I already host a short document on the subject, again courtesy of JB, here<\/a>. However, as that’s more focussed on Adaptive Thresholds and time grouping in particular, you might find the discussion on this AskTom thread<\/a> more generic and relevant to the ASH Outliers script.<\/p>\n

All in all, a terrific presentation, as I expected and something that people might want to try out. It\u2019s certainly an interesting concept and an attempt to automate something I\u2019ve been doing manually for a few years. Thanks, JB!<\/p>\n","protected":false},"excerpt":{"rendered":"

My final UKOUG 2011 post is about another of my favourite presentations –\u00a0 “ASH Outliers: Detecting Unusual Events in Active Session History” by John Beresniewicz. (JB for short, but Marco Gralike made a fairly good stab at pronouncing his surname correctly during the introduction.) I’d been looking forward to this presentation because I’d already been… Continue reading UKOUG 2011 – Ash Outliers<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1669","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1658,"url":"http:\/\/orcldoug.com\/blog\/2011\/11\/07\/upcoming-ukoug-conference\/","url_meta":{"origin":1669,"position":0},"title":"Upcoming UKOUG Conference","date":"November 7, 2011","format":false,"excerpt":"I always look forward to this time of year as the annual UKOUG conference in Birmingham approaches but this year I think I'm looking forward to it more than ever.As I'll have delivered both of my own presentations at previous events my slides will be done before I arrive and\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":930,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/21\/ukoug-agenda\/","url_meta":{"origin":1669,"position":1},"title":"UKOUG Agenda","date":"October 21, 2005","format":false,"excerpt":"What's good enough for Niall is good enough for me ... Here's my agenda for the UKOUG conferenceSunday - Oak Table day. Like Niall, I won't be there at the start, but even half a day of this standard will add a lot to the conference for me. Then onto\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1667,"url":"http:\/\/orcldoug.com\/blog\/2011\/12\/13\/ukoug-2011-oak-talks-and-unconference\/","url_meta":{"origin":1669,"position":2},"title":"UKOUG 2011 – OAK Talks and Unconference","date":"December 13, 2011","format":false,"excerpt":"There were a couple of new and related features at this years conference - the Unconference and OAK Talks.The idea of an Unconference will be familiar to those who have attended Openworld - an unscheduled part of the conference that anyone can sign up for to present during a one\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1460,"url":"http:\/\/orcldoug.com\/blog\/2008\/12\/05\/ukoug-day-3\/","url_meta":{"origin":1669,"position":3},"title":"UKOUG Day 3","date":"December 5, 2008","format":false,"excerpt":"After more work on the presentation and some very strange sleeping hours thanks to the raucuous ranks on Broad Street, it was time for the first of my presentations. But before that I wanted to attend JB's \"Proactive Detection of Oracle Performance Events using Adaptive Thresholds\" (skipped), the always entertaining\u2026","rel":"","context":"With 14 comments","img":{"alt_text":"","src":"\/serendipity\/uploads\/lion.jpg","width":350,"height":200},"classes":[]},{"id":876,"url":"http:\/\/orcldoug.com\/blog\/2006\/01\/23\/ukoug-2005-presentations-cd\/","url_meta":{"origin":1669,"position":4},"title":"UKOUG 2005 Presentations CD","date":"January 23, 2006","format":false,"excerpt":"Peter Robson, one of the directors of the UKOUG asked if I'd post the following ...\"You will remember the CD that was made of some of the presentations atOUG last autumn.Could you ask, via your blog, if anyone brought a copy of this CD, andif they would be prepared to\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1362,"url":"http:\/\/orcldoug.com\/blog\/2007\/12\/04\/ukoug-blogger-meet-up-and-day-two\/","url_meta":{"origin":1669,"position":5},"title":"UKOUG Blogger Meet-up and Day Two","date":"December 4, 2007","format":false,"excerpt":"I managed to work on my presentation a bit yesterday afternoon. I was discussing this will Niall later on and there's a point when you know you are going to be finished and you've broken the back of it, but you still have a lot of work to do. That's\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1669","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1669"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1669\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1669"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}