Generates Oracle StatsPack reports with SQL plans

oracle_statspack_report.bash

#!/bin/bash
export BEGIN_SNAP_ID=$1
export END_SNAP_ID=$2
export REPORT_PREFIX=/tmp/spreport_${BEGIN_SNAP_ID}_${END_SNAP_ID}_pid$$
export ORACLE_AUTH=PERFSTAT/PERFSTAT

if [ "${END_SNAP_ID}" -a "${BEGIN_SNAP_ID}" ]; then
 echo "BEGIN_SNAP_ID=${BEGIN_SNAP_ID}, END_SNAP_ID=${END_SNAP_ID}, REPORT_PREFIX=${REPORT_PREFIX}"
else
 sudo -u oracle sqlplus ${ORACLE_AUTH} <<\EOF
  col ucomment format a50;
  select snap_id, to_char(snap_time, 'YY-MM-DD HH24:MI:SS') snap_time, ucomment from stats$snapshot order by 1;
EOF
 echo
 echo "usage: $0 [BEGIN_SNAP_ID] [END_SNAP_ID]"
 exit 1
fi

set -x
set -e

exec > >(tee ${REPORT_PREFIX}.log)
exec 2>&1

sudo -u oracle sqlplus ${ORACLE_AUTH} > /dev/null <<EOF
 define report_type=text
 define begin_snap=${BEGIN_SNAP_ID}
 define end_snap=${END_SNAP_ID}
 define report_name="${REPORT_PREFIX}.txt"
 @?/rdbms/admin/spreport
EOF

cat "${REPORT_PREFIX}.txt" |
perl -ne 'if (/^(?:\s+[0-9,.]+){4,7}\s+(\d+)$/ and $1 > 0) { print <<"EOS"
 define report_type=text
 define begin_snap=$ENV{BEGIN_SNAP_ID}
 define end_snap=$ENV{END_SNAP_ID}
 define hash_value=$1
 define sql_id=$1
 define report_name="$ENV{REPORT_PREFIX}_$1.txt"
 @?/rdbms/admin/sprepsql
EOS
}' | sudo -u oracle sqlplus ${ORACLE_AUTH} > /dev/null

ls -l ${REPORT_PREFIX}*