AWR / Statspack

This is a list of articles I’ve written about understanding and interpreting the Statspack and AWR reports (in many cases the two are effectively interchangeable) plus as few articles on related topics.

I’ve also included a number of articles from other authors in a separate list at the end. Unfortunately the articles on the Oracle website tend to drift around over time so some of the the links may no longer work, even though the article exists somewhere.

At some point the list will be sorted by date of publication (most recent first) and the datestamp will be included with the entry.

From my blog

• Lesser known AWR scripts
• Lesser known Statspack scripts
• Caution with “Timed Events”
• The Top Timed Event
• Comments on “Instance Efficiency Ratios”
• The need to look at base statistics, not ratios
• The Statspack levels
• Threats of Statspack level 6 (in 9i)
• Checking Statspack collection times in 10g
• CPU Used
• Finding the wrong problem
• Scoping and capture problems with statspack
• Report over time how long AWR (originally Statspack) snapshots took to complete.
• Report a single statistic across time from the AWR history of v$sysstat.
• Report a single statistic across time from the AWR history of v$osstat.
• Quick and Dirty example of comparing two statistics across time from the AWR history
• v$resource_limit – including a trend through the AWR history
• Flagging (“coloring”) SQL for special capture in the AWR history
• Case study of using v$active_session_history to explain a parsing problem
• How ASH samples are captured for the dba_hist_active_sess_history.
• Saving Optimizer Stats
• Timed events example from tkprof
• Analysing Statspack  1 – basic strategies
• Analysing Statspack  2 – avoiding ratios
• Analysing Statspack  3 – be cautious about averages
• Analysing Statspack  4 – generating trends from reports
• Analysing Statspack  5 – resource hogs
• Analysing Statspack  6 – Some redo log examples
• Analysing Statspack  7 – System work or user waits
• Analysing Statspack  8 – Reading and cross-checking
• Analysing Statspack  9 – Complete case study
• Analysing Statspack 10 – Context is critical
• Analysing Statspack 11 – Chasing some clues
• Analysing Statspack 12 – A little bit of RAC
• Analysing Statspack 13 – Case Study on Statspack
• Hacking Statspack into 12c
• Deception – don’t forget to check the “percentage accounted for”
• Case Study – 1 (An example of using the AWR report).
• Missing SQL_ID – The AWR says it’s a heavy hitter, but I never see it in v$session.sql_id

External links

• Graham Wood (Oracle): a pdf file of his presentation on ASH ( v$active_session_history).
• Connie Dialeris Green (Oracle): Diagnosing Performance Bottlenecks using Statspack and the Oracle Performance Method.
  • Originally published in 2001, but check the comment I made here in 2008
• 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