I’ll warn you up front that this is going to be spectacularly lacking in detail but it’s a simple and true example from the other week of the kind of thing I find Statspack useful for.<\/p>\n
One of the developers scuttled round to my desk one late shift very concerned about a production feed that had slowed to a crawl the day before. He’d spent most of this day going round in circles trying to persuade DBAs that something was wrong and was at the end of his tether (‘Network problems’ indeed!). These days I’m in the development team so I don’t do<\/i> production, but I could tell he was fed up so I said I’d take a quick<\/i> look.<\/p>\n
All of our databases have 15 minute snapshots going back 30 days so I asked him when the problem had first occurred and ran two reports for 30 minute intervals at the same time on two days – the day before the problem and the day of the problem. This would be adequate because this feed runs throughout the day.<\/p>\n
As usual with Statspack, there was a ton of information and I suppose the skill comes in deciding where to look. Most of the time I’ll look at the timed event profile at the start of the report and the SQL ordered by gets at the very least.<\/p>\n
Here’s the start of the SQL ordered by gets statement for the good day.<\/p>\n
<\/code> Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
And this is from the bad day. I’ve highlighted the two statements that have appeared that weren’t there before.<\/p>\n
<\/code><\/p>
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
Now, and this is very important, because I was working together with the developer I could ask the simple question – ‘do those statements look like they’re anything to do with your job?’ He recognised them instantly. Performance tuning always works best when it involves people with application knowledge, not just DBAs. Next I went back to look at the Statspack report for the good day, found those statements and could see that they were performing much more work on the bad day.<\/p>\n
Now Statspack hasn’t told me why<\/b> the statement is performing more work, but it very quickly narrowed down what I needed to look at. It turned out that the UPDATE statement wasn’t using an index that it had been previously.<\/p>\n
Better still, because we save all of our object statistics for the CBO for at least 30 days, we could see which statistics had changed and investigate why Oracle was picking a different execution plan.<\/p>\n
I would say this illustrated two benefits of Statspack. First, it’s able to narrow your scope in some cases<\/i>. Second, it’s very low cost and quick to use. People had been looking at this problem for a *day*. The entire process of spotting the problem statement took less than 10 minutes and then maybe another 10 minutes narrowing down the change in the statistics that was causing the problem. If it’s so quick to use (maybe in experienced hands) there isn’t much cost if it turns out to be of no use. i.e. I think it’s worth a try. That might not be a very rigorous or scientific statement but I’ll do whatever it takes to solve business problems.<\/p>\n
(Actually – updated later – the thing it most illustrates is that Statspack can show a change<\/i> in behaviour after the fact<\/i> as long as you have it switched on everywhere)<\/p>\n
This was on Oracle 9.2.0.7. Had it been on 10g, we would probably have used AWR.<\/p>\n","protected":false},"excerpt":{"rendered":"