AWR / Statspack

People frequently ask questions about interpreting statspack (and AWR) outputs. So I’ve started to collect references to examples of statspack (and AWR) outputs that have been published on the Internet with intelligent comments about interpretation. A few of the items are about related topics, rather than output and interpretation.

There are bound to be more items out there than I currently know of, so if you know of any good examples, add it to the comments list and if I like it I’ll add it to the main list.

Getting Started: if you don’t know how to install or use statspack, then the best place to start is on your server by reading the documentation at $ORACLE_HOME/rdbms/admin/spdoc.txt, which describes how to install it, how to take snapshots, and how to use the reporting facilities.

• Graham Wood: a pdf file of his presentation on ASH ( (v$active_session_history).
• Connie Dialeris Green: Diagnosing Performance Bottlenecks using Statspack and the Oracle Performance Method.
• Metalink (MOS):  Not worth reading (until further notice) Metalink note 842884.1
• Doug Burns: “Comparing current with recent history”.
• Jos Van Den Oord: Statspack Reporting from a Standby database (11g)
• Timur Ahkmadeev Extracting a short term Load Profile from v$sysmetric
• Jonathan Lewis: Lesser known AWR scripts
• Jonathan Lewis: Lesser known Statspack scripts
• Jonathan Lewis: Caution with “Timed Events”
• Jonathan Lewis: The Top Timed Event
• Jonathan Lewis: Comments on “Instance Efficiency Ratios”
• Jonathan Lewis: The Statspack levels
• Jonathan Lewis: Threats of Statspack level 6 (in 9i)
• Jonathan Lewis: Checking Statspack collection times in 10g
• Jonathan Lewis: CPU Used
• Jonathan Lewis: Finding the wrong problem
• Jonathan Lewis: Scoping and capture problems with statspack
• Jonathan Lewis: Saving Optimizer Stats
• Jonathan Lewis: Timed events example from tkprof
• Jonathan Lewis: Analysing Statspack  1 – basic strategies
• Jonathan Lewis: Analysing Statspack  2 – avoiding ratios
• Jonathan Lewis: Analysing Statspack  3 – be cautious about averages
• Jonathan Lewis: Analysing Statspack  4 – generating trends from reports
• Jonathan Lewis: Analysing Statspack  5 – resource hogs
• Jonathan Lewis: Analysing Statspack  6 – Some redo log examples
• Jonathan Lewis: Analysing Statspack  7 – System work or user waits
• Jonathan Lewis: Analysing Statspack  8 – Reading and cross-checking
• Jonathan Lewis: Analysing Statspack  9 – Complete case study
• Jonathan Lewis: Analysing Statspack 10 – Context is critical
• Jonathan Lewis: Analysing Statspack 11 – Chasing some clues
• Jonathan Lewis: Analysing Statspack 12 - A little bit of RAC

Footnote: Any advice about reading statspack reports is almost always relevant when reading AWR reports.