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!