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.
- 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
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 need to look at base statistics, not 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: Report over time how long AWR (originally Statspack) snapshots took to complete.
Jonathan Lewis: Report a single statistic across time from the AWR history of v$sysstat.
Jonathan Lewis: Report a single statistic across time from the AWR history of v$osstat.
Jonathan Lewis: Quick and Dirty example of comparing two statistics across time from the AWR history
Jonathan Lewis: v$resource_limit – including a trend through the AWR history
Jonathan Lewis: Flagging (“coloring”) SQL for special capture in the AWR history
Jonathan Lewis: Case study of using v$active_session_history to explain a parsing problem
Jonathan Lewis: How ASH samples are captured for the dba_hist_active_sess_history.
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
Jonathan Lewis: Analysing Statspack 13 – Case Study on Statspack
Jonathan Lewis: Case Study – 1 (An example of using the AWR report).

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

Comments (13)

13 Comments »

Great collection. Thanks.

Comment by Fahd Mirza — February 6, 2011 @ 11:15 am GMT Feb 6,2011 | Reply
The papers at the top of the list from Connie Dialeris Green and Graham Wood seem to drift around as Oracle Corp. re-organizes the TechNet website. At present they are in the “database/focus-areas” directory.

If the current links fail for you, please add a comment so that I can find and relink them.

Comment by Jonathan Lewis — March 9, 2011 @ 9:37 am GMT Mar 9,2011 | Reply
This may also be of interest from Tim Gorman.
http://www.evdbt.com/papers.htm
RDBMS Forensics using ASH

The audio may aslo be available on demand in the near future
as he gave a version of this at the following online confrence.

http://www.brainsurface.com/virtathon/sessions-schedule

Ron

Comment by Ron Chennells — August 4, 2011 @ 1:06 pm BST Aug 4,2011 | Reply
Jonathan,
I recently installed 12c (database) and I’m trying to install statspack. Do you have any insight on the install, particularly around common and local users, and where perfstat would set in this pantheon? Other things to be aware of in this plugable environment for running statspack?
Thanks,
bg

Comment by Bill — July 2, 2013 @ 11:15 pm BST Jul 2,2013 | Reply
- Bill,
  
  I hadn’t thought of trying to install statspack on 12c.
  Following your comment I’ve taken a quick look at $ORACLE_HOME/rdbms/admin/spdoc.txt and noted a small reference to 12c (which, at the head of the document, calls it 12g ;) )
```
9.1.  Changes between 11.1  and 12.1
  o  Idle Events
     - Added Idle Events that span LogMiner, PQ, SQL*Net, Capture Reply
  o  Consolidated DB/Pluggable DB
     - Consolidated DB and Statspack Reporting at the CDB Root Level
```
  So there have been some changes for 12c.
  There’s also this pair of lines in the spcreate.sql script, although I can find anything in any of the other scripts that relates to it – so perhaps it an internal fiddle to handle the issues of how links between plugged databases and the container database are made.
```
-- set this parameter for creating common objects in consolidated database
alter session set "_oracle_script" = TRUE;
```
  Comment by Jonathan Lewis — July 3, 2013 @ 9:30 am BST Jul 3,2013 | Reply
- Bill,
  
  What sort of problems are you seeing, and what type of install are you doing.
  
  I’ve just done an install into EE without containers; all I did was create a tablespace for the perfstat account, comment out the “_oracle_script” line, and then run spcreate.sql as sys. I’ve taken a couple of snapshots, and produced a report,
  
  Everything seemed to work okay; the only problem was that every wait in my Top 5 looked as if it should have been listed as idle – here’s the list, with their probable timeouts:
```
lreg timer                         3 seconds
heartbeat redo informer            1 second
LGWR worker group idle             5 seconds
AQPC idle                         30 seconds
```
  Comment by Jonathan Lewis — July 3, 2013 @ 10:44 am BST Jul 3,2013 | Reply
Jonathan,

