Visualize your STATSPACK reports with Python Plotly

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 </p />
</p></div>

    	  	<div class=