Re: STATSPACK reporting generation script

From: Ramon Poca <ramon.poca_at_gmail.com>
Date: 5 Jul 2005 01:16:41 -0700
Message-ID: <1120551401.750913.27310@g43g2000cwa.googlegroups.com>

Thanks, but I was looking for something to automate the execution of those.
I've hacked away a shell script for the task. It runs on cron on midnight, generates the reports, zips them up and sends it to my mailbox.

DISCLAIMER: As usual, use at your own risk ;-)

Notes: The other script does 15-minute snapshots every 3 hours.

So this script gets the start-end snap where a.snap_time < b.snap_time + (30/1440), ie less than 30 minutes difference. THERE'S NO DATE FILTER. Last lines should TRUNCATE statspack tables. Those are commented out.

------------------8<---------------------------------

#!/usr/bin/bash
# Generate reports and zip them up

ORACLE_HOME=/export/home/oracle/product/8.1.7
ORACLE_BASE=/export/home/oracle/product/8.1.7
ORACLE_SID=WSI

PATH=/usr/local/bin:$PATH:$ORACLE_HOME/bin:/usr/X/bin LD_LIBRARY_PATH=$ORACLE_HOME/lib
export DISPLAY PATH ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH EDITOR export NLS_LANG=SPANISH_SPAIN.WE8DEC

ADMIN=YOUR_at_MAIL.ADDRESS.COM TMPDIR=/tmp/spreport.$$
mkdir -p $TMPDIR
OD=$PWD
cd $TMPDIR

sqlplus -S internal > /dev/null <<EOF
connect perfstat/perfstat
set echo off
spool reps.lst
select 'SNAPID', a.snap_id, b.snap_id
 from (select * from STATS\$SNAPSHOT) a, (select * from STATS\$SNAPSHOT) b
 where a.snap_id = b.snap_id + 1
and a.snap_time < b.snap_time + (30/1440) /
EOF DATE=`/usr/bin/date '+%Y_%m_%d'`

cat reps.lst | grep 'SNAPID' | tr -s ' ' | cut -d' ' -f2,3 |tr ' ' ':'
> reps.all

for i in `cat reps.all`; do

        BSNAP=`echo $i | cut -d':' -f2`
        ESNAP=`echo $i | cut -d':' -f1`

sqlplus -S internal > /dev/null <<EOF
connect perfstat/perfstat
set echo off
define begin_snap=$BSNAP
define end_snap=$ESNAP
define report_name=snap_${DATE}_${BSNAP}.lst

@?/rdbms/admin/spreport
EOF
done

zip -q sp_$DATE.zip snap_*.lst
uuencode sp_$DATE.zip sp_$DATE.zip > sp.uue mailx -s "Statspack for $DATE" $ADMIN < sp.uue

cd $OD

rm -rf $TMPDIR

# Truncate all statspack reports
#sqlplus -S internal <<EOF
#     connect perfstat/perfstat
#     @?/rdbms/admin/sptrunc
#EOF

Received on Tue Jul 05 2005 - 03:16:41 CDT