I’m just logging in as sys and running spcreate and getting:
```
... Creating PERFSTAT user
create user perfstat
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Where sys is in "root" container
SQL&gt; show con_name

CON_NAME
------------------------------
CDB$ROOT

and the configuration has
SQL&gt; select con_id,dbid,NAME,OPEN_MODE from v$containers;

    CON_ID	 DBID NAME			     OPEN_MODE
---------- ---------- ------------------------------ ----------
	 1 1347503191 CDB$ROOT			     READ WRITE
	 2 4062285978 PDB$SEED			     READ ONLY
	 3 2266865794 PDBORCL			     READ WRITE

On 
SQL&gt; alter session set container=pdborcl;

Session altered.
```
The spcreate.sql runs to completion and spauto.sql will run and set up the job.

Now the question is what is statspack sampling activity against — the container pdborcl or against all of the database? Given perfstat’s privileges and roles, I’m guessing all of the database. Hum …? Not straight forward.

Might be interesting to globally change perfstat to c##perfstat in the statspack creation scripts and see what happens.

Comment by Bill — July 3, 2013 @ 2:19 pm BST Jul 3,2013 | Reply
- Bill,
  
  That’s what I did when I got to a CDB. The snapshot will be for the container, since there is only one library cache, SGA, etc… But that’s the way the AWR works anyway. The interesting thing is what happens when the specific PDB is closed but the CDB is up with a job visible in cdb_jobs.
  
  I did consider messing about with a c##perfstat, but there were a lot of places I’d have to fiddle with the sp scripts.
  
  Comment by Jonathan Lewis — July 3, 2013 @ 7:15 pm BST Jul 3,2013 | Reply
- Bill,
  Latest update: edit spcusr.sql to comment the line “container=current” in the ‘create user’ statement; execute the “alter session” command from the spcreate.sql script running the script, and you can install perfstat as a common user in the CDB$ROOT.
  
  Comment by Jonathan Lewis — July 5, 2013 @ 8:56 am BST Jul 5,2013 | Reply
  - Jonathan,
    
    Thanks. I’ll give this a try when I have a moment. I have also been following and adding to this topic discussed on otn, as I see you have.
    bg
    
    Comment by Bill Gaynor — July 5, 2013 @ 4:03 pm BST Jul 5,2013 | Reply
One more comment. Spauto.sql was run as sys and is running as scheduled. I changed spauto to run every 15 minutes.
```
SQL&gt; select job, log_user, schema_user, priv_user
  2  from dba_jobs;

       JOB LOG_USER   SCHEMA_USER  PRIV_USER
---------- ---------- ------------ ------------
	 1 SYS	      PERFSTAT	   SYS

SQL&gt; select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')
  2  "Date/Time" from stats$snapshot,v$database;

NAME	     SNAP_ID Date/Time
--------- ---------- -------------------
ORCL		   1 03.07.2013:07:09:22
ORCL		   2 03.07.2013:07:24:04
ORCL		  11 03.07.2013:07:39:02
```
Not sure why there is a gap in the snap_id.

Comment by Bill — July 3, 2013 @ 2:54 pm BST Jul 3,2013 | Reply
- Bill,
  
  The gap in the snap id is a relatively common phenomenon. The sequence used by statspack has a cache size of 10, so if it gets flushed out of the library cache you can lose values. I’ve often suggested to people that they change to nocache on that sequence to avoid losing values (this allows you to cheat a little bit with “difference” SQL). On the other hand if you leave the cache at 10 it can give you a little threat warning that you have pressure on the shared pool if you see some sort of persistent pattern of gaps (e.g. the snap_id jumps by 10 between 8:30 pm and 10:15 pm, but doesn’t have gaps at any other time of day).
  
  Comment by Jonathan Lewis — July 3, 2013 @ 7:19 pm BST Jul 3,2013 | Reply
Hello All,

Just two little questions.

– Statspack is free in oracle 12C ? or we must to buy Diagnostic pack ?
– If it is free, it’s easy to install it on 12C ?

Thank you for your comments.

Comment by Yannick — October 27, 2014 @ 11:00 am GMT Oct 27,2014 | Reply