Visualize your STATSPACK reports with Python Plotly


28.10.2017
by Kamil Stawiarski

If you work as a consultant, getting remote access to your customer is sometimes a nightmare!
Even when you’ll get one, there a times when you just want to get your job done and finish this nightmare.

For example, there are times when it is easier to dump a statspack repo and do offline analyzes. Unfortunately, it is not always possible. In this kind of circumstances, I ask my customer to generate the appropriate set of reports and send them to me for later analyzes. If you know the set of snapshot ids you want to analyze, you can use the following simple script to extract a set of statspack reports:

#!/bin/bash

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

for i in `seq $1 $2`; do
  esnap=$i
  bsnap=$((i-1))

sqlplus "/ as sysdba" << !
define report_name=sp_${bsnap}_${esnap}.txt
define begin_snap=${bsnap}
define end_snap=${esnap}
@?/rdbms/admin/spreport
exit
!

done

After running this script with 2 arguments (begin snap and end enap) you’ll get a set of STATSPACK reports between each snap pair.

Sometimes it would be nice to find out where to start our analyzes, to create some kind of visualization of spikes in the range of time. For example to visualize how wait class events where used by the instance over a time and what how load profile for the instance was changing.

Fortunately we have some free tools to play with and create cool charts – I chose Python and a Plotly library (https://plot.ly/python/)

Using this library I wrote a simple Python script which will read the data from a bunch of STATSPACK reports and visualize it in the following form: http://ora-600.pl/oinstall/txt.html

Please note, that the report is interactive and autoscaled 🙂 Plotly is really cool!

So how to use it? Just like this:

$ python statspack_analyzer.py
This script by Kamil Stawiarski (@ora600pl) is to help you with visualizing data from multiple statpack reports
Usage:
python statspack_analyzer.py /path/to/reports/ pattern_to_filter_reports_by_name
You have to install plotly first [pip install plotly]
$ python statspack_analyzer.py /tmp/spreports/spreports txt
This is the format of your plot grid:
[ (1,1) x1,y1 ]
[ (2,1) x2,y2 ]

The report will open automatically in your default browser.
The scripts can be found here:
https://github.com/ora600pl/statspack_scripts/blob/master/statspack_analyzer.py

https://github.com/ora600pl/statspack_scripts/blob/master/gen_statspack_reps.sh

Have fun!


Contact us

Database Whisperers sp. z o. o. sp. k.
al. Jerozolimskie 200, 3rd floor, room 342
02-486 Warszawa
NIP: 5272744987
REGON:362524978
+48 508 943 051
+48 661 966 009
info@ora-600.pl

Newsletter Sign up to be updated