{"id":1050,"date":"2006-08-10T12:00:00","date_gmt":"2006-08-10T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1050"},"modified":"2006-08-10T12:00:00","modified_gmt":"2006-08-10T12:00:00","slug":"historical-perspective-part-2","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/08\/10\/historical-perspective-part-2\/","title":{"rendered":"Historical Perspective (Part 2)"},"content":{"rendered":"

Let’s look at items 2 and 3 from the list in my previous blog<\/a><\/p>\n

1) You know you have a performance problem and can re-create it by running a specific part of the application, be it a user interaction or batch job.
2) You have an intermittent but recurring performance problem which looks system-wide and can’t\u00a0 be attributed to a particular part of the application.
3) You had a system-wide performance problem yesterday afternoon that hasn’t occurred before.<\/p>\n

In the case of 2, we’re expecting the problem to reoccur at some point so we can start collecting data and wait for it. 3 (which might become an example of 2 eventually) is trickier because what if this was a complete one-off? Without a history of performance metrics we’ll never know what caused the problem, no matter how much management pressure there is for answers. We have to accept it was ‘one of those things’.<\/p>\n

Both cases require some form of system-wide monitoring to be in place before<\/i> the performance problem occurs. Otherwise, you’ll have absolutely no relevant information available and consequently have no chance of solving it. <\/p>\n

Here are a couple of options<\/p>\n

a) System-wide event 10046 tracing.
b) Statspack.<\/p>\n

Option a is far too expensive for all but the most simple systems or benchmarking exercises. I’ve used it in the past for a small section of a complex batch process and the output was far too complex for sensible analysis (although you could also read that as an advert for profiling tools) and certainly not something that should be enabled constantly in the expectation that something might<\/i> happen one day.<\/p>\n

Option b has been criticised by a number of Oracle Performance Tuning experts because the data is not detailed enough and often isn’t sampled frequently enough. For example, how can you spot a serious performance problem that occurred for one minute when you’re looking at an hourly snapshot interval? The detail is lost in the summarisation of a massive volume of data. (Then again, if you’re only sampling every hour, that’s your own bad decision.)<\/p>\n

It might be true that there are certain response time problems that are impossible to solve without tracing an individual user interaction or batch job, although I would argue that I have frequently solved performance problems using Statspack alone. (In fact, I’m just waiting for the next time this happens so that I can blog about it and start off the arguments again 😉)<\/p>\n

In fact, what we’re really looking for is a solution that is <\/p>\n

– Detailed enough
– Frequent enough
– Recorded at the session level and
– Maintained for long enough to perform retrospective performance analysis. <\/p>\n

There are a number of ways this could be achieved.<\/p>\n

The Oracle Wait Interface book<\/a> I mentioned yesterday describes a solution that records session-level events into database tables. The data is not as detailed as a full trace file, but may give a hint of what performance problems individual sessions have experienced. I think it’s a pretty good attempt to bridge the gap between the high-level Statspack and low-level tracing information.<\/p>\n

Better solutions are available in Oracle 10g, provided of course that you have paid the necessary licence fees<\/a>! Historical perspective is precisely what Active Session History (ASH) and Automatic Workload Repository (AWR) are designed to deliver.<\/p>\n","protected":false},"excerpt":{"rendered":"

Let’s look at items 2 and 3 from the list in my previous blog 1) You know you have a performance problem and can re-create it by running a specific part of the application, be it a user interaction or batch job.2) You have an intermittent but recurring performance problem which looks system-wide and can’t\u00a0… Continue reading Historical Perspective (Part 2)<\/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-1050","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":866,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/08\/updates\/","url_meta":{"origin":1050,"position":0},"title":"Updates","date":"February 8, 2006","format":false,"excerpt":"Just a couple of small updates on some previous blogs.The database that was suffering from network latency problems between it and the app servers was moved down South last week. It all went very smoothly and that particular problem has been resolved. The 20+ hour job runs over night very\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1049,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/09\/historical-perspective-part-1\/","url_meta":{"origin":1050,"position":1},"title":"Historical Perspective (Part 1)","date":"August 9, 2006","format":false,"excerpt":"As mentioned in a few blog comments in the past, it's not the greatest idea to advertise on the web that you're going on holiday (or vacation if you prefer), given the easy availability of home addresses online. You might as well leave the back door open for the burglars\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1614,"url":"http:\/\/orcldoug.com\/blog\/2010\/10\/03\/network-events-in-ash\/","url_meta":{"origin":1050,"position":2},"title":"Network Events in ASH","date":"October 3, 2010","format":false,"excerpt":"Note - using ASH and the Top Activity screen require the use of the Diagnostics Pack License.This post was prompted by yet another performance problem identified using pretty pictures and Active Session History data. Although, as you'll see, some pretty old-fashioned tools played their part too!ASH entries only exist for\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":992,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/13\/production-call-out\/","url_meta":{"origin":1050,"position":3},"title":"Production Call-out","date":"June 13, 2006","format":false,"excerpt":"On Saturday morning, we ran into a problem on one of our 9.2.0.7 Production databases during an Extract, Transform and Load (ETL) batch process.I was called at 5:15am to look at a job that normally runs in a few minutes but had failed after 30 or 40 minutes when trying\u2026","rel":"","context":"With 4 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1159,"url":"http:\/\/orcldoug.com\/blog\/2006\/12\/11\/a-more-complex-statspack-example-summary\/","url_meta":{"origin":1050,"position":4},"title":"A More Complex Statspack Example – Summary","date":"December 11, 2006","format":false,"excerpt":"Looking back at the three blogs (and hopefully the comments, where others have made some very useful contributions), it's all quite unsatisfactory, isn't it? We haven't solved the problem. All we've proved is that the tests aren't equivalent, although I think there's value in that negative result because I was\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1156,"url":"http:\/\/orcldoug.com\/blog\/2006\/12\/07\/a-more-complex-statspack-example-part-2\/","url_meta":{"origin":1050,"position":5},"title":"A More Complex Statspack Example – Part 2","date":"December 7, 2006","format":false,"excerpt":"So far we're barely into the comparison of the Statspack reports from the two different test environments and they look quite different already.Next up, the Instance Efficiency Percentages. I have to confess that I rarely find these very useful because the numbers are always near 100% on most databases I\u2026","rel":"","context":"With 13 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1050","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=1050"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1050\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1050"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1050"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1050"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}