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 asked to investigate ‘what is wrong with our environment’.
One of the problems is that the test runs for between 7 and 12 hours and, viewing a Statspack report covering such long periods, it’s extremely difficult if not impossible to identify what is definitely a problem. However, it wasn’t at all difficult to identify that our environment was experiencing more transaction activity, which unearthed the central flaw in the vendor’s test – they didn’t have most of the standard functionality switched on.
My usual approach to identifying performance problems with a business task like this is to trace it using event 10046, level 8 or 12 and that would have provided much more detailed information about the task at hand.
Consider this, though.
Whilst I’m a fan of session-level tracing, do you think
it would have been easier to identify this problem using event 10046 on
the session of interest or not? We would have seen what was holding up
the job by looking at the wait events, but we would have known nothing
about the cause, because that was outside of the session we were looking at. We might have seen the batch job waiting for a resource but our deliberately narrow scope wouldn’t include the other jobs running on the system. (We could have switched on tracing at the instance level, particularly as these are test environments, but we would have had a ton of output to review – trust me, I’ve done it!).
Please don’t take this as a criticism of session-level tracing, but merely a suggestion that there’s room for a variety of performance analysis tools which give different views of a problem.
I think it’s reasonable to describe the instance-wide scope of Statspack as a weakness. I also think it’s reasonable to describe the instance-wide scope of Statspack as a strength. It depends what you’re trying to achieve.
Exactly. The image I use for statspack versus trace is to imagine diagnosing a problem in a car.
The statspack report will tell you if the coughing and splutter is the result of a lack of petrol or an ignition problem.
The trace will tell you if it is an empty tank, a clogged filter or carburator, a flat battery, a leaking coil or dirty points.
Assuming of course you drive a conventional car as opposed to an hybrid!
There’s also the intermediate level of the v$session views. Assuming you don’t have to deal with connection pooling or shared server, it’s not hard to whip up a session-level resource profile from v$session, v$sesstat, v$session_event, and sometimes it’s possible to zoom in on the expensive sql by hitting v$sql* via v$open_cursor.
A bit of perl/DBI or analytic sql, and voila, more than you probably wanted to know about the session.
It doesn’t have much to do with your statspack problem, and I don’t know how that fits into the automobile analogy. Maybe statspack is more like a traffic control report – was the problem due to a traffic jam or an accident, the v$view thing would be company headquarters uploading your recent driving activity and a cumulative history of engine diagnostics from the truck’s computer, and the trace would be more like some sort of engine-level diagnostic history.
Perhaps it is apparent that I have an mba, master of bad analogies.
Although in some cases 10046 is indispensible, I find myself fixing plenty of performance problems without using it. Why there are plenty of performance problems is another question…
Perhaps it is apparent that I have an mba, master of bad analogies.
LOL 😉
On your more serious points, that’s where the Oracle Press Wait Interface book works well – a cross between Statspack and session event tracing.