“How do you interpret statspack data ?”
If you go for a generic answer, it’s likely to be a long answer – but the first step is always the classic “Tom Kyte turnaround” … Why do you want to interpret Statspack data ?
As “a starter for ten” [Ed: a reference that may be familiar only to UK viewers of the quiz show Univeristy Challenge], I’m just going to float one thought: there are three reasons for looking at Statspack data and the reason dictates the approach, and the significance you attach to the data.
Reason 1 is simply to observe patterns and trends over time. Statspack allows you to collect data at regular intervals and, with a little careful progamming, you can run trend lines through the data to answer questions like “Has the random disk read time been getting worse over the last six months”, “Has the number of hourly executions of query X been increasing over the last 8 weeks“.
Reason 2 is to answer simple questions like: “why was the system surprisingly slow at 10:00 am this morning”. The strategy is simple – dump the Statspack report ($ORACLE_HOME/rdbms/admin/spreport.sql) or AWR report ($ORACLE_HOME/rdbms/admin/swrrpt.sql) around the problem time this morning, and do the same for the same time-period yesterday and the previous week. Simply eye-balling the reports looking for the biggest changes is often enough to identify the critical issue.
Reason 3 is to address the standard complaint: “the system is slow”. If that’s really the case – no one task is a terrible issue but response times always seems to be unreasonably slow – then a quick analysis of a Statspack report may give you a clue about how to use your time most cost-effectively to make a difference. The trick, of course, is to identify and cross-reference the information that highlights the two or three critical failings that are worth addressing. I’ll be discussing this, and the difference between ‘analysing the data’ and ‘picking random numbers’, in a future article.
Footnote: Any advice about reading Statspack reports is almost always relevant when reading AWR reports (and vice versa).