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 be attributed to a particular part of the application.
3) You had a system-wide performance problem yesterday afternoon that hasn’t occurred before.
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’.
Both cases require some form of system-wide monitoring to be in place before the performance problem occurs. Otherwise, you’ll have absolutely no relevant information available and consequently have no chance of solving it.
Here are a couple of options
a) System-wide event 10046 tracing.
b) Statspack.
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 happen one day.
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.)
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 😉)
In fact, what we’re really looking for is a solution that is
– Detailed enough
– Frequent enough
– Recorded at the session level and
– Maintained for long enough to perform retrospective performance analysis.
There are a number of ways this could be achieved.
The Oracle Wait Interface book 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.
Better solutions are available in Oracle 10g, provided of course that you have paid the necessary licence fees! Historical perspective is precisely what Active Session History (ASH) and Automatic Workload Repository (AWR) are designed to deliver.