(Reminder, just in case we still need it, that the use of features in this post require Diagnostics Pack license.)<\/p>\n
Damn me for taking so long to write blog posts these days. By the time I get around to them, <\/em>certain very knowledgeable people have commented on part 1<\/em><\/a> and given the game away! 😉 Why do people argue for a Statspack\/AWR interval of 15 or 30 minutes on important systems? Because when they encounter a performance problem that is happening right now or didn\u2019t last for very long in the past, they can drill into a more narrow period of time in an attempt to improve the quality of the data available to them and any analysis based on it. (As an aside, I\u2019m sure most of us have generated additional Statspack\/AWR snapshots manually to *really* reduce the time scope to what is happening right now on the system, although this is not very smart if you\u2019re using AWR and Adaptive Thresholds!) <\/p>\n However, there are better tools for the job these days. <\/p>\n If I have a user complaining about system performance then I would ideally want to narrow down the scope of the performance metrics to that user\u2019s activity over the period of time they\u2019re experiencing a slow-down. That can be a little difficult on modern systems that use complex connection pools, though. Which session should I trace? How do I capture what has already happened as well as what\u2019s happening right now? Fortunately, if I\u2019ve already\u00a0paid for Diagnostics Pack then I have *Active Session History* at my disposal, constantly recording snapshots of information for all active sessions. In which case, why not look at<\/p>\n – The session or sessions of interest (which could also be *all* active sessions if I suspect a system-wide issue) Rather than running a system-wide report for a 15 minute interval that aggregates the data I\u2019m interested in with other irrelevant data? (To say nothing of having to wait for the next AWR snapshot or take a manual one and screwing up the regular AWR intervals …) <\/p>\n When analysing system performance, it\u2019s important to use the most appropriate tool for the job and, in particular, focus your data collection on what is *relevant to the problem under investigation*. The beauty of ASH is that if I\u2019m not sure what *is* relevant yet, I can start with a wide scope of all sessions to help me find the session or sessions of interest and gradually narrow my focus. It has the history that AWR has, but with finer granularity of scope (whether that be sessions, sql statements, modules, actions or one of the many other ASH dimensions<\/a>). Better still, if the issue turns out to be one long-running SQL statement, then a SQL Monitoring Active Report<\/a> probably blows all the other tools out of the water! <\/p>\n With all that capability, why are experienced people still so obsessed with the Top 5 Timed Events section of an AWR report as one of their first points of reference? Is it just because they\u2019ve become attached to it over the years of using Statspack? AWR has it\u2019s uses (see JB\u2019s comments for some thoughts on that and I\u2019ve blogged about it extensively in the past) but analysing specific performance issues on Production databases is not it\u2019s strength. In fact, if we\u2019re going to use AWR, why not just use ADDM<\/a> and let software perform automatically the same type of analysis most DBAs would do anyway (and in many cases, not as well!) <\/p>\n Remember, there\u2019s a reason behind these Recurring Conversations posts. If I didn\u2019t keep finding myself debating these issues with experienced Oracle techies, I wouldn\u2019t harbour doubts about what seem to be common approaches. In this case, I still think there are far too many people using AWR where ASH or SQL Monitoring are far more appropriate tools. I also think that if we stick with a one hour interval rather than a 15 minute interval, we can retain four times as much *history* in the same space! When it comes to AWR \u2013 give me long retention over a shorter interval every time! <\/p>\n P.S. As well as thanking JB for his usual insightful comments, I also want to thank Martin Paul Nash<\/a>. When I was giving an AWR\/ASH presentation at this springs OUGN conference, he noticed the bullet point I had on the slide suggesting that we *shouldn\u2019t* change the AWR interval and asked why. Rather than going into it at the time, I asked him to remind me at the end of the presentation and then because I had no time to answer, I promised I\u2019d be blogging about it that weekend. That was almost 4 months ago! Sigh. But at least I got there in the end! 😉 <\/p>\n","protected":false},"excerpt":{"rendered":"
<\/em>
I finished the last part by suggesting that a narrow AWR interval makes less sense in a post-10g Diagnostics Pack landscape than it used to when we used Statspack. <\/p>\n
– For the short period of time I\u2019m interested in
– To see what they\u2019re actually doing <\/p>\n