{"id":1487,"date":"2009-04-20T12:00:00","date_gmt":"2009-04-20T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1487"},"modified":"2009-04-20T12:00:00","modified_gmt":"2009-04-20T12:00:00","slug":"diagnosing-locking-problems-using-ash-part-6","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2009\/04\/20\/diagnosing-locking-problems-using-ash-part-6\/","title":{"rendered":"Diagnosing Locking Problems using ASH \u2013 Part 6"},"content":{"rendered":"

“Toto, I’ve a feeling we’re not in Kansas any more.<\/a>“<\/p>\n

What started as a simple write-up of a course demo gone wrong (or right, depending the way you look at these things) has grown arms and legs and staggered away from the original intention to talk about ASH. so I thought it might be worth taking a breather and see where we\u2019ve been so far, before pressing on with another couple of posts.<\/p>\n

Part 1<\/a> \u2013 Introduced a blocking lock problem and showed how it would look in the OEM DB\/Grid Control GUI.<\/p>\n

Part 2<\/a> \u2013 Demonstrated that you might not see any ASH samples for the blocking session if it doesn\u2019t generate enough activity to be captured.<\/p>\n

Part 3<\/a> \u2013 Demonstrated that even if the blocking session *has* been active enough to be sampled, you might not have the specific activity that caused the problem.<\/p>\n

Part 4<\/a> \u2013 Reinforced part 3 to show that you might *think* you\u2019re looking at the SQL statement which was holding the blocking lock when you actually aren\u2019t and highlighted JB\u2019s point that there is no way of knowing the SQL statement that was holding a particular lock. I see that Jonathan Lewis has posted an illustration of this<\/a> although let\u2019s wait to see if Tanel Poder comes up with something 😉<\/p>\n

I decided at this point to draw some conclusions as to the limitations of ASH as a tool in diagnosing locking problems after they\u2019ve cleared. In amongst the meandering detail, it’s important to reiterate that objective. There are lots of ways of diagnosing locking problems that are happening right now or that you expect to happen in future.<\/p>\n

Part 5<\/a> \u2013 The same subject cropped up on the Oak Table list and in amongst the suggestions were a couple to use LogMiner. One was from Kyle Hailey and I posted his scripts and output verbatim, but there were a few cut and paste errors and so I was faffing around with the post for a while and eventually added a comment to say I\u2019d fix it later. I\u2019ve now done so, re-running the tests with the correct code and removed the comment from the post. Sadly, none of the corrections fixed the two central problems with this approach<\/p>\n

a) Kyle and I have both found LogMiner behaviour to be very flaky indeed and it doesn\u2019t seem to return the results I\u2019d expect, over multiple tests by two different \u2018testers\u2019. (See the last post<\/a> for an example.)<\/p>\n

b) I had to change the examples to use UPDATE statements rather than SELECT FOR UPDATEs. <\/p>\n

I\u2019ll be looking at these two issues in Parts 7 and 8 (I think) but who knows what turn these posts will take next! Here\u2019s where I think I might be heading<\/p>\n

Part 6 \u2013 You\u2019re reading it.<\/p>\n

Part 7 \u2013 Redo log dump confirmation that the LogMiner queries in Part 5 aren’t showing what I’d expect.<\/p>\n

Part 8 \u2013 Redo log dump confirmation that redo entries won\u2019t help with the original SELECT FOR UPDATE example anyway.<\/p>\n

Part 9 \u2013 Can I stop now, or should I attempt to reach a double-digit part number for the first (and hopefully last) time?<\/p>\n","protected":false},"excerpt":{"rendered":"

“Toto, I’ve a feeling we’re not in Kansas any more.“ What started as a simple write-up of a course demo gone wrong (or right, depending the way you look at these things) has grown arms and legs and staggered away from the original intention to talk about ASH. so I thought it might be worth… Continue reading Diagnosing Locking Problems using ASH \u2013 Part 6<\/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-1487","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1480,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/31\/diagnosing-locking-problems-using-ash-part-3\/","url_meta":{"origin":1487,"position":0},"title":"Diagnosing Locking Problems using ASH – Part 3","date":"March 31, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.In the last part I described a locking scenario where the blocking session had only executed one SQL statement that was quick enough to avoid being sampled by ASH and is now inactive. As a result, there is no ASH data\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1478,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/30\/diagnosing-locking-problems-using-ash-part-2\/","url_meta":{"origin":1487,"position":1},"title":"Diagnosing Locking Problems using ASH – Part 2","date":"March 30, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I tried the graphical approach to tracking down the root cause of a locking problem in the last part. Now let's look at the underlying ASH data. I ran the same test after bouncing my test instance to start from a\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1477,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/30\/diagnosing-locking-problems-using-ash-part-1\/","url_meta":{"origin":1487,"position":2},"title":"Diagnosing Locking Problems using ASH – Part 1","date":"March 30, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.There are few better illustrations of the utility of ASH data than the ability to detect and diagnose locking problems*, even after they've occurred. Because ASH is sampling all active sessions all of the time, you should have information both for\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1481,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/09\/diagnosing-locking-problems-using-ash-part-4\/","url_meta":{"origin":1487,"position":3},"title":"Diagnosing Locking Problems using ASH \u2013 Part 4","date":"April 9, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.No sooner had I finished part 3 with some conclusions than I thought of another example I should have included and then someone else made a comment in an email which suggested another. (Thanks, JB!) \"It's probably worth pointing out that\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1669,"url":"http:\/\/orcldoug.com\/blog\/2012\/01\/10\/ukoug-2011-ash-outliers\/","url_meta":{"origin":1487,"position":4},"title":"UKOUG 2011 – Ash Outliers","date":"January 10, 2012","format":false,"excerpt":"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\u2026","rel":"","context":"With 11 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1484,"url":"http:\/\/orcldoug.com\/blog\/2009\/04\/17\/diagnosing-locking-problems-using-ash-part-5\/","url_meta":{"origin":1487,"position":5},"title":"Diagnosing Locking Problems using ASH \u2013 Part 5","date":"April 17, 2009","format":false,"excerpt":"... and so it continues.Coincidentally, the subject of tracking down locking problems after they occurred cropped up on the Oak Table mailing list just after the last post. Several suggestions were offered but I think the nearest and most detailed suggestion was that proposed by Kyle Hailey, ex-Oracle and now\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1487","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=1487"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1487\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1487"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}